?
Solved

SSL cert not showing up in SQL Server 2008 Configuration Manager

Posted on 2012-09-10
12
Medium Priority
?
8,452 Views
Last Modified: 2012-10-01
I have recently installed an ssl certificate on SQL Server in our office with the intent of encrypting connections between clients and the server.  I was able to import the cert/key pair just fine into Windows (under the Local Computer certificate store, using the standard Certificates MMC).  However, the cert does not show up in the SQL Server Configuration Manager when opening the 'Properties' -> 'Certificate' tab under 'Protocols for MSSQLSERVER'.

I have been do some searching around the web, and everything I've found suggests this is due to the cert being invalid in some way.  However, I have verified just about everything I can about the cert:

It is issued to the proper FQDN of the server.
I have the private key installed.
It has the Server Authentication role.
All CAs in the cert chain are trusted.
The Certificates MMC shows it as being valid.

What is the magic requirement for SQL Server 2008 to accept this cert?  As a test, I was able to configure other services (such as IIS) to use the cert successfully, so I'm fairly certain the cert is good.
0
Comment
Question by:ZMEadmin
  • 7
  • 3
  • 2
12 Comments
 
LVL 30

Expert Comment

by:Rich Weissler
ID: 38387124
The certificate needs to be in the certificate store for the same account used to run SQL Server.  Having the certificate in Local Computer would only be appropriate if you're running the SQL Service as LocalSystem or another built in local.  Assuming you're using a domain account... export the cert with the private key from the Local Computer, and import it into the store for that account.
1
 
LVL 81

Expert Comment

by:arnold
ID: 38387288
Along the path of razmus' comment, the certificate might need to be imported into the service store.
Mmc, add certificates console, then import the certificate (make sure the certificate has both private and public keys) how was the certificate signing request generated?
Make sure that the functionality on the certificate matches what you need.
0
 

Author Comment

by:ZMEadmin
ID: 38388524
Thanks for the information.  On this particular system, SQL is indeed running as LocalSystem, which is why I imported the cert into the Local Computer store.  I have also verified that the cert has the role "Server Authentication", which I thought was the only role needed for this use in SQL Server.

As far as how the CSR was generated, my company has an internal CA with a custom web interface.  We enter what information we want for the cert (common name, roles, etc), and an automated system does everything from there (generates the CSR, cert, private key, etc), and gives us the files.  I took the cert and private key, used the openssl command on a linux machine to merge the two files into a pkcs12 formatted pfx file, and imported the pair into the Windows server.  I've done this hundreds of times on other Windows servers for other apps (Exchange, IIS, etc) and I've never had a problem.  This is the first time I've tried it with SQL Server, though.

The cert shows up as valid in the Certificates MMC, and all details look correct.  Our internal CA cert is trusted on all of our Windows servers, so the certificate chain is fully trusted.

Do I need to run Sql Server Configuration Manager in a special way in order to see the cert?  I've tried running it as "Administrator" and as my own account (which has local admin privs).
After importing the cert, is there anything I need to do before it will typically show up in Configuration Manager?  (Such as a service restart, etc?)
0
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
LVL 30

Expert Comment

by:Rich Weissler
ID: 38388589
Sounds like you have everything covered.  That's the good news and the bad news, because it means it's something not obvious.  To answer the question, 'what's required?' from technet, the only thing I don't see is:
"The certificate must be created by using the KeySpec option of AT_KEYEXCHANGE. Usually, the certificate's key usage property (KEY_USAGE) will also include key encipherment (CERT_KEY_ENCIPHERMENT_KEY_USAGE)."
Look at the details of the cert and make certain in 'Key Usage', that 'Key Encipherment' appears as one of the values.  I can't imagine it wouldn't be there.
After that...  I'd double check the stuff you know to be true:
Make certain there isn't a subtle error in the FQDN.  Look again to confirm that the private key is available.  Make certain the date on the certificate is currently valid...
0
 

Author Comment

by:ZMEadmin
ID: 38389336
I found the problem.  It's a name mismatch, but not an obvious one.  As is pretty common with a lot of large, geographically diverse corporate networks, we name our servers with a hostname format like so:

machinename.datacenter.company.com (eg. db01.chi3.company.com for a db server in a Chicago datacenter, etc)

However, the Windows server is in an AD domain with a name such as 'production.company.com'.  This would make the *Windows* hostname:

machinename.production.company.com

