Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors