Not able to add a data source with ODBC connection on on-premise gateway

[vinesh]

I want to specify these two setting when connecting to a database: 

multisubnetfailover=True;applicationintent=ReadOnly

I'm using an ODBC connection to connect to the  databaseand the connection string looks like this:

driver={SQL Server Native Client 11.0};server=XYXServer;database=MyDatabase;multisubnetfailover=True;applicationintent=ReadOnly

Im able to use the connection string similar to the above to connect and access the data in the PowerBi Desktop and build my reports. Issue is when I use the same connection string to add a ODBC DataSource to the Enterprise gateway(From the manage Gateways page) it fails with the below error.

Error:

Version:

13.0.1700.1333

Underlying error code:

-2147467259

Underlying error message:

The 'Driver' property with value '{SQL Server Native Client 11.0}' doesn't correspond to an installed ODBC driver.

DM_ErrorDetailNameCode_UnderlyingHResult:

-2147467259

Microsoft.Data.Mashup.ValueError.ClientLibraryName:

SQL Server Native Client 11.0

Microsoft.Data.Mashup.ValueError.DataSourceKind:

Odbc

[v-shex-msft]

Hi @vinesh,

You can refer to below thread about how to configure the odbc data source:

Enterprise Gateway with ODBC

In addition, why don't you directly get data from sql datasource? Sql connector was higher performance than odbc driver.

Regards,

Xiaoxin Sheng

[vinesh]

Hi, that didn't help.  The issue still exists. Are there different formats of the ODBC connection strings for PowerBI Desktop and the Enterprise Gateway?

[v-shex-msft]

Hi @vinesh,

>>The 'Driver' property with value '{SQL Server Native Client 11.0}' doesn't correspond to an installed ODBC driver.

Based on your error message, the issue is related to odbc client version, please modify it to the current installed version.

Reference link:

Microsoft SQL Server ODBC Driver connection strings

Check the ODBC SQL Server Driver Version (Windows)

Regards,

Xiaoxin Sheng