cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rsovitzky
Frequent Visitor

Adding ODBC connection in Power BI service fails; works locally

Greetings, 

I'm running into a roadblock attempting to add an ODBC data source via Power BI Service via +New / Data sources page from the following URL. https://app.powerbi.com/groups/me/gateways

 

Here are a few facts:

  1. The ODBC driver (for Sage 100) is 64-bit and on a workstation that contains Power BI Gateway (July 2022).  On the debug tab of the ODBC Manager, the test is successful: Connection succeeded. Datasource includes xxxx tables.
  2. The Gateway passes the network test. Its status is online.
  3. From Power BI Service (New), if I purposely mistype the DSN name (system DNS, not user DSN) I receive the following message (expected result). ODBC: ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified.   Note: This helps illustrate that Power BI Service is able to communicate with the driver:.
  4. If I provide the correct DSN, I received the following error: ODBC: ERROR [S0000] [ProvideX][ODBC Driver][PVKIO]Logon failed
    ERROR [S0000] [ProvideX][ODBC Driver][PVKIO]Logon failed.
  5. I've tried several variations with/without credentials embedded in the DSN string; always with the same result.
  6. From the workstation that contains the Gateway, I can successfully use Power Query in Excel locally to use the same DSN I am attempting to use in Power BI Service and it works fine.
  7. The network firewall was temporarily disabled; same results. 

Does anyone have any suggested troubleshooting steps?  -Rick

1 ACCEPTED SOLUTION
rsovitzky
Frequent Visitor

SOLVED! The solution required changing the Windows account used by the Power BI Gateway. https://docs.microsoft.com/en-us/data-integration/gateway/service-gateway-service-account However, the twist is that I followed the instructions in the above link and the process completed without an error, but the Gateway service account did NOT change to the account I had specified, instead it changed to LocalSystem. I had to manually change the account used by PBIEgwService  via windows Services, verified that the account was then listed in Service Settings in the Gateway and then I was able to successfully add the data source. 

View solution in original post

5 REPLIES 5
rsovitzky
Frequent Visitor

SOLVED! The solution required changing the Windows account used by the Power BI Gateway. https://docs.microsoft.com/en-us/data-integration/gateway/service-gateway-service-account However, the twist is that I followed the instructions in the above link and the process completed without an error, but the Gateway service account did NOT change to the account I had specified, instead it changed to LocalSystem. I had to manually change the account used by PBIEgwService  via windows Services, verified that the account was then listed in Service Settings in the Gateway and then I was able to successfully add the data source. 

rsovitzky
Frequent Visitor

Hi @GilbertQ , thank you for replying. I could have been more clear in point 3 of my post: yes, it is a system DSN. Yes, authentication is basic and I am certain that the name and password match the DSN connection string.

Ok thanks it does appear then that somehow the username/password is different and that is why it is failing.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

@GilbertQ to test, I created another DSN and set the database (Sage 100) password for the user to a simple, non-secure one and verified that the DSN works locally on the workstation in Power Query for Excel but fails from Power BI Service with the original error. I then repeated the process with a different, existing name and password with the same results: locally the DSN works but I cannot add it via Power BI Service. At this point, there is [almost] no chance that the name/password is the problem. 

 

I can follow these exact same steps connecting to Sage 100 on a different Gateway/server/database and successfully create a Date Source connection from Power BI Service. So, I am confident that the correct steps are being followed, but I am puzzled about what is different or what might be causing the ODBC login failure in environment A vs. B. Any hints or suggests are greatly appreciated.

GilbertQ
Super User
Super User

Hi @rsovitzky 

 

If you can make sure that the authentication is set to basic and you put in the same username and password as to what you have in your DSN Connection.

 

Also can you make sure that in the Windows Server you open the 64bit ODBC Connection and the connection is a System DSN?

 

GilbertQ_0-1658960165908.png

 

 

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

Helpful resources

Announcements
Power BI Show Episode 10 Recap

The Power BI Community Show

Watch the playback when Amit Chandak, a Power BI Super User, demos how to use Field Parameters to make reports more dynamic.

Health and Life Sciences Power BI User Group

Health and Life Sciences Power BI User Group

Power BI specialists at Microsoft have created a community user group where customers in the provider, payor, pharma, health solutions, and life science industries can collaborate.

Ignite 2022

What's Next at Microsoft Ignite 2022

Explore the latest innovations, learn from product experts and partners, level up your skillset, and create connections from around the world.

Top Solution Authors
Top Kudoed Authors