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....
-Windows Server 2003
-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).
Thanks very much,