To SSL or not to SSL, that is the question….
Posted by Sudarshan Narasimhan on October 22, 2011
A lot of database administrators out there would have heard of SSL (Secure Sockets Layer) and the security it offers, and thus want to configure their SQL Servers to use SSL for encryption of data on the wire. Bear in mind that SSL Encryption is very different from the built-in database encryption features like TDE. SSL is used for encrypting data transmitted across the network (on the wire) between the SQL Server and a client/application. Once configured all traffic between SQL Server and a client application is encrypted which has its advantages (secure transmission) and its disadvantages (performance impact due to the encryption and extra round-trip at connection time). SSL is a powerful feature that is a given standard of most systems and will definitely feature in a list of To-Have items if any security audit or standards are enforced in your company.
In this post I am not going to talk about what the SSL/IPSec standards, but going to focus on how to get a SSL configured for your SQL Server instance. Anyone who has setup SSL for SQL Server before might have encountered a situation where the certificate from a trusted authority does not get detected in SQL Configuration Manager. I am going to elaborate how to tackle that issue as well, so read on and stay safe!
What do you need for SSL?
Certificates! You will need to purchase/provision a certificate from a certificate authority such as VeriSign or GoDaddy.com
Pre-Requisite Reading Material
Encrypting Connections to SQL Server
http://msdn.microsoft.com/en-us/library/ms189067.aspx
How to enable SSL encryption for an instance of SQL Server by using Microsoft Management Console
http://support.microsoft.com/default.aspx?scid=kb;en-us;316898
How to Enable Channel Encryption
http://blogs.msdn.com/b/sql_protocols/archive/2005/10/04/476705.aspx
SQL Server fails to start with error 17182 "TDSSNIClient initialization failed with error 0xd, status code 0x38" when server is configured to use SSL
http://support.microsoft.com/default.aspx?scid=kb;en-US;2023869
I would highly recommend you read the above mentioned articles from MSDN and only then proceed with the steps listed below. Before going further I want to highlight a few important points from the above article regarding Certificate requirements. For SQL Server to load a SSL certificate, these are the requirements.
- The certificate must be in either the local computer certificate store or the current user certificate store.
- The current system time must be after the Valid from property of the certificate and before the Valid to property of the certificate.
- The certificate must be meant for server authentication. This requires the Enhanced Key Usage property of the certificate to specify Server Authentication (1.3.6.1.5.5.7.3.1).
- The certificate must be created by using the KeySpec option of AT_KEYEXCHANGE
- 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. If SQL Server is running on a failover cluster, the common name must match the host name or FQDN of the virtual server and the certificates must be provisioned on all nodes in the failover cluster.
Now, these are the mandatory requirements. But how do you verify these properties are satisfied before trying to load this certificate in SQL Server. Here is where the tool CERTUTIL.exe comes in very handy. (Certutil.exe comes with Windows)
I have a certificate named SUDA24322118 which I am going to check to see if the above 5 requirements are satisfied. From an elevated command prompt, run the following: certutil -v -store "my" "SUDA24322118" >certprop.txt
Open certprop.txt in Notepad and check the following values:
Validity of the certificate (Requirement #2)
Certificate is created for Server Authentication (Requirement #3)
Key Specification Property (Requirement #4)
Subject Name (Requirement #5)
The last one is the tricky one. In my case, my machine name is SUDA24322118 and hence the subject name must be the same. Once all of the above are verified, the certificate is good to be used with SQL Server.
Wild-Card Certificates
SQL Server 2008 R2 and onwards support wildcard certificates. E.g., *.ABCXYZ.com could be the CN of a certificate. This means that the SQL Server Engine (Server side components) will allow you to load a wildcard certificate for SSL. There are some caveats you need to know regarding client-side issues, so please read the accepted wildcard examples HERE.
Unable to see the certificate in the drop-down list box in SQL Server Configuration Manager
If you have religiously followed all the steps till now but still unable to see/pick the certificate to load in SQL Server, then you can follow the workaround given below to use the certificate. To reproduce this issue I created 2 certificates on the same lab machine SUDA24322118 which is in a WORKGROUP.
1. Create a test certificate: I used MAKECERT.exe which is available with Windows SDK. I have Win7 SDK installed and makecert.exe is available in "C:\Program Files\Microsoft SDKs\Windows\v7.1\Bin\".
makecert -r -pe -n "CN= SUDA24322118" -b 01/01/2000 -e 01/01/2036 -eku 1.3.6.1.5.5.7.3.1 -ss my -sr localMachine -sky exchange -sp "Microsoft RSA SChannel Cryptographic Provider" -sy 12
makecert -r -pe -n "CN= SUDA24322118.bplogix.com" -b 01/01/2000 -e 01/01/2036 -eku 1.3.6.1.5.5.7.3.1 -ss my -sr localMachine -sky exchange -sp "Microsoft RSA SChannel Cryptographic Provider" -sy 12
The subject name in the 1st certificate has just the hostname of the computer whereas the second one has the FQDN. Both of them are stored in the local user store called ”my”.
2. If you open you SSCM, you see that only the 1st certificate is listed and the second one is missing.
Surprised? Don’t be. Both of them have the same properties and satisfied all the 5 certificate requirements. You can see the certificate if you open up mmc and add the snap-in for Certificates. When asked to chose the account, select “Computer Account” for the Local computer. Both of the above certificates will be present under Personal –> Certificates.
How do I get SQL Server to use the certificate with FQDN? Fortunately, there is a simple workaround for this.
3. Locate the Certificate Hash Value: We make use of certutil.exe again to get the cert hash value
certutil -store "my" "SUDA24322118"
certutil -store "my" "SUDA24322118.bplogix.com"
You can pipe the results of the above command to a text file and copy the hash value highlighted above. Remove all the blank spaces, trailing and leading spaces. For the sample above, the final hash value would be: 278e30a6ba1748bbabd360b9b8ad1d78e9104d87
4. Add the hash to the SQL Server instance Registry: Open up the registry (regedit) and navigate to the SQL Server instance hive. For e.g. a default instance of SQL Server 2008 R2 would be HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQLServer\SuperSocketNetLib\
In the above location there would be a entry called “Certificate” which would be blank by default. Paste the hash value noted in step #3 here. This associates the certificate with this hash in the local store with this instance of SQL Server. Next time, when SQL Server starts it will load this certificate.
5. Enable Force Encryption to “Yes” using SQL Server Configuration Manager.
Note: DO NOT navigate to the Certificate tab and try to make any changes there. Just ignore the certificate tab altogether since we have directly modified the registry (which is what the tool does anyway).
6. Restart the SQL Server service. Open the latest ERRORLOG and you can see that the certificate with FQDN has been loaded successfully.
2011-10-11 22:34:10.80 Server The certificate [Cert Hash(sha1) "278E30A6BA1748BBABD360B9B8AD1D78E9104D87"] was successfully loaded for encryption.
7. Make a test connection from Management Studio (SSMS) and verify using the value of column encrypt_option in the DMV sys.dm_exec_connections is TRUE, meaning the connection is being encrypted. For the real techies out there you can use Netmon or Wireshark to check if the network packet is actually being encrypted.
That’s all folks. There is more to client-side encryption using SSL which you can read about in the above mentioned MSDN articles. Stay secure & safe. I’ll be back with more SQL Server stuff…
The SQL Dude! 
Setting up SSL encryption for SQL Server using certificates – Issues, tips & tricks « The SQL Dude! said
[…] by Sudarshan Narasimhan on April 21, 2012 I had posted quite a lengthy post on setting up SQL Server for SSL encryption back in October. This post has frequently ranked high […]
SWISSSQL said
Great! Very helpful! Sad but true: we all still have to tweak around with MS-servers in order to do things that should just work like expected (manipulating the registry shouldn’t be a way to install certificates)… THANK YOU VERY MUCH!
Sudarshan Narasimhan said
You’re welcome. Sadly i don’t think the SQL Configuration Manager (SSCM) is going to get better any time soon. At least we have a workaround that gets the job done. I guess we’ll have to live with that.
Hitesh said
Hi
I need to know the impact of using SSL , would it screw up my data in case the ceritfcate got corrupted.. I mean what is the max worst case I could face if I decide to use SSL .
Thanks
Sudarshan Narasimhan said
Hi Hitesh,
Your Data remains safe in the database. SSL provides communication security over the Internet/Intranet. Basically, SSL provides secure transmission of data over the wire. If the SSL certificate got expired/corrupt, then the connection handshake would fail when trying to make an initial connection to the database. Your Data is safe, don’t worry.
-Sudarshan
Richard said
I tried the registry workaround on sql2014 but it’s not showing as encrypted in SSMS when I click connection info.
I’m trying to use a cert with the FQDN that’s auto issued by my internal MS CA, it has server and client uses enabled and all but it doesn’t show up in the dropdown
I’m running SQL under a domain service account (domain\SQLserv) and I’ve copied the cert with the private key to the user store of that user and still nothing.
any info would be great.
NeedZ said
Sudarshan,
Great article man, my question is that we are running SQL servers in active directory environment and servers doesnt have public name e.g server1.cnn.com instead their names are server.test.local , what to do here…
NeedZ said
and we need public certs for encryption, not self signed, do we need to rename our servers ? or is their any workaround to this as well 🙂
Sudarshan Narasimhan said
Thanks. Even if it’s a local server like you said, the Subject property of the certificate must indicate that the common name (CN) is the host name. Instead of FQDN you can use just the machine/hostname in the certificate.
Kevin said
Hello Sudarsharn,
Need help. Trying to get this work, with a public cert… say the sql server computer name is bob01, I can get it to work with a selfsign cert, but when I try to get a public cert it wont allow me to just use bob01. So I used bob01.james.com for example, and tried your registry fix, but when I spin up the sql server service, it gives me a
“Windows could not start the SQL Server (MSSQLSERVER) on local Computer. For more information, review the System Event Log. If this is a non-Microsoft servce, contact the service vendor, and refer to service-specific error code 13.”
What should I do?
Sudarshan Narasimhan said
Hi Kevin,
When you put in the certificate hash value into the registry, did you paste that in? If you did, can you paste it into notepad, ensure there are not special characters or white spaces and then copy-paste from notepad into the registry. Try re-starting SQL services. If it still fails, can you share the contents of your errorlog file (remove server names, IP etc.. before sharing plz).
-Sudarshan
dunkman00@hotmail.com said
great article, but where would i store the inf file? if i want to reference the inf file like this, “certreq.exe -new c:\temp\certreq.inf new.csr”, i get a system cannot find the file specified error.
Sudarshan Narasimhan said
You must create the template INF file using notepad, save it locally on the machine where you are running certreq.exe and use that to create the certificate request. Refer to the following blog for an example https://blogs.technet.microsoft.com/pki/2009/08/05/how-to-create-a-web-server-ssl-certificate-manually/
pjunklive said
Awesome detailed write up! We are running many SQL 2008-2014 servers and testing install public certs with a wildcard in the SAN. In SSCM I have the “Protocols for MSSQLSERVER > Force Encryption = Yes”, do I need to set the “SQL Native Client 11.0 Configuration > Force Protocol Encryption = Yes” if I want to enforce encrypt network data?
Also, for some reason I can copy the original cert to another machine, put the hash in the SQL registry, force encryption in SSCM, restart SQL Service and Wireshark shows that the cert is used. Here is the catch, the server name is not in the CN or SAN of the Cert. This SQL Server is called y.mydomain.com not *.sql.mydomain.com as set in the SAN of the cert. Do you have any explanation on how that would be possible?
Thanks and keep up great work SQL Dude!
Sudarshan Narasimhan said
Thanks! As you’ve already provisioned Server Side cert successfully, you don’t have to make any changes to SQL Native Client 11.0 Configuration. This a client config setting and makes no sense to change this on the server itself. Any change here only applies to SQL client apps like SSMS, SQLCMD running on the server itself, which is a local connection on the server anyways.
Now, for client application connections that are running remotely like web apps or local client apps on user’s PC, it depends on the type of application, but you do need to configure the client to trust the server certificate or root certifying authority of your certificate. You can either choose to trust the server certificate (Trust Server Certificate = “Yes”) which skips certificate validation by the client or install the Trusted Root Certifying Authority’s certificate on the clients. You can do this by following the steps “Enable encryption for a specific client” given in https://support.microsoft.com/en-us/kb/316898.