Cant connect to ODBC in power query online

[Soof1234]

Hi,

Anyone familiar with connecting to an ODBC database from Progress? Already spent hours now trying to connect. I already have a gateway set up.

The format of the string i am using is the following:

Driver={<Drivername>};HOST=<host>;PORT=<port>;DB=<database>;UID=<userid>;PWD=<password>;DIL=0;

Most of the time this is the error that i get. DMTS_PublishDatasourceToClusterErrorCode.

Already googled it and might be firewall issue, but perhaps any of you already solved it on another way. Would be gratefull if someone could provide me the solution. Thanks in advance!

Kind Regards,

Soof1234

[v-jingzhan-msft]

Hi @Soof1234 

According to the error code DMTS_PublishDatasourceToClusterErrorCode, here are a few steps you can take to troubleshoot and potentially resolve the issue:

1. Check Firewall and Network Settings: Since you suspect a firewall issue, verify that the necessary ports and endpoints required for the On-Premises Data Gateway and Power Query Online are allowed through any firewalls or network security devices. This includes ensuring that the gateway can communicate with the Power BI service. More details on the required ports and endpoints can be found here: Adjust communication settings for the on-premises data gateway.

2. Review ODBC Driver and Connection String: Double-check that the ODBC driver specified in your connection string is installed and properly configured on the machine where the On-Premises Data Gateway is running. Additionally, review the connection string parameters to ensure they match the expected format and values for your specific ODBC driver and database.

Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!

[Soof1234]

Hi @v-jingzhan-msft ,

Thanks for your reply. Came a little bit further today. Was able to enter the DSN name in the connection string and installed the gateway on the same server as where the DSN from ODBC is stored. Following error occured: The specified DSN contains an architecture mismatch between the Driver (32-bit) and Application (64-bit)

As the progress database we use is not supported on 64 bit, I need to check for another solution now as Fabric gateways always use 64 bit. I am able to connect to the ODBC data with the ODBC connector when i download Power BI Desktop 32 bit on the virtual machine where our data is stored. This however leaves me with the problem that I am not able to refresh the data on Power BI service through the gateway. I prefer to set everything up in Microsoft Fabric.

I encountered that some people did use PostgreSQL connector instead of ODBC to gather the data. However, you may have another solution to solve this problem to bridge the connection between 32 and 64 bit.

Kind Regards,

Soof1234