SQL Server 2008 encrypted connection using multiple server names

Question

I have a SQL Server that must be accessed using various DNS names (SQL1, sql1.contoso.local, sql1.contoso.com, etc).

I'm struggling with creating a correct SSL certificate that can be used to secure all these names. Applications using Windows Integrated security are connecting to the server using it's local address, because from the Internet only the SQL Logins are enabled.

I have created SSL certificate with Subject (CN) containing sql1.contoso.com and with several SAN entries: DNS=SQL1, DNS=sql1.contoso.local, etc.

Using this certificate I can connect using encrypted connection only when I use the server sql1.contoso.com. Using other names will result in this error message in SQL Server Management Studio:

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)

I'm connecting to the server from a computer that trusts the CA that issued the SSL certificate used on the SQL Server and all the firewalls are correctly opened to allow connections using the 1433 port. The client also trusts the CA. The certificate was generated using Web Server template.

Is it possible to configure by SQL Server so I can connect to it using several domain names of the server using an encrypted connection?

I can only assign one certificate to a single SQL Server Instance, so I have to use one certificate for each accessible server name. If I could assign different certificates for the local connections I can use proper certificate signed by trusted CA instead of self-signed certificate.

I tried to generate a new certificate with multiple CN entries but I wasn't able to connect to the server using any of the defined names in CN entries.

Answer 1

I'm pretty sure that you'll need a certificate which has multiple CNs not one with DNS entries.

The certificate was generated using Web Server template.

I don't think that's the correct template. I don't have access to a CA at the moment, but I don't think that template uses the correct server authentication certificate. The note in my book says:

The certificate must be a server authentication certificate that 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 also be created using the KeySpec option of AT_KEYEXCHANGE; optionally the key usage property will include key encipherment.