Forum Discussion
Unable to create connection with SQL Server DB from Power BI Service
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.
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?
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.
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.