Forum Discussion
Using MS Access as a data source with On-Premises Data Gateway
23.11.2018: Another update: The order of the installations is important.
22.11.2018: I updated this article. Let me know if it works for you!
When publishing a report that uses an on-Premises MS Access database file as a data source you will soon discover that the On-Premises Data Gateway doesn't provide the possibility to configure an Access Data Source. As of today, Access is still not on the list of available data source types.
However, an Access connection can be configured for the personal Gateway. But what if using a personal gateway is no option for you, because you might not be allowed to install it on your machine? And using a file data source for the job surely won't do the job?!
As it turns out, it is possible to update datasets using Access as a datasource by using the On-Premises Data Gateway!
What to install
- Install the "Microsoft Access Database Engine 2016 Redistributable" (x64) in order to connect using ODBC.
- If you want to also connect using the "File" data source type, additionally install the "Microsoft Access Runtime 2016" (x64) (You need both installations in order to get it to work, but strangely, I found I could uninstall the Redistributable after installing the Runtime and both File and ODBC connections continued to work.
Do NOT install older versions (e.g. 2010), because those seem to install the drivers in a way that the driver won't show up in the "ODBC Data Sources (64bit)" tool. I suspect the gateway being x64 is not able to find installations in the x86-folder.
How to connect using ODBC
Intead of the non-existant Access Connector, you can use ODBC to connect to your MDB or ACCDB-File.
Setting up an ODBC connection can be done in a couple of ways: File DSN, User DSN, System DSN and via DSN-less connection.
File DSN:
Trying to put in a string starting with "FILEDSN=" in Power BI Desktop will show the message "The connection property 'filedsn' isn't supported.":
No luck here!
User DSN / System DSN:
For that, users need the permission to create DSNs using ODBCAD32.exe. But even if they are allowed to do so, using the same method on the gateway would require to setup User DSNs for the gateway's service account or System DSNs on the gateway server. This would be an administrative nightmare if there are more than a few Access dbs. Even worse, what if you have a cluster with more than one server? And if you have to reinstall one of them?
You surely want to avoid that!
DSN-less
A DSN-less connection is simply a connection string, like that:
driver={Microsoft Access Driver (*.mdb, *.accdb)};dbq=C:\database.mdb
This works fine with Power BI Desktop, but using the string in a gateway ODBC data source you are likely to get errors like this:
ERROR [HY000] [Microsoft][ODBC Microsoft Access Driver]General error Unable to open registry key Temporary (volatile) Ace DSN for process 0x1830 Thread 0x6670 DBC 0x1b7e8e68 Jet'.
ERROR [01S00] [Microsoft][ODBC Microsoft Access Driver]Invalid connection string attribute Trusted_Connection
If you google these errors you will find a lot of possible reasons, vague solutions from Microsoft and, finally, this.
The crucial point seems to be:
Grant RWX (including modify) permissions for the user you configured for this datasource on the AppData\Local folder of the user that runs the gateway's service. We use a group "WF-OPDG-rwx" where we put the data source users and use it to grant the necessary permissions for the gateway service user's Local folder.
Things to notice:
- Always grant write permissions on the Access file for the user that is configured on the gateway, even if you want to read data. Read permissions are not enough and provoke errors as well.
- If you chose to install the "Microsoft Access Runtime" you can know configure a data source using the "File" option as well.
Conclusion
With the correct setup we have two working solutions: DSN-less ODBC and FILE. In out case, we use the FILE approach due to folling reasons:
- Users usually start using the Access connector in Power BI Desktop and are annoyed when being told to change the PBIX to use ODBC.
- As you might know, the connection string in Power BI Desktop needs to be exactly the same like the one you configure for the gateway. When using ODBC you have to take care to choose the "Data source name (DSN)" None in order to avoid the GUI to change the connection string by adding that name. By Using the file approach you skip that possibility.
But consider this downside:
- By granting permissions on the Local-folder, all Access data sources use the same folder. That could be considered as a security issue.
Hopefully this post is going to prevent others from spending days looking for a solution like I did. Thanks for reading!
18 Replies
Just to say I had trouble could not install the Microsoft Access 2016 Runtime from the link you provided since this uses the Windows Installer technology (MSI) and there is a conflict when using the Click-to-Run version of office.
Here's the link to the Click-to-run version, choose your language in step 2. and the option to download either 32-bit or 64-bit version will appear.
Hope that helps someone!
Great post - many thanks - helped me a lot with fixing my connection to MS Access - needed to swich my query from native OLEDB to ODBC due to being unable to update the data source setting on the gateway:
Here is an example M query to make that connection work in the query editor:
let Source = Odbc.DataSource("driver={Microsoft Access Driver (*.mdb, *.accdb)};dbq=\\path to DB\DBName.accdb", [HierarchicalNavigation=true]), #"\\path to DB\DBName.accdb" = Source{[Name="\\path to DB\DBName.accdb",Kind="Database"]}[Data], #"Query_View" = #"\\path to DB\DBName.accdb"{[Name="Query",Kind="View"]}[Data], in #"Query_View"Thank you so much for all the hard work you put into finding this out and publishing Sokon .
I found when doing this that I only needed to grant modify access to the AppData\Local\Temp folder in order to get the connection working. This improves the security of the setup and also means you don't get a bunch of 'can't enumerate objects in this container' errors when applying permissions. I tried to limit to just combinations of the subfolders that were in there and none of those worked, so I presume the process is just writing to the Temp folder itself.
I'm having a little bit of difficulty understanding this part:
Grant RWX (including modify) permissions for the user you configured for this datasource on the AppData\Local folder of the user that runs the gateway's service.
How did you determine what user runs the gateway service? And when it says user that configured the data source, is that the person that owns the semantic model or has access to that particular gateway connection or something else entirely.
Thanks,
Steve
Hi Steve,
user that runs the gateway's service = the service account that you see in services.msc on the gateway host running the service 'On-premises data gateway service'. By default, this account is called PBIEgwService, so the AppData folder is going to be [gateway host]\C$\Users\PBIEgwService\AppData
user you configured for this datasource - the user whose credentials are used for the gateway connection to authenticate. Careful, this isn't (necessarily) 'the user that configured the data source', it's the credentials used
Thanks for your sharing the above methods about refreshing Access data source in Power BI Service.
I would like to give some comments on “an Access connection can be configured for the personal Gateway. But what if using a personal gateway is no option for you” in your post. The issue that using personal gateway to connect to the MS Access DB might be related to the 32 bit/64 bit Access database.
Power BI only have 64 bit personal gateway, it is by design to leverage only 64 bits drivers. Referring to the 32 bit MS Access DB, the 64bit access driver is required on the gateway machine .You can download it from here, after that the scheduled refresh could be configured successfully.
In addition, there is an idea about adding Access as data source for on-premises gateway, you can vote it up.
Regards,
LydiaDear Sokon and Anonymous
First of all, thank you for your post!
I have a PowerBI model which combines data from an Oracle and different MS Access dbs.
Unfortunately we are stuck with the 32 bit version and we can’t change it.
My goal is to set up a scheduled refresh with the enterprise gateway and the different 32 bit MS Access dbs.At the moment I have installed:
- PowerBI Desktop 32 bit
- Enterprise Gateway 32 bit and configured with the Oracle DB
Is it even possible to use the same approach as you did and set up a scheduled refresh via ODBC?
Thanks in advance.Anonymous's comment is not about the enterprise gateway but about the personal gateway. In that case, people install their own gateway and are likely to run into 32bit/64bit issues depending on various circumstances, one of them being the version of the installed Office.
As far as i know, there's only one version of the "On-Premises Data Gateways" that should be 64bit.
In your case, i think you should be fine. In my scenario we use 32bit as well.
It should also be possible to mix 64bit und 32bit db drivers on a gateway. You just can't run both the Access 32bit an 64bit driver on the same machine - or at least not without additional tricks.
Hope that helps!
