UmbraCare LOGO

How to Fix SSL Certificate Trust Issues When Connecting to SQL Server

Learn how to resolve SSL certificate trust issues when connecting to a SQL Server on Windows. This step-by-step tutorial covers certificate installation, connection string adjustments, and troubleshooting tips.

Published on: , by:
Piotr Bach
Piotr Bach
Wojciech Tengler
Wojciech Tengler
MS SQL .NET Core

Table of contents

Background

Connecting to an SQL Server might sometimes throw errors related to SSL certificates.

One standard error is caused by the server's certificate not being trusted by the client machine.

This article will guide you through resolving such SSL certificate trust issues.

Error Description and Stack Trace

You might encounter an error similar to the following when trying to connect to SQL Server:

Failed to detect SqlServer version.
A connection was successfully established with the server, but then an error occurred during the login process. (provider: SSL Provider, error: 0 - The certificate chain was issued by an authority that is not trusted.)
Microsoft.Data.SqlClient.SqlException (0x80131904): A connection was successfully established with the server, but then an error occurred during the login process. (provider: SSL Provider, error: 0 - The certificate chain was issued by an authority that is not trusted.)
 ---> System.ComponentModel.Win32Exception (0x80090325): The certificate chain was issued by an authority that is not trusted.
   at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at Microsoft.Data.SqlClient.TdsParserStateObject.ThrowExceptionAndWarning(Boolean callerHasConnectionLock, Boolean asyncClose)
   at Microsoft.Data.SqlClient.TdsParserStateObject.SNIWritePacket(PacketHandle packet, UInt32& sniError, Boolean canAccumulate, Boolean callerHasConnectionLock, Boolean asyncClose)
   at Microsoft.Data.SqlClient.TdsParserStateObject.WriteSni(Boolean canAccumulate)
   at Microsoft.Data.SqlClient.TdsParserStateObject.WritePacket(Byte flushMode, Boolean canAccumulate)
   at Microsoft.Data.SqlClient.TdsParser.TdsLogin(SqlLogin rec, FeatureExtension requestedFeatures, SessionData recoverySessionData, FederatedAuthenticationFeatureExtensionData fedAuthFeatureExtensionData, SqlConnectionEncryptOption encrypt)
   at Microsoft.Data.SqlClient.SqlInternalConnectionTds.Login(ServerInfo server, TimeoutTimer timeout, String newPassword, SecureString

This error indicates that the SSL certificate provided by the SQL Server is not trusted by the client machine.

Steps to Resolve the Issue

  1. Install the Root Certificate: Ensure that the root certificate used by the SQL Server is installed in the Trusted Root Certification Authorities store on the client machine.
  2. Update Connection String: Modify the connection string to either bypass certificate validation (not recommended for production environments) or to explicitly trust the server certificate.

Step 1: Install the Root Certificate

Export the Server's SSL Certificate

  1. On the SQL Server machine, open the Certificates MMC snap-in:

  • Press Win + R, type mmc, and press Enter.
  • In the MMC console, go to File > Add/Remove Snap-in.
  • Select Certificates and click Add.
  • Choose Computer account, then Next, and Finish.

  1. Navigate to Personal > Certificates.

  2. Locate the certificate used by SQL Server.

  3. Right-click on the certificate, select All Tasks > Export.

  4. Follow the wizard to export the certificate as a .cer or .crt file.

Import the Certificate on the Client Machine

  1. On the client machine, open the Certificates MMC snap-in (certmgr.msc).
  2. Navigate to Trusted Root Certification Authorities > Certificates.
  3. Right-click and select All Tasks > Import.
  4. Follow the wizard to import the exported certificate.

Step 2: Update the Connection String

Bypass Certificate Validation (Not Recommended for Production)

Add TrustServerCertificate=True to your connection string:

"umbracoDbDSN": "Server=MSSQL-7CC6CS1;Database=YOUR-DB-DEV;Trusted_Connection=True;Integrated Security=SSPI;Connection Timeout=300;TrustServerCertificate=True"

Explicitly Trust the Server Certificate

Ensure that the server's SSL certificate is installed in the Trusted Root Certification Authorities store on the client machine.

If you have followed the steps above, this should already be done.

Troubleshooting Tips

  1. Verify SQL Server Configuration: Ensure that SQL Server is configured to use the correct SSL certificate.
  2. Check Windows Update: Ensure your client machine has the latest Windows updates, which may include updates to the root certificates.
  3. Review SQL Server Logs: Check the SQL Server logs for any additional details that might provide further insights into the issue.

Final Thoughts on Resolving SQL Server SSL Certificate Issues

By following these steps, you should be able to resolve SSL certificate trust issues when connecting to SQL Server.

Ensuring that the correct root certificate is installed on the client machine and updating the connection string appropriately will help establish a secure connection.

If you continue to face issues, reviewing the server configuration and client machine updates can provide further insights.

🌐 Explore More: Interested in learning about SQL and web development insights?

Explore our blog for a wealth of information and expert advice.

EDITOR'S PICK

↑ Top ↑