ODBC in On Premise Gateway Data Source Error

[Tad17]

Hey Guys,

I'm attempting to connect to an ODBC source using the On Premise Gateway. It is currently working using the Personal Mode, but I am getting ready to move the Gateway to the server so I'm testing and learning about the Non-Personal Mode version. I've managed to get it to connect to everything else, including directly to the SQL servers, but I cannot get it to connect to the ODBC. I've copied the error information below. Our corporate IT guys prevent us from doing anything with screenshots or pictures so I apologize for the format:

Unable to connect: We encountered an error while trying to connect to . Details: "We could not register this data source for any gateway instances within this cluster. Please find more details below about specific errors for each gateway instance."Hide details
Activity ID: 234ea41a-4777-427c-b2e9-5fced6140b3a
Request ID: aee9e675-2570-596d-d9d4-c73586c904a6
Cluster URI: Unpostable html
Status code: 400
Error Code: DMTS_PublishDatasourceToClusterErrorCode
Time: Fri Nov 08 2019 15:13:17 GMT-0600 (Central Standard Time)
Service version: 13.0.11271.222
Client version: 1910.4.033
Power BI Test: 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=************
Microsoft.Data.Mashup.ValueError.Reason: DataSource.Error

[Anonymous]

I have exactly the same problem. Personal gateway works fine for me, but when trying to set up the other on premise gateway i get the same error: Unable to connect to the data source undefined.

@Tad17 did you solve this by chance?

Thanks!

[Tad17 · Accepted solution]

@Anonymous I did not. I ended up cheating. I set up the personal gateway on the server and used the regular gateway on my computer for the reports I have to manually update. It has been working ever since. I have found numerous other people who have problems connecting to ODBC with the On Premise Gateway even though it works just fine in the Personal On Premise Gateway. Connecting through SQL via On Premise Gateway (Non-Personal Mode) worked just fine. Same steps to connect and everything., but for some reason ODBC was throwing up the error. So I just got permissions to connect directly without the ODBC and set that up on the On Premise Gateway for myself and left the personal one on the server. This won't be an issue for now as I am the only person using the gateway.

However, the "server" for my department is actually the VP's desktop so the situation is less than ideal as any changes I make to the reports that have to be done in desktop now have to be done from his computer. I tried making the changes on my computer and then changing the file path to look at his user since all of the pbix files are on one dirve, but since it can't apply the changes to the queries it won't save the changes so I have to do it from his computer. I'm looking to get this all changes in the near future.

[venal]

@Tad17 

Can you please check for the credentials that user name domain/username (or) username..

Please try to check and let us know if you have any issues..

If this post helps, then please consider Accept it as the solution to help the other members find it more

If this post was helpful may I ask you to mark it as solution and give it some kudos?

[VasTg]

@Tad17 

First, Do you have the Datasource setup complete? If not, create one under settings->manage gateway/cluster and give permission to Users.

After that, map the dataset gateway connection to the datasource created in the first step.

[Tad17]

Setting up the data source is the issue. This source is the exact same as the one being used in the pbix file and by the personal gateway. The only thing I could possibly be doing differently is the connection string since that part is optional during connection setup in desktop, but apparently required in the on-premise gateway setup. The original query was pulled from a power querry in excel, so after trying just the dsn= function in the connection string as I assumed was needed and then every combination of what the conection string had been in excel I continued to receive the error.

To the other reply, I'm sure the credentials are correct as they were copy/pasted from power query and I confirmed them with our IT department as well as made sure the power query was still working in excel and MySQL.