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

[Anonymous]

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

[v-priyankata]

Hi @Anonymous 
I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please Accept it as a solution and give it a 'Kudos' so others can find it easily.
Thank you.

[v-priyankata]

Hi @Anonymous 
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you.

[v-priyankata]

Hi @Anonymous 

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[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.

[Anonymous]

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]

@Anonymous 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?

[Anonymous]

Hi @pborah , I placed some flat files in the  DB server where my SSMS is installed. I connected to the flat files from power bi desktop and published it on to power bi service. From Power Bi Service, I was able to connect to those flat files placed in the same  DB server where SSMS is located. So I am able to connect to a folder in the same DB server my SSMS is located. However, when I am trying to connect to the same server as a SQL Server connection from Power BI Service, I am unable to authenticate the connection. 

For Server* in the New Connection window in Power BI Service , I have used  the Server IP. I have also tried using the fully qualified server name.

By Power BI report being hosted in the app server, I meant Power BI dekstop is hosted in the app server. Power BI service is where the report is hosted in the cloud for it to be consumed by the end user. The path is SQL->Power Bi Desktop in the App Server->Published to Power BI Service ->SQL via the on premises data gateway.

Let me know if you need further clarifications. If you have time, I would even appreciate jumping on a brief screen sharing call to help trouble shoot the issue.

Thank You.

[pborah · Accepted solution]

@Anonymous thanks for explaning the scenarios. Unfortunately I cannot jump on a call as I'm also at work. I just hang around in the forums in my downtime which lately seems to be a fair bit lol.

So to start with, your gateway being able to reach flat files on the server machine is completely different from it being able to reach a SQL database hosted on the same computer. I strongly suspect this is a SQL server configuration issue. As in - not the computer that is hosting SQL Server but the application itself. Part of your error message also supports this -

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.

My SQL capabilities stop at writing queries so perhaps a DBA might be able to chime in here? I'd suggest taking a look at the security configurations for your SQL server instance as well as the particular database you're trying to hit.

Also keep in mind SSMS is not SQL Server, it's just a management tool. You can have SSMS installed on an entirely different computer compared to where SQL server is being hosted, so long as proper authentication has been setup, you'll still be able to manage your databases from outside the SQL server machine.

I know this is not a solution but I hope it points you in the right direction.