Having problems with connecting MS Access .accdb to the On-premises Enterprise Gateway using ODBC.

[Romer]

Access is connecting perfectly into the Power BI Desktop via ODBC.

Also can connect ok via Personal Gateway (Personal and Enterprise not on same machine)

​​When trying to connect Power BI Service via the Enterprise Gateway, get the following error:

Request ID:	5341b472-146d-1f23-5e72-aea6cec9e6e7
Cluster URI:	https://wabi-australia-east-a-primary-redirect.analysis.windows.net/
Status code:	400
Error Code:	DMTS_PublishDatasourceToClusterErrorCode
Time:	Mon Jan 13 2020 05:22:48 GMT+1030 (Australian Central Daylight Time)
Service version:	13.0.11747.315
Client version:	1912.2.031

SUDEL-APPGW:	Unable to connect to the data source undefined.
Underlying error code:	-2147467259
Underlying error message:	ODBC: ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
DM_ErrorDetailNameCode_UnderlyingHResult:	-2147467259
Microsoft.Data.Mashup.ValueError.DataSourceKind:	Odbc
Microsoft.Data.Mashup.ValueError.DataSourcePath:	dsn=ReportML_System_DSN_64
Microsoft.Data.Mashup.ValueError.Reason:	DataSource.Error

We have successfully connected SQL database via the Enterprise Gateway

Our setup:
Azure cloud server (Ms server 2019)
Terminal services
Ms Access (2019)
on-premises Data Gateway

MS Access Database Engine 2010   14.0.7015.1000

[mohdmohideen200]

"DMTS_PublishDatasourceToClusterErrorCode" is a technically valid error code. Its "grammar" is that of a machine readable error, designed for clarity within a specific technical context.

[v-lid-msft · Accepted solution]

Hi @Romer ,

If you are using a custom ODBC connector to connector in gateway , please consider the following limitation in this document:

• Make sure the folder you create is accessible to the background gateway service. Typically, folders under your user’s Windows folder or system folders won’t be accessible. The on-premises data gateway app shows a message if the folder isn't accessible. This instruction doesn't apply for the on-premises data gateway (personal mode).
• For custom connectors to work with the on-premises data gateway, they need to implement a “TestConnection” section in the custom connector’s code. This section isn't required when you use custom connectors with Power BI Desktop. For this reason, you can have a connector that works with Power BI Desktop, but not with the gateway. For more information on how to implement a TestConnection section, see this documentation.

Please also refer to this similar thread: https://community.powerbi.com/t5/Service/ODBC-in-On-Premise-Gateway-Data-Source-Error/td-p/841330

Best regards,

[Romer]

Used On-Premises Gateway in Personal mode on the terminal server, leaving RDP running all the time.

OCDB connection to the MS Access works good.

Scheduled update works good.