Logging into Sql server failed

[Anonymous]

I wonder if anyone can help please? I am trying to set up a data source to the gateway via a connection to a SQL server. 

In desktop it works fine but when I attempt to add to the gateway I get the following error message - 

Unable to connect: We encountered an error while trying to connect to . Details: "We could not register this data source for any gateway instances within this cluster. Please find more details below about specific errors for each gateway instance."

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.

Anyone know what is causing this? Thanks 

[GilbertQ]

Hi @Anonymous 

If possible I would suggest a service account if that cannot be done, then when the password changes then you would have to update the gateway data sources?

[GilbertQ]

Hi @Anonymous 

As per the error when setting this up in the PBI Service on the Gateway settings adding in a data source, are you using a login which has got access to the database?

Can you also confirm that the On-Premise Gateway can access the SQL Server. You can test this by logging into your server where the On-Premise gateway is installed and connect to the SQL Server with valid login credentials.

If the above works put in those same details in the data source.

Here are more details: Tutorial: Connect to on-premises data in SQL Server - Power BI | Microsoft Docs

[Anonymous]

Hi  @GilbertQ  thanks for your reply 🙂 

We are able to login to the database through SSMS with the credentials that are producing the error on the Gateway, we can also use these credentials via Power BI Desktop using a Organisational Account Authentication method, so the login does have access to the database. Furthermore the problem does not seem to be with the Gateway, as we are able to switch the datasource to use different login credentials and connect to the SQL Server.

The problem with the credentials that work, is they are not for a service account, and therefore will require a password reset every so often.

Can you offer any more advice based on this info?