SQL Server connections offline

[powerapps_tec]

We are facing an unusual problem with the Power BI gateway/service. Dataset refreshes that use existing SQL connections work fine, but their status appears as offline/unreachable in the interface. And we are unable to create new connections. This problem started suddenly without any change made on our servers.

 

When creating new connections, we get this error (please note: I have replaced our server name with a placeholder text).

1) Unable to create connection for the following reason: We could not access the data source. Please make sure you have permission to access the data source and that your credentials are correct.

<<server-name>>: A connection was successfully established with the server, but then an error occurred during the login process. (provider: SSL Provider, error: 0 - The target principal name is incorrect.) 

Nothing has changed on our side. The DNS name of the server is working with dataset refreshes and all our apps that use it. Our domain controller is also working correctly. We have updated our gateway software to the latest version 3000.254.6.

We would really appreciate some guidance and support with this issue! 

Many thanks.

[powerapps_tec · Accepted solution]

We solved the issue with the solution described in this post. 
https://powerbi.microsoft.com/en-us/blog/on-premises-data-gateway-october-2024-release/
By trusting all our database servers in the GatewayCore configuration file, we are able to create new connections, and existing connections appear online.
Thanks to @Anonymous for the clear instructions in the post.

[powerapps_tec]

Thank you for your replies to my message. 1) No, we do not have any logon triggers and 2) Yes, the certificate name matches the server name.
As I mentioned, the dataset refreshes are working fine. It seems like the gateway software uses different mechanisms to authenticate for a dataset refresh versus when creating new connections or retrieving the status of a connection. If we could find out what that difference is, it would really help narrow in on the problem.

Many thanks.

[Anonymous]

Hi @powerapps_tec,

Thank you for reaching out to Microsoft Fabric Community Forum.

The error "The target principal name is incorrect." indicates an SSL certificate problem with SQL Server.

Please verify if the SQL Server certificate matches the server name used in Power BI Gateway.
Please make sure the latest SQL drivers are installed.
Restart SQL Server and Power BI Gateway services after applying changes.

If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it!

Regards,

Vinay Pabbu

[GilbertQ]

Hi @powerapps_tec 

Can you confirm that there are no logon triggers when the user is logging into SQL Server? I have seen that issue happening in the past. If there is a case where there are logon triggers with regards to your SQL Server, you will have to use an ODBC SQL Server connection to overcome these issues.