SQL 2008 R2 SSL certificate shows self-generated on node2. Node1 shows the correct certificate loaded in SQL log

Question

I have created an SSL certificate for SQL 2008 R2 encryption and installed on the first node.  When I restart the SQL server I see that the certificate is loaded because the Hash and certificate thumbprint matches.  I export the certificate from node1 and install it on node2, restart the SQL service and fail over the cluster from node1 to node2 with no error.  When I look into the SQL log I see that a self-generated certificate is created instead of using the SSL certificate.  Using Microsoft NetMon I see that the traffic is being encrypted.

Is this a display bug and it is really using the SSL certificate I requested?

More Information

SSL certificate is generated from our CA

The certificate chain is installed

The certificate was requested on Node1 while I was logged into the server as the domain SQL server service account

The certificate is stored in Current User -> Personal and Computer Account -> Trusted

SQL 2008 R2 SP2 x64 Enterprise Edition

Answer 1

I think it is more likely that the second node is using the self-generated certificate; not the one you want. That is the expected behavior if SQL Server can't load the user certificate. It is possible that there might be a message in the error log.

You obviously got the process correct when installing the certificate on the first node. I guess I would start over on the second node, and see if it works the second time. One common problem is that people are logged in as a different user when installing the certificate, and then the SQL Server credentials can't get to that location. But you seem to know that and did it properly on the first node.

---

Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

Answer 2

It was my understanding that the self-generated certificate could not be used when the Force Encryption is turned on.  I have it set on and when I filter to display TDS in NetMon all I see returned is TLS.  Am I wrong in assuming that the self-generated certificate is only used for login?

Also, on Node1 when I go to the registry to see what certificate thumbprint is loaded it is blank even through I know the certificate is loaded and in use. 

Answer 3

The self-generated certificate is used for encrypting data when Force Encryption is turned on, if you haven't properly loaded your own certificate.

---

Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

Answer 4

Thank you for the answers.  That really helped me out on understanding why I was seeing encrypted data.  Is it wrong to use a self-generated certificate for encryption?

Answer 5

It is not wrong to use a self-generated certificate for encryption. (Actually self-signed is a better term.) But a self-signed certificate does not provide protection against a man-in-the-middle attack, so you have additional risk if someone can insert themselves into the communication channel. Using a certificated signed by a trusted certificate authority (instead of a self-signed certificate) can increase your protection. 

---

Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

Answer 6

I finally figured out what was missing.  The template I was using from our CA would not allow the private key to be exported.  The certificate on Node1 had the private key so when I exported/imported it on Node2 it did not have the private key.  I created the new cert under a different template that allows private key export and it worked!!! Now in the SQL log I see SQL using the same cert now matter what node.

Thanks for all your help