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.