Installing Certificate on SQL Server 2005

Hi -

I'm trying to implement encryption using a 3rd party certificate. I'm following Uncle Bill's documentation on how to install a cert on the server using MMC... I can import the cert into the certificate store just fine. However, when I go to the SQL Server Configuration Manager and bring up the properties window for "Protocols for <Servername>" there are no certs in the dropdown list on the "Certificate" tab.

My certificate is valid... has a good date, etc. What could I be doing wrong? I thought I might have it in the wrong spot in the certificate store, but have moved it all around with no change in result...

Any help would be greatly appreciated!

Chris

Hi, Chris

SQL Server 2005 only displays certificates that are valid for usage with SQL 2005 encryption. So it verifies the following things:

1. Enhanced Key Usage section of cert contains:

Server Authentication (1.3.6.1.5.5.7.3.1)

2. Certificate has private key, and mark private key as exportable when generating it.

3. Certificate is in correct store (should be in Local Computer store under Personal Certificates for SQL running under localsystem or network service). .

4. Your sql service should run under admin account, otherwise it has no permissing to the private key and thus the cert would not show up in the drop down list of SSCM for you to choose the cert.

5. SQL Server 2005 has a new kind cert called self-signed certificate, which means when server can not find a good cert to load in the cert store, it will load such a self-signed cert,check your server errorlog, you could find key word "self-signed". In another word, you always can make encryption connection if you force server encryption, but, if you force client encryption, you will get error"certificate was not trusted" unless you specify trusting server cert through configuration,search more information in Books online.

BTW, you can refer following blog to gain more info about certificate in SQL Server 2005:

http://blogs.msdn.com/sql_protocols/archive/2005/12/30/508311.aspx

HTH

Ming.