Introduction
Last year we got a requirement to ensure encrypted connections to our SQL Server instances. Before, we did not think it was necessary – all our instances were accessed by application services internally. Still, secure connections protect the instance and clients from attacks as man-in-the middles, so we did it.
Connection encryption differs from Transparent Data Encryption, but you need a certificate in both cases. In this article, we describe the procedure of setting up an encrypted connection for SQL Server instances.
Setting Up the Certificate Snap-in in MMC
A certificate is a digitally signed document containing public and private keys that encrypt connections. The public and private keys are a “Key Pair” – the public key encrypts the data, and it is only the private key that can decrypt them.
Certificates are issued by a Certificate Authority, an entity that both the server and client trust. In our case, we generated a certificate from the server hosting SQL Server.
We start this process by launching Microsoft Management Console (mmc.exe).
When MMC is launched, we navigate to File > Add and Remove Snap-ins … (Figure 1). Here, we add the Certificate Manager snap-in to our console to manage certificates on the server. Note that there are other ways to get to this point.
We want to manage certificates from our computer account in such a way that other administrators would not have any issues with permissions when they also need to manage certificates (Figure 2).
In this article, we are dealing with managing certificates on the local computer where our SQL Server instance is installed (Figure 3).
Once we complete the process of the Certificate snap-in creation, we can put it to use.
Start by selecting All Tasks > Request New Certificate:
Enrolling a MMC Certificate
The action from Figure 5 launches a wizard – we will quickly run it through. The details are more relevant for the Windows Administrator, but the crucial thing is to get a valid certificate that SQL Server can use.
Verify the necessary conditions:
Select an enrollment policy and the type of certificate you want. In our case, we selected the policy as configured by our Domain Administrator for purposes like this. You might speak with your Domain Administrator to define the best option in your environment.
Certificate enrollment is the process of requesting a digital certificate from a Certificate Authority. In some environments, the CA is part of the Public Key Infrastructure.
Configuring SQL Server
Now when we have the certificate, we go over to SQL Server and configure it to use that certificate.
- Open the SQL Server Configuration Manager and navigate SQL Server Network Configuration > Protocols for MS SQL Server.
- Right-click on this item and select Properties from the drop-down menu (Figure 11):
- In the Properties window, select the Certificate tab. If you have done the certificate enrollment correctly, you should see it listed in the drop-down menu labeled Certificate (Figure 12). By doing this, we associate this certificate with the SQL Server instance. Note that we can also see the certificate details in SQL Server Configuration Manager.
- Once we are done applying the valid certificate, we go over the Flags tab and set the Force Encryption flag to YES. It ensures that all connections to SQL Server are encrypted.
The cryptographic protocol that SQL Server uses for connections’ encrypting will depend on the operating system configuration. Then, you should restart the SQL Server instance. It loads this new certificate after it.
We can see the data in Windows Event Viewer – the SQL Server error log. We can also verify the encryption of connections with such tools as Network Monitor from Sys Internals (Figure 14).
Conclusion
An encrypted connection is typically required in organizations concerned about security. In this article, we have shared our experience on how to configure encrypted connections on SQL Server.
Our approach involved enrolling a certificate, applying that certificate to an SQL Server instance, and enabling Forced Encryption. It is essential to note is that when you set Force Encryption to YES in SQL Server, all clients connecting to the instance must use the same cryptographic protocol.
