SQL Server Get Data fails... cannot fathom why

[randyvolters]

Trying to show this tool to mgmt, getting very frustrated as the most basic operations do not succeed.

Very simply, I wanted to connect to an SQL Server instance running on the same machine Power BI Desktop is installed on.

I have tried using the Server Name proper, 'localhost', (and then based on a post I saw in this community I even tried the IP address) with 'use Windows authentication' checked.

Each attempt I am greeted with a message about the system not supporting an encrypted connection to which I reply that using an unencrypted connection is fine.  Once I do that after a few seconds I get 'The user was not authorized'. 

This seems an absurd message to get since SMSS running on the same machine is connected using Windows Authentication to the database I wanted to get data from.  In SMSS I can run any query I want against that database no problem.

I'm gob-smacked that this most simple and basic operation apparently doesn't work in Power BI Desktop.

Anyone else have this issue?

[v-yuezhe-msft]

Hi @randyvolters,

Adding to other posts, make sure you launch Power BI desktop with admin privileges, go to “File –> Options and Settings –> Options –> Data Load” to clear cache , then check if you can connect to SQL Server. If the issue persists, please uninstall Power BI Desktop and reinstall it, then connect to SQL from it.

Thanks,
Lydia Zhang

[Seth_C_Bauer]

@randyvolters Check out this related link with the how/why of the Encryption message. I saw that today at a client, and he was able to pass through and access the database after clicking ok on the warning message.

Have you checked the SQL logs or Event Viewer for any errors when you try to log in? This should lead you to an answer as to why your creds are being rejected. If you are positive that your creds are good and you have access to the database (which it sounds like you are) - then I would delete the cached data source as there might be something that got messed up in it initially or since last connection. You can delete the Power BI Cached data source by going to File -> options -> Data Source Settings -> global -> (Select the offending data source) -> clear permissions and a delete data source prompt will pop up. Delete the data source. And try to re-connect to the SQL instance.

[ankitpatira]

@randyvolters As @ashishrj mentioned never faced any issue connecting to SQL Server. You should try using sql authentication as well as alternate credentials. It could be the permission to connect to sql server for that particular user.

[ashishrj]

@randyvolters I haven't faced any such issues when working with Power BI Desktop tool using SQL Server as a database. 

You can try creating a new SQL Login and first try using it in SSMS and then in Desktop tool although Windows Authentication should work like a charm 🙂