Connect PBI desktop to local Microsoft sql server on same device?

[h4tt3n]

Hello fellow developers,

I want to test a report against a local Microsoft SQL server (2019) that is installed and populated with test data on my local machine. I have no problem accessing and manipulating the database through SSMS, but I can't log into the db through PBI. What am I missing?

Help and advice is much appreciated.

Thanks in advance, Mike

[amitchandak · Accepted solution]

@h4tt3n , how are you trying that . I was able to connect with machine name and localhost both

refer: https://youtu.be/My0bLn9voo4?list=PLPaNVDMhUXGbKatyDdOhGbTL3xW2Xy6pA&t=108

or refer

https://docs.microsoft.com/en-us/power-bi/connect-data/service-gateway-sql-tutorial

https://hevodata.com/learn/connect-sql-server-to-power-bi/

[MiraAciu]

Hi Mike,

I presume you are using Windows auth on both systems as they are local. 

SSMS is a 32 bit app, Power BI Desktop can be installed on 32 or 64 bit. Check on SQL Server 2019 Configuration Manager if Shared Memory is enabled for both 32 and 64 aplications(SQL Native client configuration -> client protocols). To be sure enable all protocols.

Or, maybe, is better to post the error message here 🙂

[h4tt3n]

@MiraAciu I get the following error:

[MiraAciu · Accepted solution]

It seems you have encrypted connection enabled. 

Pls check this article 

https://docs.microsoft.com/en-us/troubleshoot/sql/connect/error-message-when-you-connect

[amitchandak · Accepted solution]

@h4tt3n , how are you trying that . I was able to connect with machine name and localhost both

refer: https://youtu.be/My0bLn9voo4?list=PLPaNVDMhUXGbKatyDdOhGbTL3xW2Xy6pA&t=108

or refer

https://docs.microsoft.com/en-us/power-bi/connect-data/service-gateway-sql-tutorial

https://hevodata.com/learn/connect-sql-server-to-power-bi/

[h4tt3n]

Thanks, you both were very helpful.