Freigeben über

Implementing SSL encryption for SQL Server in a DNS forwarding environment

  • Artikel

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 policy\EditFlags +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!

Written By: – Harshdeep Narula,SE,Microsoft GTSC

Reviewed By: – Levi Justus & Akbar Farishta,TL, Microsoft GTSC

Comments

  • Anonymous
    August 26, 2011
    You mentioned that the SUBJECT ALTERNATIVE NAME should list all aliases. The actual hostname or FQDN so should be the CN? what about the friendly name? will the cert outline below work? server hostname = server1.internaldomain.com fqdn used to connect = serverdb.externaldomain.com this is the request planning on submitting to our CA: friendly name = server1.internaldomain.com SUBJECT/CN = server1.internaldomain.com SUBJECT ALTERNATIVE NAME serverdb.myexternaldomain.com < - name used to access the server serverdb2.myexternaldomain.com < - name used to access the server

  • Anonymous
    September 06, 2011
    Hi, Thanks for showing interest in the blog post. Here's a modified version of  your outline that will work:- server hostname = server1.internaldomain.com fqdn used to connect = serverdb.externaldomain.com <b> This is the request planning on submitting to our CA: </b> friendly name = <blank> SUBJECT/CN = server1.internaldomain.com SUBJECT ALTERNATIVE NAME server1.internaldomain.com serverdb.myexternaldomain.com < - name used to access the server serverdb2.myexternaldomain.com < - name used to access the server We recommend that the friendly name be left blank because we have seen issues with certificates in the past, where friendly names have been specified. Hope this helps. Please let me know if you have any further questions or concerns.