Connecting to ODBC Via Data Gateway

[PHILAM2]

I am having difficulties connecting to a dataset via ODBC. Details are as follows:

On my host computer I have a Power BI desktop model that connects to my data via a user dsn. The settings in this user dsn specify both the driver to be used and the data path. When I first connect from the desktop software it asks me for my username and password, thereafter these seem to be saved and all is well. I have then succesfully published the semantic model to the Power BI service. The issue comes when I try to connect via a gateway to refresh. The gateway seems to be working ok (within the Power BI Web Service it shows as "online"), the problem is with setting up the ODBC data connection itself within the web service. It asks me to select my gateway, and give it a name. From "connection type" I am selecting "ODBC". For "connection string" it initially prompted me with dsn=abc123 (i.e. simply the name of the dsn that had pulled through with the semantic model) so I felt no need to change this (unless this is where I am going wrong, do I need to put driver details in here etc)?

I then select "BASIC" as my authentication method and type in the username and password for my DSN (which I know are correct). However when I attempt to save the connection I receive the following error message):

Unable to create connection for the following reason: Unable to connect to the data source. Either the data source is inaccessible, a connection timeout occurred, or the data source credentials are invalid. Please verify the data source configuration and contact a data source administrator to troubleshoot this issue.

It seems to me that there are two possible issues here: 1) whilst the gateway is online something within its settings is not letting the request through or 2) I need to be entering more information in the "connection string" field other than the prompt of "dsn=abc123". Any suggestions would be very much appreciated!

[v-dineshya · Accepted solution]

Hi  @PHILAM2,
Thank you for reaching out to the Microsoft Community Forum.

Please check below things:

1. User DSN vs System DSN
Issue: Power BI Gateway runs as a Windows service (usually under a service account), and cannot access User DSNs only System DSNs.

Solution: Recreate your ODBC DSN as a System DSN, not a User DSN. Go to ODBC Data Source Administrator (64-bit). Under the System DSN tab, create the same DSN (abc123) with the same driver and data path. Make sure it is accessible system-wide.

2. Gateway Machine and DSN Match
Issue: The DSN (abc123) must exist on the same machine where the gateway is installed, and match exactly in spelling.

Solution: Verify that the gateway is installed on the same machine that has the abc123 DSN. If the gateway is remote, recreate the same abc123 System DSN on that machine.

3. Connection String Details
Issue: The connection string might need additional information, depending on the ODBC driver you're using (which isn’t specified in your post).

Solution: While dsn=abc123 is often enough, some drivers need more, e.g., a driver name, server address, or explicitly setting trusted connection, UID/PWD, etc.

Example:

Driver={YourDriverName};DSN=abc123;UID=your_user;PWD=your_password;

Note: Try adding Driver= if your ODBC connection requires more specificity.

4. Authentication Type and Credentials
Issue: The authentication used may differ when connecting via the gateway.

Solution: Confirm that the username/password being passed matches what the DSN expects. Some DSNs use Windows Authentication, others use Basic/SQL Auth. If your DSN uses integrated security, try setting the credential type to Windows, and provide valid domain credentials.

5. Driver Compatibility or Timeout
Issue: The ODBC driver may not behave identically under the gateway (especially older or non-Microsoft ones).

Solution: Try a test connection using another tool (e.g., Excel, Power BI Desktop on the gateway machine) using the same DSN. Confirm that the driver version is up-to-date and compatible with 64-bit systems.

Please refer community threads and articles.

Solved: Adding ODBC connection in Power BI service fails; ... - Microsoft Fabric Community

Solved: Adding ODBC datasource to gateway - Microsoft Fabric Community

Solved: ODBC Connection Error - Driver Error - Microsoft Fabric Community

Solved: Can't connect ODBC datasource to gateway - Microsoft Fabric Community

Solved: 32 Bit ODBC driver with 64 Bit Power BI - Microsoft Fabric Community

Power BI gateway and Report Builder support for ODBC data sources - Power BI | Microsoft Learn

Troubleshoot the on-premises data gateway | Microsoft Learn

If my response has resolved your query, please mark it as the Accepted Solution to assist others. Additionally, a 'Kudos' would be appreciated if you found my response helpful.

Thank you

[v-dineshya]

Hi @PHILAM2 ,

If my response has resolved your query, please mark it as the Accepted Solution to assist others. Additionally, a 'Kudos' would be appreciated if you found my response helpful.

Thank you

[v-dineshya]

Hi @PHILAM2 ,

If my response has resolved your query, please mark it as the Accepted Solution to assist others. Additionally, a 'Kudos' would be appreciated if you found my response helpful.

Thank you

[v-dineshya]

Hi @PHILAM2 ,

If my response has resolved your query, please mark it as the Accepted Solution to assist others. Additionally, a 'Kudos' would be appreciated if you found my response helpful.

Thank you

[Akash_Varuna]

@PHILAM2 Ensure the gateway is correctly configured and has access to the DSN. Update the connection string to include driver details. Use a System DSN instead of a User DSN, as gateways require it. Test the connection outside Power BI and check gateway logs for detailed errors if it still fails.