Resolving SQL Connection Errors Due to SSL Certificate Issues

SSL certificates are very important for authenticating websites and ensuring connections are encrypted. However, there are some scenarios when they can create headaches. Rather than throw the baby out with the bathwater and abandon the use of SSL, you should know how to use them properly.

One issue that you can run into will occur when connecting with a remote computer. You might try to connect to an SQL Server from a remote computer with the same credentials that you used to connect locally. Unfortunately, you might receive an error like this.

“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.) (.Net SqlClient Data Provider).”

There is no way to connect to the server when this error message occurs. The message discusses a certificate and references the SSL provider of the SQL Server. However, you have not installed a certificate on the server, so you won’t be able to identify the underlying issue.

You need to know how to resolve these issues. You also need to make sure that they work with different content settings, such as PowerPoint templates created with a tool like SlideUplift. I mentioned this in my previous article Relationship Between SSL Certificate Key Length and SQL Performance, which established the importance of optimizing SSL when testing PowerPoint presentations and other content settings.

The server will install a self-signed certificate for it, unless you specify that you would like another one to be setup. This certificate is created by the SQL Server. It won't be supported by a real public key infrastructure. This means that the certificate can encrypt communications and validate their integrity. Unfortunately, it does not verify that you are connecting to the right server, since the only 'authority' that assures us that the server is the correct one is the server itself.

That is precisely what this error message is about. What it tells us is that, by default, as the identity of the server cannot be verified, it does not allow the connection.

Something similar happens with many other services that use an SSL certificate by default. For example, when connecting for the first time to a remote computer using a Terminal Server (also called Remote Desktop), you will be asked whether or not you want to trust the certificate and therefore connect to the server.

This makes sense since there are three main purposes of a digital certificate:

• Privacy of the information, which includes encrypting communications in transit.
• The integrity of the communication, which combines them with digital summaries.
• Trust, which is ensured by verifying that communications come from whom they say they come from and that they are not communicating with their intended servers.

The first two purposes can’t be supported if trust is not adequately established.

How Do You Solve This Problem?

If the error is issued by the SQL Server Management Studio when you try to connect from the login screen, then you have to go to the advanced options of that dialog. After reaching this section, you will need to go to the tab named 'Connection Properties' and make sure that the 'Encrypt connection' option is checked.

You can always uncheck it and prevent the error from occurring, but then the traffic between your computer and the remote SQL Server will no longer be secured, which entirely defeats the purpose of SSL encryptions.

The next step is to go to the 'Additional Connection Parameters' tab. You must then write a switch to make the connection trust the remote SQL Server certificate. This additional parameter is:

TrustServerCertificate=True.

Just type it in the text box in that window. After clicking on 'Connect,' you will notice that the error disappears and will be able to work normally.

If the error is being given by an application, we only have to modify the connection string and put the same parameter at the end.

If you do this, then you have to be sure that the server we are connecting to is the one we are interested in and that no security problem could be falsifying the DNS so that you connect to another site.