The SQL Dude!

On-Disk & In-Memory Pages that teach you the SQL way of life!

Posts Tagged ‘TDSSNIClient’

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.

  1. The certificate must be in either the local computer certificate store or the current user certificate store.
  2. The current system time must be after the Valid from property of the certificate and before the Valid to property of the certificate.
  3. 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).
  4. The certificate must be created by using the KeySpec option of AT_KEYEXCHANGE
  5. 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)

image

Certificate is created for Server Authentication (Requirement #3)

image

Key Specification Property (Requirement #4)

image

Subject Name (Requirement #5)

image

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.

image

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.

image

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"

image

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.

image

5. Enable Force Encryption to “Yes” using SQL Server Configuration Manager.

image

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…

Advertisement

Posted in Security | Tagged: , , , , , , , , , , | 15 Comments »

SQL Server service does not start after enabling SSL encryption

Posted by Sudarshan Narasimhan on August 3, 2011


I recently had a customer who came up to me with a SQL Service start-up issue. This issue started after they had enabled SSL encryption using a personal certificate. Now, before I begin let me confirm a couple of things :-

  1. The correct SSL certificate has been chosen in SQL Configuration Manager.
  2. The ForceEncryption flag is set to Yes.

There are some forum posts on this issue and I don’t entirely agree with the solutions proposed there, because they did not actually solve the real problem but worked around it without understanding how/why the problem occurred, and how to get a complete solution.

http://social.msdn.microsoft.com/Forums/en-US/sqlexpress/thread/b308d9c8-6aba-4b3b-9b32-f919816d1be2/

http://social.msdn.microsoft.com/Forums/en-US/sqldatabaseengine/thread/63c15cb5-0d12-4ea8-bf84-e3ea8a42a866/

I am going to be bold and say this; please do not follow the above posts since they are workarounds. Most people in enterprise environments cannot run SQL Service under the Local System account, which is what the above posts are asking you do to, yikes!!!

As always, the first place to check when a SQL Service does not start is the SQL ERRORLOG (yes, ERRORLOG and NOT the EVENTLOGS – I said 1st place).

For a complete step-by-step guide to approach SQL Service start-up issues, you can read the blog post I wrote on MSDN SQL Server FAQ –> HERE

Here is what I found in the ERRORLOG,

2011-06-08 12:35:30.56 Server Unable to load user-specified certificate [Cert Hash(sha1) “XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX“]. The server will not accept a connection. You should verify that the certificate is correctly installed. See “Configuring Certificate for Use by SSL” in Books Online.

2011-06-08 12:35:30.59 Server Error: 17182, Severity: 16, State: 1.
2010-06-08 12:35:30.59 Server TDSSNIClient initialization failed with error 0x80092004, status code 0x80. Reason: Unable to initialize SSL support. Cannot find object or property.

2011-06-08 12:35:30.62 Server Error: 17182, Severity: 16, State: 1.
2011-06-08 12:35:30.62 Server TDSSNIClient initialization failed with error 0x80092004, status code 0x1. Reason: Initialization failed with an infrastructure error. Check for previous errors. Cannot find object or property.

So, it appears the certificate chosen for SSL encryption has some issues. Here are the things I checked next:-
1. The certificate itself is valid and has not expired (using certmgr.msc).
2. The certificate hash matched that of the certificate associated with the instance but it wasn’t loading.

The certificate appears to be fine. Well, what next?

The way SQL Server loads the certificate happens through the credentials of the SQL Server service account defined for this instance. So, I started to check what the SQLSvc account was and whether it had permissions to access the certificate. At this point I cam across this forum post for a similar issue

http://social.msdn.microsoft.com/Forums/en/sqlexpress/thread/db68b3bf-33a8-42d6-8dac-f7229a2f9cde

The solution listed there was to ensure that the SQL service account had a valid user profile on the server machine. This profile can sometimes be missing if the service account was changed to a new account, which doesn’t yet have a user profile created. You can verify the user profile on any machine by going to
My Computer –> Properties –> Advanced –> Settings (under User Profiles section).

image

Solution #1 (as applicable)

Identify the SQL service account and log on to the server machine that is running SQL Server, with the service account. During logon, Windows will create a new user profile for the service account.

Unfortunately, this wasn’t the scenario in my case. Another thing I have to caution people is regarding service account change. When changing the SQL Svc account ALWAYS ALWAYS use the SQL Server Configuration Manager, and NEVER do it through services.msc. Here are some reasons why:-

1. Permissions & ACL’s of SQL Server registry keys are done only by SSCM
2. SQL Server User Groups are updated with the new service account only by SSCM.
3. WMI namespace permissions are only set through SSCM.

So, if you changed the service account through services.msc, it is possible that the new service account does not have permissions to access the certificate. The certificate hash is stored in the following registry key. If the new service account cannot access it, then you “can” get the error above, since SQL cannot load the certificate due to insufficient privileges.

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.SQL2008\MSSQLServer\SuperSocketNetLib\Certificate

I started to check if this was happening in my case and found that the SQL Service account was not even present in the local group SQLServerMSSQLUser$[Computer_Name]$[Instance_Name]. This confirms that the service account change wasn’t done through SQL Configuration Manager.

This means the SQL Service account does not have permissions to open the private key for the certificate. i.e. SQL Server failed to load this specific certificate due to insufficient permissions. Once we give the service account permission to read the private key, the SQL Server should start up without a problem.

Here is what I did to resolve it,

Solution #2

1. Identify the SQL service account (could be a domain account or any of the built-in accounts like LocalSystem/LocalService/NetworkService).

2. If the service account was set properly using SQL Configuration Manager, then it should have been added to the SQL Security Groups. Look for this on the local machine groups. e.g. SQLServerMSSQLUser$[Computer_Name]$[Instance_Name]

3. Open the Microsoft Management Console (MMC) by click Start -> Run, entering mmc and pressing Enter.

4. Add the Certificates snap-in by clicking File -> Add/Remove Snap-in… and double clicking the Certificates item.

Note:
Select computer account and Local computer in the two pages on the wizard that appears.
Click OK.

5. Expand Certificates (Local Computer) -> Personal -> Certificates and find the SSL certificate you imported or created.

6. Right click on the imported certificate (the one you selected in the SQL Server Configuration Manager) and click All Tasks -> Manage Private Keys.

Note:
You will only see this option for SSL certificates imported from a certifying authority. E.g. you will not see this for EFS, RDP certificates

7. Click the Add button under the Group or user names list box.

8. Enter the SQL service security group that you identified in step #2 and click OK.

You can also directly add the service account itself here, but for any future changes you need to repeat these steps to add that individual account. Instead its better to add the security group, since the service account will always be present here if account change was done using SSCM.

Now, when I started the SQL service you can double-check that the certificate is being loaded and used by looking for this entry in the errorlog. In my case, it started fine and SSL encryption was working.

Open-mouthed smile

2011-06-08 12:48:47.88 Server The certificate [Cert Hash(sha1) “XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX“] was successfully loaded for encryption.


Problem Solved!

Now, to check if the connection is actually encrypted you can use Netmon or Wireshark to check the network packet. Refer to the screenshot posted in this blog on how to do that. An easier way for DBA’s to verify this is to use the DMV sys.dm_exec_connections, look at the encrypt_option column. This DMV also tells you if the connection is using Kerberos/NTLM/SQL Authentication -> auth_scheme column has this value.

Hope this saves you some time.

Posted in Startup | Tagged: , , , , , , , | 19 Comments »