CODEWITHSUNDEEP
sql-serverpowerbireportreportserver
groucho
groucho

Reputation: 3

Can't connect to a SQL Server database when managing a PowerBI report server report

I created a Power BI report which takes data from a SQL Server database. Everything works fine when I upload it to Power BI online service.

But, when I upload it to Power BI report server, I click manage and select data sources to connect the report to the SQL Server datasource, it doesn't work.

With Windows authentication, it always throws wrong credentials.

With base authentication it always throws this error:

Unable to connect
The report server was unable to connect to the data source with the information you entered. Make sure you have entered the connection string and credentials correctly.

Microsoft SQL: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server cannot be found or cannot be accessed. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Unable to open a connection to SQL Server)

I tried different logins and creating new accounts in SQL Server, trying to upload the dataset separately all to no avail.

Upvotes: 0

Views: 714

Answers (1)

Ph1reman
Ph1reman

Reputation: 576

This is a network issue. Power BI cannot reach your instance. To be clear, if this is a local server what you would usually install is a gateway so that Power BI as a cloud service can communicate with your onprem environment through the firewall.

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

To be clear, the reason why this works when you work locally is because you're on the local network, and when you upload this to Power BI it is no longer local and therefor cannot reach your server. When you've got the report in the cloud you should enter the dataset/semantic model settings where you can select a gateway for the data to pass through, but only if you've got one setup.

Generally speaking, I would not recommend opening the firewall towards Power BI for your network. Not only is the list of IP's from Microsoft quite extensive, but it updates quite a bit. As such, gateway is my recommendation.

Upvotes: 0

Related Questions