Recently there has been a rise in number of error messages regarding certificates in Microsoft SQL Server Management Studio (SSMS). This is because of the breaking change that has been introduced with the latest version of SSMS starting from version 20.0. Prior to this version of SSMS when connecting to SQL Server it was not mandatory to enable certificate checks. What this means is normally connections to the database server are not encrypted by default using a certificate. Going forward however the default behavior is to always try and encrypt the data transfer between the client and the server using a certificate.
- Certificates from a trusted authority
- Self-Signed Certificates
Trusted Authority Certificates
A trusted authority is one of the approved providers of certificates and therefore ensure that the certificate is unique and can identify the machine and domain because a chain of authentication is established from the authority who has permission to grant certificates to the vendor who has generated the certificate and finally the machine which has installed the certificate. In this case because there is a chain of verification it is easy to establish the identity of the machine or the client to which the connection is being established. Due to this chain of trust, we can be sure of the identity of the machine we are connecting to. Think of it like the badge an officer may show to establish he is genuine when knocking on the door. However, this might not be practical where many servers are being used and security isn’t really a high priority because the data is not production data. In such cases it doesn’t make sense to pay for individual certificates for hundreds of servers in your data center. Here is where you can generate something called a self-signed certificate. It is basically a certificate generated by you and therefore doesn’t have the chain of authentication that would be required as mentioned previously. A self-signed certificate can still be used to encrypt the data however because it doesn’t have any parent level information it cannot be used to establish the identity of the connection. While this allows us to still protect data to some extent it cannot be used to authenticate that the client is who it says it is. In management studio we are given the choice between using either of these options in the first case we install the certificate and use that certificate when establishing connections to SQL Server. Upon detecting that there is a certificate SQL Server will validate the chain of authentication to ensure that this certificate was generated from a known trusted provider who will take responsibility for ensuring that the identity of the client and the server has already been established. The first thing here is to have a valid domain like mycompany.com. Then buy a certificate using this domain from a trusted authority. Then ensure that server is part of this domain and install the certificate from the trusted authority. When connecting to SQL Server the connection window will look like the one below: –
Notice that strict means there must be a valid certificate. The challenge here is that certificates expire and need to be rotated regularly so it can become cumbersome to administer. With that said Google had implemented something similar may years ago when the decided they would prioritize websites with Https over http and the internet is better because of it.
Self-Signed Certificates
If there is a need for a less expensive solution, then you can use the below settings as well. This time instead of using strict method we use Mandatory. In such cases we can use a self-signed certificate and ignore the certificate chain using the checkbox Trust server certificate as shown below. We are basically saying that trust is implicit to the certificate that is self-signed, and that SQL Server should not try to establish the chain of authentication to verify the identity because it was not generated by trusted authority in the first place.
By using this option, we are basically adding an additional layer of security to data transfer between the client and the server. In most cases if using Azure cloud services data doesn’t exit the network and would typically be transferred from database server inside the cloud to a web server inside the cloud. The use of this certificate security is mainly for connections being established outside of your network directly to your database this could mean developers connecting to an Azure cloud instance and transferring a copy of data from a select statement or even reports that are being sent to client systems where they have direct access to the database.
The last option we have is to set the dropdown to optional, in this case there is no check are validation being performed and a certificate will be used if one is provided but if none are providing the connection is still established using an unsecure channel. This does not mean however that your connection is unencrypted, say for example the client is using a VPN. In these cases, packets sent between the client and the server are still encrypted just using a different certificate in the VPN client.
Bugs
Unfortunately, when management studio was released, this feature has not been tested completely and there are a few bugs. For example, if you are trying to establish a connection using SQL Server agent to view the job steps and inside the job step let’s say you have a package that is deployed in SSIS catalogue. In such cases again you need to establish a connection to the SQL Server database via the SQL Server agent and this interface does not have the option for enabling trust server certificate checkbox and so you will get the error. However, you can still just ignore the error click through and screen will open just fine. Keep in mind that enabling encryption or trusting the certificate is now an attribute that you want to put in the connection string so that by default any connection you are established always uses trusted connection.
Installing certificates
Tip
It can be a hassle to always check the box when connecting to SSMS multiple times a day. To speed things up enter TrustServerCertificate=True within the Additional connection parameters window as shown below.
This will ensure that you can connect using the default Mandatory option without having to do the additional mouse click of trust Server certificate.