Unable to connect to Microsoft SQL Server "An Error happened while reading data from the provide..."

[Anonymous]

I get the following erro when trying to connect to the Microsoft SQL Server to get data from a database. When I put the server name it does this, but when I put the IP address of the server, it works fine.

Details: "An error happened while reading data from the provider: 'Could not load file or assembly 'System.EnterpriseServices, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a' or one of its dependencies. Either a required impersonation level was not provided, or the provided impersonation level is invalid. (Exception from HRESULT: 0x80070542)'"

[JordiDeGirona]

Hi

I tried to reinstall NET, register and unregoster DLL, etc, etc.

Opening Excel with admin can open a query, but not with my usual user.

Finally, ther solution was:

Create a query that works opening excel with admin user, and save the excel file

Open excel with regular user, and open file saved in previous step

Go to Edit query (Power Query Editor) -> Configure data source button -> Edit permissions ->Uncheck cipher connection

Solved

Any new query you create will work as usual

[Anonymous · Accepted solution]

Hi All,

I managed to solve this issue. It was not a .net issue. I previously connected to a datasource using different credentials. Despite me using my current credentials for the new power bi file connection, it was still trying with that other credentials. I had to clear all connection permissions and restart my Power BI and then it worked.

[Anonymous]

Just restart the Power BI desktop, and you will be connected to your SQL database. 

[adza]

It did help. And I spent many hours on researching in regards to the error.

Strange bahaviour of PBI. It is enough to change password to get into this error.

Great tip. Thanks a lot!

[mwegener]

Hi @Anonymous ,

look at this, seems to be a similar problem.

https://community.powerbi.com/t5/Desktop/Unable-to-Connect-to-SQL-Server/td-p/202262

If I answered your question, please mark my post as solution, this will also help others.

Please give Kudos for support.