SSL cert not showing up in SQL Server 2008 Configuration Manager

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.

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.

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...

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:



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.

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.)

http://social.msdn.microsoft.com/forums/en-US/sqlsecurity/thread/8f27fbb3-1566-408a-bfde-abdde17fb424/

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?

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.

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.