Unable to connect Power BI to SQL

[WW]

Hello all, 

I am facing this error for a few days and it's driving me crazy. I just downloaded Power BI Desktop and hope to connect it to my SCCM database. I have disabled my firewall, check my IP portal, remote connection from SQL is enabled.

I am able to login to my SQL, my accout is fine, I am able to connect to the DB from the same connector, just not with Power BI. Is there any settings I might have missed?

Error Message(s) as follow, (it seems to change between the variant)

1) Data Source.Error: Microsoft SQL: A network -related or instance-specific error occurred while establishing a connection to SQL server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL server is configured to allow remote connections. (Provider: Named Pipes Provider, error 40 - Could not open a connection to SQL server)

2) DataSource.Error:

3) User was not authorized (Prompted at credential popup).

Help, anyone! Thanks!

[v-yuezhe-msft]

Hi WW,

Could you please firstly use SQL Server Management Studio to connect to the remote SQL Server instance and check if it is successful? If it also fails, please follow the steps in this blog to troubleshoot this issue.

In addition, we need to verify that if you use a Windows login or SQL login to connect to SQL Server, and how you specify the settings in the following screenshot.

Thanks,
Lydia Zhang

[WW]

Hi Lydia,

I am able to successfully connect to the remote SQL Server instance from another server. I have used both Windows and Database login, but error appears on both.

[v-yuezhe-msft]

Hi WW,

Please check that if you can successfully connect to SQL Server from other application(such as Excel) which is installed in the same server with Power BI Desktop.

And use “IP address, port” or “tcp:computername” as server name to connect to SQL Server in Power BI Desktop.

Thanks,
Lydia Zhang

[WW]

Hi Lydia,

Thanks for your reply, I have the same error using Excel (Power Query). I tried both tcp:computername and IP,port is still got stuck at "The User was not authorized" message. However I am able to connect from another machine 's SQL management studio to this DB and it works. Not sure what else I have missed...

[v-yuezhe-msft]

Hi WW,

Based on my test, in the following screenshot, when we specify a login account that doesn’t exist in SQL Server, or we specify an existing SQL login in the Windows page or specify a Windows login in the Database page, the above error message could occur.

Please describe more details about that how you specify the settings in the above screenshot. Also make sure that you right click Power BI Desktop and choose “Run as administrator” to start it. Besides, delete the cache and the old data source connections related as described in this blog.

Thanks,
Lydia Zhang