Sql 2008 r2 wildcard certificate

Question

I have installed wildcard certificate in Certificates(Local Computer)\Personal in sql server. We are using Windows 2008R2 server and 2008 R2 SQL. Certificate is issued by StartCom. With certificate everything is ok (I have used it in IIS), but it didnt appeared in sql configuration manager protocols dropdown list. So i followed microsoft article:

http://support2.microsoft.com/kb/316898

and added certificate thumbprint in registry. Restarted SQL service.

Then I am trying to connect to sql server using SMSS I am getting error:

"A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: SSL Provider, error: 0 - The certificate's CN name does not match the passed value.) (Microsoft SQL Server, Error: -2146762481)"

So obviously problem is wildcard certificate, because it is issued to *.domain.com and server name is server.domain.com.

By this article, microsoft sais that "SQL Server 2008 R2 and the SQL Server 2008 R2 Native Client support wildcard certificates. "

http://technet.microsoft.com/en-us/library/ms189067(v=sql.105).aspx

So question would be: Sql 2008 r2 supports or does not support wildcard certificates. If supports, so there is problem? Why i am getting this error?

So after few days of trying to use wildcard certificate, we generated certificate with hostname, and after 5 min we enjoyed an encrypted connection. But still there is a question..Can sql 2008 r2 work with wildcard certificates or not?

Answer 1

It took a large amount of trial and mostly error for me to get it to work but SQL Server does support wildcard certificates. I'm not sure if all my steps are necessary but skipping any failed for me.

1. Create a self signed certificate where the Common Name is the same as the Windows server name.

   makecert -r -pe -n "CN=HOSTNAME" -b 01/01/2015 -e 01/01/2025 -eku 1.3.6.1.5.5.7.3.1 -ss my -sr localMachine -sky exchange -sp "Microsoft RSA SChannel Cryptographic Provider" -sy 12
   

2. The self-signed certificate will appear now in the SQL Server Configuration Manager drop down. Configure SQL Server encryption options with the self-signed certificate. Restart SQL Server and test.

3. Obtain the certificate hash of your wildcard SSL certificate.

   certutil -store "my" "*.domain.com"
   

4. Edit the registry and replace the Certificate entry with the certificate hash of your wildcard SSL certificate. Restart SQL Server and test.

   HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL*version_revision*.MSSQLSERVER\MSSQLServer\SuperSocketNetLib\
   

When I initially tried it without first creating a self-signed certificate SQL Server wouldn't start.