Deploying a Certificate for Encrypted Connection SQL Server
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.
Figure 1: Add or Remove Snap-in
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).
Figure 2: User Computer Account
In this article, we are dealing with managing certificates on the local computer where our SQL Server instance is installed (Figure 3).
Figure 3: Manage Local Computer
Figure 4: Selected Snap-ins – Certificate
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:
Figure 5: Request New Certificate
Enrolling a 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:
Figure 6: Certificate Enrollment Wizard
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.
Figure 7: Certificate Enrollment Policy
Figure 8: Certificate Type
Figure 9: Certificate Enrolled
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.
Figure 10: The Certificate Details
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):
Figure 11: SQL Server Protocols
Figure 12: Associate Certificate with SQL Server
Figure 13: Force Encryption
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).
Figure 14: Using Network Monitor
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.