So dns and everything in the company (including all of the clients talking to the server) are using the datacenter naming convention.  However, SQL Server is using the Windows domain naming convention.  Thus, SQL Server is expecting the cert common name to match the Windows domain hostname..

So that brings me to my next question:  How can we convince SQL Server to use the cert I have created?  We need a cert using the datacenter naming convention, as that is the hostname all the clients are connecting to.  If there is not a way to "trick" SQL Server into accepting this cert, what might my options be?  Here are some possibilities:

Make a new cert with a common name of the Windows domain hostname, but with a subject alternative name of the datacenter hostname.

Flip that around, and make a cert with a common name of the datacenter hostname, but a SAN of the Windows domain hostname.

Any other ideas?
0
 
LVL 81

Expert Comment

by:arnold
ID: 38389416
It is not an issue on your SQL server using the cert. people accessing db01.chi3.company.com, but the certificate is for a different host.
You need to generate the csr on the SQL server and then submit it for singing to the CA this way you will have production.comany.com signing the db01.chi3.company.com


You could use OpenSSL to generate the CSR and then go through the pkcs12 conversion as you have once the production ca signs the csr and provides you the cert.
0
 

Author Comment

by:ZMEadmin
ID: 38389646
But if I generate the CSR on the SQL Server, I still need to use the datacenter hostname as the common name in the cert.  You think generating the CSR on the SQL Server will add some data to the cert that will allow SQL Server to accept it?  Or am I misunderstanding your recommendation?

For what it's worth, I used our compay CA to generate a cert using the AD domain hostname as the common name (machinename.domain.company.com) rather than the datacenter hostname, and I then imported that into the Windows machine.  SQL Server accepted that one that just fine.

Regardless, I'll go ahead and generate the CSR on the SQL Server and see what happens.  Would you happen to have a recommended certreq INF file template, tuned for a SQL Server cert?  (Just to make sure I get all of the necessary attributes.)
0
 
LVL 81

Expert Comment

by:arnold
ID: 38390259
0
 

Author Comment

by:ZMEadmin
ID: 38407896
We're still having some troubles with this.  To eliminate some factors, I issued a cert/key pair (using our internal CA) using the Windows hostname, and SQL Server Configuration Manager sees that just fine.  We then configured a jdbc client to connect using that same hostname to prevent any name mismatches.  However, we're still getting an error with the client.

Our company's internal CA actually signs with an intermediate CA.  Can anyone verify if, when a client connects, SQL Server provides the intermediate CA's cert along with the host's cert?  It shouldn't matter in our case, since our client's keystore already trusts the root and intermediate CAs, but I wanted to verify.  (It's my understanding that, in general, a server  provides the intermediate certs along with its own cert, while clients are generally only expected to trust the root CAs.)

Update:  I answered my own question about the cert chains there.  We did a tcpdump and watched the ssl handshake, and verified that SQL Server does indeed provide both its cert and the intermediate certs to the client, so that's good.

Update2:  I can also successfully connect to the SQL Server with Management Studio using encryption, so I'm assuming that means encryption is at least *working* to some extent?
0
 

Author Comment

by:ZMEadmin
ID: 38407970
So, the only way we've found to fix the cert problem is to use the "trustServerCertificate=true" parameter in the jdbc connection string.  Not ideal, but it gets us passed the immediate cert errors.  However, now we get the following when connecting:

WARN - Could not obtain connection metadata
java.sql.SQLException: An attempt by a client to checkout a Connection has timed out.
Caused by: com.mchange.v2.resourcepool.TimeoutException: A client timed out while waiting to acquire a resource from com.mchange.v2.resourcepool.BasicResourcePool@14d659d -- timeout at awaitAvailable()


Anyone see that before when connecting to a SQL Server with encryption?  The server is by no means heavily loaded, and non-ssl connections work fine.
0
 

Accepted Solution

by:
ZMEadmin earned 0 total points
ID: 38438084
So after much experimentation, it looks like there are only two ways to get SQL Server to recognize a cert:
Make the common name in the cert match the Windows name of the machine.

Just to note:  It did not matter if we generated the cert/key pair with our internal CA and imported them into the SQL Server, or if I generated the CSR on the SQL Server and requested a cert from our CA.  The article I linked to describes the problem and how SQL Server queries the system for available certs.
0
 

Author Closing Comment

by:ZMEadmin
ID: 38450135
After much experimentation and searching the web , I arrived at that solution.  It addresses my needs, and would probably address the needs of the many, many people I saw posting on various forums with the exact same question I had.
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
Loops Section Overview
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question