Compartir a través de

Implementing SSL encryption for SQL Server in a DNS forwarding environment

  • Artículo

Let’s say you have an environment which implements DNS forwarding. In such a setup, the client uses a different name (or FQDN) while connecting to SQL Server than the actual SQL Server name (or FQDN). The connection request is forwarded to actual SQL Server through DNS forwarding implemented at the n/w layer.

In such an environment, the standard procedure for implementing SSL Encryption will not work. This post seeks to list out the steps needed to implement SSL Encryption successfully in such a scenario.

The Error

Do we love them or what? If you try to implement SSL encryption (either client side or Server side) using the standard procedure, the attempt to connect to SQL Server will generate the following error:-

[System.Data.SqlClient.SqlException]
{"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.)"}         

The reason we get this error is because the client is submitting a connection request to say, 'Server X' (which doesn't exist), and the actual SQL Server name is, say, 'Server Y'. Normal connectivity works fine, as the DNS forwarding alias exists on the n/w (the DNS server uses something like a mapping table to look up the actual server name for each request, and then redirects the connection attempt to the concerned server). While using force protocol encryption, the connection request fails since the Certificate being used has been issued to the actual Server name, and the responding server name is different from the one specified in the connection request. As a result, connectivity will fail.

So how do we fix it?

There are basically three things that need to be done for implementing SSL Encryption in such an environment:-

1) Create DNS CNAME record(s) which map the alias(es) to the "actual" name of SQL server: - Please use the KB article titled “Creating a DNS Alias Record” available at https://support.microsoft.com/kb/168322

Basically, this relates to the DNS forwarding part of the problem. If we create A records instead of CNAME records for the aliases, we get this error:-

[Microsoft] [ODBC SQL Server Driver] [TCP/IP Sockets] SSL Security error

2) Create a single DNS A record for that "actual" name of the SQL Server.

3) Create a SSL certificate with the "Subject Alternative Name" field: - In such a scenario, the certificate should have the "SUBJECT ALTERNATIVE NAME" field enabled, and this should contain the actual name or FQDN of the SQL Server ('Server Y' in the example above) as well as all the aliases ('Server X' in the example above).

The CN of the "SUBJECT " field should contain the "Actual" name of the SQL server.

4) To enable Subject Alternative Name field, run the following command on the CA(Certification Authority) server: -

certutil -setreg policyEditFlags +EDITF_ATTRIBUTESUBJECTALTNAME2
net stop certsvc
net start certsvc

This command will add a registry entry to enable the "SUBJECT ALTERNATIVE NAME" field on the certificates.

If you are using a third party certificate, request your vendor to issue you a certificate with the "SUBJECT ALTERNATIVE NAME" field enabled. The contents the "SUBJECT ALTERNATIVE NAME" field should have are outlined in Step 6.

5) Submit a new certificate request to the CA (Certification Authority) to get the new certificate issued with both "SUBJECT" and "SUBJECT ALTERNATIVE NAME".

6) The CN name of the "SUBJECT" should have the "actual" name of the SQL Server. The "SUBJECT ALTERNATIVE NAME" field on the certificate should have the actual SQL Server name, as well as all the CNAME labels (aliases).

7) Install the newly issued certificate on the SQL Server or the client depending on whether you are trying to implement Server side or client side encryption.

Please feel free to post the questions in comment section!

Comments

  • Anonymous
    September 20, 2012
    Very helpful post

  • Anonymous
    April 21, 2015
    I am running into the same issue. The difference is we have an active-passive MS cluster of 2 servers on which the SQL server instance is running. The cert CN points to the cluster virtual IP, and the SANs include the individual server name. How can this issue be overcome in this case?

  • Anonymous
    July 29, 2015
    The comment has been removed