• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 9308
  • Last Modified:

SSL cert not showing up in SQL Server 2008 Configuration Manager

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.
  • 7
  • 3
  • 2
1 Solution
Rich WeisslerProfessional Troublemaker^h^h^h^h^hshooterCommented:
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.
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.
ZMEadminAuthor Commented:
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?)
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

Rich WeisslerProfessional Troublemaker^h^h^h^h^hshooterCommented:
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...
ZMEadminAuthor Commented:
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:


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?
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.
ZMEadminAuthor Commented:
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.)
ZMEadminAuthor Commented:
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?
ZMEadminAuthor Commented:
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.
ZMEadminAuthor Commented:
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.
ZMEadminAuthor Commented:
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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

  • 7
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now