Using MS Access as a data source with On-Premises Data Gateway

[Sokon]

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! 

[JoshT]

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.

[sjm334]

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

[JoshT]

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

[sjm334]

Thank you so much for the extra information.  This makes sense and I thought this was the case.  I was able to confirm that in service.msc it shows the gateway as running with Log On As NT_SERVICE\PBIEgwService.  

However, when I navigate to this path: [gateway host]\C$\Users\ I don't even see user PBIEgwService let alone am able to access it.  Am I missing another step in my configuration or did was there something wrong with my installation?

[JoshT]

If you're using the path exactly as I have typed it, it may not work because of policy restrictions at your organisation on accessing the hard drive externally (i.e. the C$ syntax). The alternative would be to log onto the box directly as an administrator, then go to C:\Users\.

FYI as an added bonus if you're a gateway admin and you haven't been to this folder before, navigate to C:\Users\PBIEgwService\AppData\Local\Microsoft\On-premises data gateway\Report and you get a bunch of reports on the queries that are passing through it!

[valdo34]

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"

[Brian_M]

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.

https://support.office.com/en-us/article/download-and-install-office-365-access-runtime-185c5a32-8ba...

Hope that helps someone!

[BWL]

Dear @Sokon and @v-yuezhe-msft

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. 

[Sokon]

@v-yuezhe-msft'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!

[BWL]

@Sokon, you are right! I  have the 64 bit "On-Premises Data Gateways" installed. 

I tried your approach and I set up an file DSN for an MS Accces DB. 

But what is exactly the string I have to use, (Gateway & Desktop) when I try to connect through DSN file?

Thank you!

[Sokon]

Using a file DSN is not my approach and it won't work.  Use a DSN-less connection. Example for that is in the main post.

[BWL]

Sorry!! It is all quite new for me! 😉
I tried your set up, and used the DSN-less connection. 

But I get the following error. 

Statuscode:	400
Foutcode:	DMTS_PublishDatasourceToClusterErrorCode
Tijd:	Wed Jul 11 2018 16:32:20 GMT+0200 (Midden-Europese zomertijd)
Versie:	13.0.5933.161

Warehouse:	Kan geen verbinding maken met de mashup-gegevensbron. Raadpleeg de foutdetails voor meer informatie.
Onderliggende foutcode:	-2147467259
Onderliggend foutbericht:	De eigenschap 'Driver' met de waarde '{Microsoft Access Driver (*.mdb, *.accdb)}'komt niet overeen met een geïnstalleerd ODBC-stuurprogramma.
DM_ErrorDetailNameCode_UnderlyingHResult:	-2147467259
Microsoft.Data.Mashup.ValueError.ClientLibraryName:	Microsoft Access Driver (*.mdb, *.accdb)
Microsoft.Data.Mashup.ValueError.DataSourceKind:	Odbc
Microsoft.Data.Mashup.ValueError.DataSourcePath:	dbq=*path*;driver={Microsoft Access Driver (*.mdb, *.accdb)}
Microsoft.Data.Mashup.ValueError.Reason:	DataSource.MissingClientLibrary

[Sokon]

Looks like you have the wrong driver installed on the gateway, if any. For a older driver you need to change the connection string like that: 

driver={Microsoft Access Driver (*.mdb)};dbq=C:\database.mdb

[BWL]

I just checked and I have the drivers installed, but it still doesn't work. 

Just to be sure, I have installed now:

• PowerBI Desktop 32 bit
• MS Acces 32 bit
• Enterprise Gateway 64 bit
• Microsoft Acces Database Engine 2010 32 bit   on the gateway server
  
  

You have exactly the same set up right?

[Sokon]

I think so, yes!

[BWL]

@Sokon,

Thank you for your reply.
I have exactly the same set up, but I don't get it work.
Still can't connect because of the "wrong driver specified". 

I think the gateway 64 bit just can't connect with the 32 bit driver. 

Hope i'll find another solution someday. 🙂

[Sokon]

@BWL: I had to move the Access connection and therefore had to redo everything I wrote on another machine. I updated the main post accordingly and you might want to try again. I added information about what I installed and fixed a wrong information in the permission part.

[v-yuezhe-msft]

@Sokon,

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,
Lydia