Unable to create connection with SQL Server DB from Power BI Service

[refayetanzir]

Hello,

I hope this message finds you well. I am a Data Analyst at a large transportation management company . We are  in the process of setting up the structure for our new Business Intelligence pursuits. We have encountered a persistent issue with Microsoft Power BI that requires some assistance for resolution.

The primary objective is to set up a scheduled refresh for our reports within Power BI Service. However, we are currently facing difficulties authenticating the connection between Power BI Service and our SQL Server database. The report, which was prepared in Power BI Desktop, is hosted on an application server . The On-Premises Data Gateway is installed on the same server.

The data source for the Power BI report resides on a database server . There are no connectivity issues between the application server and the database server ,as the Power BI report hosted on the application server can retrieve data from the database server. Additionally, the report was successfully published from Power BI Desktop to the designated workspace within Power BI Service, indicating that outbound connectivity from the application server to Power BI Service is functioning properly.

We have also confirmed that the necessary ports for both inbound and outbound connectivity between Power BI Service and the application server are open and operational. This has been further validated by successfully establishing a connection from Power BI Service to a data source consisting of flat files, which are also hosted on the same application server and use the same On-Premises Data Gateway. We were also able to establish connection from Power Bi Service with flat files placed in the DB Server. 

Furthermore we  created a new service account on the application server and added it to the On-Premises Data Gateway. The service account was also granted user permissions to the database server . However, when attempting to authenticate using Windows authentication mode, we are getting the same error. We also tested SQL Server authentication mode, but that approach was unsuccessful as well. I have attached a screenshot of the error message for your reference.

Any insights to help resolve the issue would be greatly appreciated.

Thanks

[pborah]

Where exactly is the point of failure in your process and are there any error messages you are receiving from Power BI service? I'm sorry I'm not seeing any attached screenshot.

[refayetanzir]

Hi @pborah ,

The error is encountered when we are trying to create a connection with the SQL server database from Power BI Service. The following is the error message:

Unable to create connection for the following reason: Logging into Sql server failed. Possible reasons for this error include an invalid authentication mode, a missing login in Sql server, a login from an untrusted domain or a problem contacting the domain controller for the authentication request.



Details: 10.50.100.54 Prepod App Server2: Received error payload from gateway service with ID 3985950: SqlException encountered while accessing the target data source. Please have this information handy if you choose to create a support ticket. Session Id: 25287990-fd00-446f-a8e2-33800e162e40 RequestId: e44dd29b-c6b7-4f05-822c-f8c703c16962 Cluster URI: https://api.powerbi.com Status code: 400 Time: Wed Mar 12 2025 12:04:01 GMT-0500 (Central Daylight Time)


We checked in ssms and the request is not even reaching the database server from Power Bi Service. 

[pborah]

@refayetanzir can your data gateway reach that specific SQL server? Additionally have you tried using the fully qualified server name or even the ip of the SQL machine when trying to connect from service?

Also can you clarify what you mean by your Power BI report being hosted on an application server? I'm confused because Power BI service is where your report lives to be consumed by your end users. Why this extra step? What is the path here? SQL->AppServer->Service or SQL->Service->AppServer?