Can TLS certificate be used for SQL Server encryption on the wire???

  • [アーティクル]

 During our recent interactions with a few customers, we were asked following questions about TLS certificates with SQL server:

1. Can TLS certificate be used with SQL Server 2008 for server-side encryption?

2. If Yes, How?

To start with, let’s see what is TLS and SSL.

Ø The basic thing to know about TLS(Transport Layer Security) and SSL(Secure Sockets Layer) is that both of them are certificates used for data encryption over a network. The reason you would prefer using a certificate for communication on a network is because you do not want anyone eavesdropping your communication to know the content. For example, if you encrypt your login credentials using public key of receiver, it can be decrypted only by private key of receiver and will appear as junk values to an intermediary.

Ø When it comes to making a choice between TLS and SSL, both of them provide you optimum security but you do need to know that TLS is an advanced version of SSL. People often refer to SSL 3.1 as TLS 1.0; this logically means you can ideally use a TLS certificate wherever you use a SSL certificate. Rather than discussing on the details of TLS and SSL, I wanted to discuss about using TLS certificate with SQL Server.

If you want all requests from your server to be encrypted; you can go for server-side authentication. However, if you want to opt for authentication only from certain clients; you can go for client-side authentication.

Now talking specifically about authentication with certificate for SQL Server; we found https://support.microsoft.com/kb/316898 which talks about using SSL certificate with SQL Server, but nothing about TLS.

From further research, we found the following article which talks about TLS/SSL saying it can be used ‘For SQL Access’- https://technet.microsoft.com/en-us/library/cc784450(WS.10).aspx

But still we could not find any official documentation which says for sure that we can use TLS certificate with SQL server and how to go ahead if we were to do so.

Now here comes the deciding part..What we need to really understand is that a TLS certificate or a SSL certificate is ultimately a certificate, i.e. a .pfx file or a .p12 file (These extensions are for a certificate containing the private key). Be it TLS or SSL, SQL Server will see it as a .pfx file and will try loading it if it is compatible. Hence the implementation of TLS certificates in SQL Server is same as SSL.

We can load the TLS certificate from a Certificate Authority in to the Microsoft Management Console(MMC) using the following steps from the KB article mentioned above:

How to Configure the MMC Snap-in

1. To open the Certificates snap-in, follow these steps:

a. To open the MMC console, click Start, and then click Run. In the Run dialog box type:
MMC

b. On the Console menu, click Add/Remove Snap-inunder File menu.....

c. Click Add, and then click Certificates. Click Add again.

d. You are prompted to open the snap-in for the current user account, the service account, or for the computer account. Select the Computer Account.

e. Select Local computer, and then click Finish.

f. Click Close in the Add Standalone Snap-in dialog box.

g. Click OK in the Add/Remove Snap-in dialog box. Your installed certificates are located in the Certificates folder in the Personal container.

<Assume self1 be the name of my TLS certificate here>

clip_image001

If your certificate does not show up on the Personal Folder, you need to maximize Personal, then right click on Certificates and click on Import.This will help you import the TLS certificate you have bought from the Certificate Authority in case it doesn’t show up in the first place.

Now that the TLS certificate is ready for use with SQL Server, all we need to do is import this certificate in SQL Server Configuration Manager.

To do this, open the ‘SQL Server Configuration Manager’. Click on ‘SQL Server Network Configuration’; then right click on ‘Protocols for MSSQLSERVER’ and select Properties.

Now go to the ‘Certificate’ tab in the Properties window.

In the Certificate drop-down list, you should be seeing your TLS certificate ready for use (just like you see the certificate named self1 in the below snapshot)

clip_image002

Now we can go to the Flags tab and make ‘Force Encryption’  value as ‘Yes’ which means that every communication with your SQL Server will now be encrypted.

How do you confirm that TLS certificate has been loaded successfully with SQL Server?

After you have restarted your SQL Server service, the certificate is loaded and this can be seen from the SQL Server Error Log which would say that the certificate <xyz> has been loaded for encryption.

To verify that the requests are being encrypted, you can run the following query:

select * from sys.dm_exec_connections where encrypt_option = 'TRUE'

The columns ‘client_net_address’ and ‘session_id’ can help you determine the client information so that you can know requests from which machine are encrypted.

(You should be seeing all requests as encrypted as that is what server-side authentication means! Run ‘select * from sys.dm_exec_connections’ to see all connection requests.)

What if you still don’t see the TLS certificate in SQL Server Configuration Manager?

Chances are there that you would still not  see the TLS certificate in the drop-down list for Certificate in SQL Server Configuration Manager in the first attempt.

In one of the cases we saw the Details of the certificate and found that the Subject was ‘abc-corp.abc.com’ whereas the Subject Alternative Name contained the following:

DNS Name=abc-corp.abc.com

DNS Name=www.abc-corp.abc.com

DNS Name=ABC-Server1.abc.local

DNS Name=ABC-Server2.abc.local

DNS Name=ABC-Server3.abc.local

DNS Name=ABC-SQLServer.abc.local

In our case customer had decided to use this certificate for multiple servers. The name of SQL Server being ABC-SQLServer.abc.local

Now, we were not able to see the certificate under SQL Server Configuration Manager because the following criteria was not met:

The Subject property of the certificate must indicate that the common name (CN) is the same as the host name or fully qualified domain name (FQDN) of the server computer.

So in our case we suggested to request the Certificate Authority to change the Subject name to ABC-SQLServer.abc.local (FQDN of SQL Server) instead of abc-corp.abc.com

                Once this change was done, we loaded certificate again in MMC and now we could see the certificate loaded in SQL Server Configuration Manager!

Hence before buying the TLS certificate, do ensure that your certificate meets all requirements to be compatible with SQL Server which can be found at the following link:

https://technet.microsoft.com/en-us/library/ms189067.aspx

I hope this blog helps you in case you stumble across the question we had at the start for TLS certificate.

Written By : -  Deepesh Jethwani,SE,Microsoft GTSC
Reviewed By: - Pradipta Das,TL,Microsoft GTSC 
                         Gurwinder Singh,TL,Microsoft GTSC

Comments

  • Anonymous
    May 17, 2016
    Very good article.