Issues Connecting SQL Server Database to Gateway for Refresh

[RyanFiting]

Hello Community - 

I am having a bit of a strange issue with my On-Prem Data Gateway and getting a SQL Server Connection working with the gateway.

I have 3 databases on on server in SQL Server, and I am able to connect to all 3 databases from the desktop without any issues.  When I push the report to the service, and assign the connections to the gateway, I have no issues with 2 out of the 3 databases.  2 of the databases on the server work and refresh when connected to the gateway, but the 3rd database (on the same server) throws an error when I try to assign the connection to the gateway.  Here is the error I get:

Unable to update connection credentials.
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:

OANDE_PBI: Received error payload from gateway service with ID 1595192: SqlException encountered while accessing the target data source.

Please have this information handy if you choose to create a support ticket.

Session Id: 372c0c20-73f8-4d33-8478-94fdc6ede927
RequestId: eb3f9fd2-18b5-46ae-94fc-a632bbc74fb8
Cluster URI: https://api.powerbi.com
Status code: 400
Time: Tue Feb 11 2025 22:24:08 GMT-0500 (Eastern Standard Time)

Have any of you encountered this error before and were you able to resolve it? The server Certificate is checked as Trusted but I am not sure what else to do for this error.  Any help is appreciated.

Thanks Community!

Ryan F

[v-saisrao-msft]

Hi @RyanFiting,

We haven’t heard back from you regarding your issue. If it has been resolved, please mark the helpful response as the solution and give a ‘Kudos’ to assist others. If you still need support, let us know.

Thank you.

[v-saisrao-msft]

Hi  @RyanFiting,

Thank you for reaching out to the Microsoft Fabric Community Support. 

 I understand you're experiencing an issue with connecting one of your SQL Server databases to the On-Premises Data Gateway for refresh in Power BI. Here are some steps you can follow to troubleshoot and resolve this issue: 

1. Please ensure that SQL Server is configured to Mixed Mode Authentication (SQL Server and Windows Authentication) rather than Windows Authentication only. You can confirm this setting in SQL Server Management Studio (SSMS) under Server Properties > Security. If any changes are made, kindly restart the SQL Server service. 
2. Please ensure that the account used in the gateway has a valid login on SQL Server and is mapped to a database user with at least “db_datareader” permissions on the specific database experiencing issues. Permissions on other databases will not suffice—each database requires explicit access. In SSMS, verify the database’s Security section for the correct user mappings. 
3. Please use SSMS or another database tool to manually connect to the problematic database using the exact credentials configured in the gateway data source. If the connection fails, the issue is likely related to SQL Server authentication or permissions, rather than Power BI. 
4. In the On-Premises Data Gateway application, please verify that the connection settings (server name, database name, authentication type, credentials) are identical to those used for the other functioning databases. 
5. Remove the data source for the problematic database from the On-Premises Data Gateway app. In Power BI Service, disconnect the data source from the dataset. Restart the gateway service. Re-add the data source in the gateway and reconnect it in Power BI Service. 
6. Please ensure that both the Power BI Service and the On-Premises Data Gateway are up to date. Begin by restarting the SQL Server service, followed by restarting the On-Premises Data Gateway service. 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly and give Kudos if helped you resolve your query.  
 

Thank you. 

[RyanFiting]

I have tried all but one of these but they did not work.

When you say 'restart the gateway' are you referring to uninstalling and reinstalling the gateway?  Then am I required to go back to all 30+ of my reports and reconfigure all of the data sources to the 're-installed' gateway?

[v-saisrao-msft]

Hi @RyanFiting,

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.

[v-saisrao-msft]

Hi @RyanFiting,

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-saisrao-msft · Accepted solution]

Hi @RyanFiting,

I understand you're experiencing an issue with connecting one of your SQL Server databases to the On-Premises Data Gateway for refresh in Power BI.

When I referred to "restarting the gateway," I meant stopping and starting the On-Premises Data Gateway service, not uninstalling and reinstalling it. 

• Via the On-Premises Data Gateway Application: 
• Launch the On-Premises Data Gateway application on the server. 
• Go to the Service Settings tab. 
• Select Restart. 

For detailed instructions, you can refer to Microsoft’s official documentation: 

Restart an on-premises data gateway | Microsoft Learn 

1. Please open SQL Server Management Studio (SSMS) and examine the SQL Server error logs (ERRORLOG). Check for any failed login attempts related to the problematic database. If the account is blocked or expired, re-enable it. 
2. Log into the problematic database via SSMS using the exact credentials configured in the gateway. If the login fails, ensure that the account has the necessary permissions (db_datareader or higher) on the specific database. 
3. Ensuring there are no network or firewall restrictions obstructing the connection. 
4. Please review the gateway logs for further error details. You can access these logs through the gateway installation directory or the gateway application. 

Please go through the below link for additional information:   

Troubleshoot the on-premises data gateway | Microsoft Learn 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly and give Kudos if helped you resolve your query.  

Thankyou. 

[RyanFiting]

Thanks for the information.  I have tried all of these besides one and it did not work.

When you say 'restart' the gateway, are your referring to uninstalling and reinstalling and then reconfiguring all of my reporting and workspaces to the gateway?