Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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:
Does anyone have any suggested troubleshooting steps? -Rick
Solved! Go to Solution.
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.
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.
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.
@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.
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?