Unable to connect to SQL server using SQL Server database connector in power bi desktop with the error message shown error message section below.

Issue:

Unable to connect to SQL server: The server name provided doesn’t match the server name on the SQL Server SSL certificate. Please contact your administrator or see the link below for more information. Alternatively, you could change your connection encryption setting. Would you like to continue using an unencrypted connection?

This issue happens in the following scenario:

1. When any application tries to connect to SQL server (in our case it is power bi desktop) where a certificate is needed for the same.
2. SQL server name that is provided in the connector UI does not match with the SQL server name that is mentioned in the certificate that is being used.

Note: By default, our SQL Server Connector will work only with the following two options. For more general information about connector is available at https://docs.microsoft.com/en-us/power-query/connectors/sqlserver Opens in new window or tab Please review for your own understanding.

1. Encrypt connection to disabled and Trust Server Certificate as false. What does this mean? See below screen shot!

2. Encrypt connection to enabled and Trust Server Certificate as false. What does this mean? See below screenshot

We do not expose the option “Trust Server Certificate” in SQL Server Connector like how it is exposed in SQL Server management studio. But it is always set to false in SQL Server Connector in power bi desktop.

When it is # 1, it means we are not using any certificate to connect to SQL server.

When it is #2, it means we are using certificate to connect to SQL server.

Troubleshooting Steps

1. Please check with user what is the server name used in SQL Server database connector in power bi desktop
2. Please check with user what is the sql server name mentioned in the certificate which is used to connect to SQL sever. If the end user does not know how to check then they need to consult with their SQL server admin
3. Both # 1 and # 2 should match here. If they do not match that is when we get this error. Please see the steps mentioned in Resolution section and ignore the step 4 below otherwise continue.
4. If both # 1 and # 2 match and still fail with the error message mentioned above in error message section. Then please file an IcM with details of # 1, # 2, error message and desktop logs.

Unable to connect to SQL Server

This above Article is applicable only for the error message that is mentioned in the error message section below and it fails in Power BI desktop. If the error is seen is service only, then please follow below steps:

Issue/Problem/Symptoms

User will get “Invalid connection credentials” error while refreshing the report built with SQL Server as source.

Error Message

Invalid connection credentials. Underlying error code: -2147467259 Underlying error Message: The server’s name provided doesn’t match the server’s name on the SQL Server SSL certificate.

Resolution

Issue will be fixed by specifying FQDN for SQL Server data source in PBIX and under on-premises data gateway settings. For more information, please refer to this Power BI forum: Solved: Certifiticate problem w enterprise gateway import … – Microsoft Fabric Community.

SQL Bi Links:

1. Port Number For Analysis Services – Find Ports For SSAS? (kingfishertechtips.in)
2. RS.exe Utility: Migrate RDL Reports To Another SSRS/PBIRS (kingfishertechtips.in)
3. Direct Query Mode ON In Tabular Model Issue – Visual Studio (kingfishertechtips.in)
4. SMTP Server In SSRS/PBIRS |Subscribe To Email Problem/Issue (kingfishertechtips.in)
5. SQL Server 2022 Download, Install, Connect Using SSMS Tool (kingfishertechtips.in)
6. Creating Tabular Project & Deploy Model To Analysis Service (kingfishertechtips.in)
7. Deploy Tabular Project To Azure Analysis Service – SSDT Tool (kingfishertechtips.in)
8. SSRS/PBIRS – Install And Configure The Report Server On-Prem (kingfishertechtips.in)
9. Could Not Load Folder Contents In Portal URLs – SSRSPBIRS (kingfishertechtips.in)
10. SSRS/PBIRS – Install And Configure The Report Server On-Prem (kingfishertechtips.in)
11. SQL Server 2022 Download, Install, Connect Using SSMS Tool (kingfishertechtips.in)
12. Capture Fiddler Trace for Debugging the Network Traffic (kingfishertechtips.in)
13. On-Premises Gateway: Configure/Map SQL DB To Power Bi (kingfishertechtips.in)
14. Fixed Column/Row Header Make Visible In Any Report (kingfishertechtips.in)
15. Self-Signed Certificate: Configure SSRS/PBIRS With SSL (kingfishertechtips.in)
16. Create Local User Account/Local Admin User In Windows 10/11 (kingfishertechtips.in)
17. Login With AAD/Domain/Microsoft Account To Azure VM’s/RDP (kingfishertechtips.in)