Deploying a Certificate for Encrypted Connection SQL Server

Total: 3 Average: 5

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
  • 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.
Figure 12: Associate Certificate with SQL Server
  • 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.
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.

References

  1. Enable Encrypted Connections
  2. SQL Server Certificates and Asymmetric Keys
Kenneth Igiri
Latest posts by Kenneth Igiri (see all)

Kenneth Igiri

Kenneth Igiri is a Database Administrator with eProcess International S.A., Ecobank Group's Shared Services Centre. Kenneth has over eight years' experience with SQL Server and Oracle databases as well as related technologies. His interests include database performance, HADR, and recently, Cloud. Also, Kenneth teaches at Children's Church and writes fiction. You can connect with Kenneth via his blog or social network account.