I'm running into a problem with configuring SQL over SSL on a SQL 2005 server. Hoping someone can tell me what I'm doing wrong....

Setup:
-Windows Server 2003
-SQL 2005
-Certificate from Thawte - Proper one for server authentication
-SQL Service runs under Administrator

Here's what I've done so far:

1. A Certificate has been purchased from Thawte, with the FQDN of "servera.domain.com" (to match the external DNS name of the SQL server)
2. I have provisioned the certificate on the server, by using the Certificates MMC to import the .CER file from Thawte into the Computer store (tried user store also, for kicks - didn't help)
3. Went into SQL Configuration Manager, which doesn't show the certificate (Certificate field is blank)
3a. Found a workaround, which was to add the certificate's thumbprint (cert hash) without spaces to the certificate value under "HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.x\MSSQLServer\SuperSocketNetLib" registry key.

At this point, I try to start the SQL service and it starts and then stops. An error appears in the event log - Event ID # 26014 - "Unable to load user-specified certificate. The server will not accept a connection. You should verify that the certificate is correctly installed".

I looked at the Microsoft's certificate requirements for SQL Server 2005 to load a SSL certificate. The cert meets all of the criteria, but the subject property of the certificate is making me wonder.... The requirement is for the subject property to "indicate that the common name (CN) is the same as the host name or fully qualified domain name (FQDN) of the server computer". The CN of the cert is "servera.domain.com", in order for it to match the internet DNS record, but the server name is "servera.internal.local". Could SQL be refusing to use the cert due to the CN being a bit off? This is the only thing I can think of, but not sure how one would get around this issue without naming the server the external DNS name (not generally recommended).

Any ideas?

Thanks very much,

Vishnu

I can't seem to get a response from anyone on any SQL forums..... Has nobody ever run into this????

Ideas? Anybody?

I have not run into this, but have only worked with this in a test environment sith self signed certs.

Try registering the cert using httpcfg, see the below link for more details:

http://technet2.microsoft.com/windowsserver/en/library/e17527d2-105a-451f-8e3f-d515479527011033.mspx?mfr=true

Also assure that the certificate meets:

• The certificate must be in either the local computer certificate store or the current user certificate store.
  
  
• The current system time must be after the Valid from property of the certificate and before the Valid to property of the certificate.
  
  
• The certificate must be meant for server authentication. This requires the Enhanced Key Usage property of the certificate to specify Server Authentication (1.3.6.1.5.5.7.3.1).
  
  
• 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).
  
  
• The Subject property of the certificate must indicate that the common name (CN) is the same as the host name or fully qualified domain name (FQDN) of the server computer. If SQL Server is running on a failover cluster, the common name must match the host name or FQDN of the virtual server and the certificates must be provisioned on all nodes in the failover cluster.
  

Thanks for the idea, but it didn't help. I used the httpcfg command, which completed successfully. Then I put the thumprint of the certficate back into the "certificate" value of the "HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer\SuperSocketNetLib" registry key. When I tried to restart the SQL service, it failed and logged the following errors in the System event log:

EventID 26014 - Unable to load user-specified certificate

EventID 17182 - TDSSNIClient initialization failed with error 0x80092004, status code 0x80.

EventID 17182 - TDSSNIClient initialization failed with error 0x80092004, status code 0x1.

Also, as far as the certificate requirements. The cert meets all of the requirements, except maybe the last one -- "Subject property of the certificate". The certificate's subject is the FQDN of the external host name. The internal host name is different. It would seem to make sense to me that this would be what is causing the issue, but how would one get around this when the FQDN's are different and need to stay this way?

Thanks again,

Vishnu

Sorry, but I have exceeded my knowledge on this issue,put me back in the lab with a self signedcert and I'd of had you off and running. I would recommend contactingThawte support and see if they have a had similar issues or can provide a greater insight.

David,

Thanks anyway. You got me thinking a bit, leading me to try a differnt angle. I went to Thawte's site and generated a trial certificate, using the internal FQDN. This certificate worked properly and SQL started. So, I beliefve it's definitely related to the FQDN. I'll give Thawte a call, to see what I need to do about this.

I'll let you know how it turns out.

-Vishnu

Hi ,

Any luck on that. I am struggling with the same issue.

Certificate Requirements For SQL Server to load a SSL certificate, the certificate must meet the following conditions:

The certificate must be in either the local computer certificate store or the current user certificate store.

The current system time must be after the Valid from property of the certificate and before the Valid to property of the certificate.

The certificate must be meant for server authentication. This requires the Enhanced Key Usage property of the certificate to specify Server Authentication (1.3.6.1.5.5.7.3.1).

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

The Subject property of the certificate must indicate that the common name (CN) is the same as the host name or fully qualified domain name (FQDN) of the server computer.

If SQL Server is running on a failover cluster, the common name must match the host name or FQDN of the virtual server and the certificates must be provisioned on all nodes in the failover cluster.

SQL Server 2008 R2 and the SQL Server 2008 R2 Native Client support wildcard certificates. Other clients might not support wildcard certificates.

For more information, see the client documentation and KB258858.

following will help you.

https://support.microsoft.com/en-us/kb/316898/

• Edited by Dinesh Vishe 13 hours 26 minutes ago

Certificate Requirements For SQL Server to load a SSL certificate, the certificate must meet the following conditions:

The certificate must be in either the local computer certificate store or the current user certificate store.

The current system time must be after the Valid from property of the certificate and before the Valid to property of the certificate.

The certificate must be meant for server authentication. This requires the Enhanced Key Usage property of the certificate to specify Server Authentication (1.3.6.1.5.5.7.3.1).

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

The Subject property of the certificate must indicate that the common name (CN) is the same as the host name or fully qualified domain name (FQDN) of the server computer.

If SQL Server is running on a failover cluster, the common name must match the host name or FQDN of the virtual server and the certificates must be provisioned on all nodes in the failover cluster.

SQL Server 2008 R2 and the SQL Server 2008 R2 Native Client support wildcard certificates. Other clients might not support wildcard certificates.

For more information, see the client documentation and KB258858.

following will help you.

https://support.microsoft.com/en-us/kb/316898/

• Edited by Dinesh Vishe Friday, May 01, 2015 5:40 PM

I know this is an old thread, but I just wanted to let you know how we handle this issue.  We use a SAN cert (Subject Alternate Name), which allows you to have multiple FQDN's in the same cert.  Therefore, when you access the server using the server name FQDN as well as an alias FQDN...  So we like to use SAN certs so that processes and users can connect to the SQL server over SSL/TLS using multiple FQDN's.  Maybe this will help your situation...

HTH
Ozone