locked
SSL for SQL Server Instance RRS feed

  • Question

  • I must admit that I'm a little new to the SQL Server DBA role however I have been doing ok up to this point.  We had a self-signed certificate for our primary instance of SQLServer and just recently had a test certificate issued from a trusted CA.  This certificate was issued for a hostname; the DNS zone for that server has an A name which points that host name to our locally hosted/managed SQL server.  I am able to connect to the instance fine by using this method.  The problem I'm having is that the subject line of the SSL certificate lists the hostname and as such, SQL server will not let me use it because it does not match the machine name/hostname. 

     Our problem is that we don't use LDAP/AD so I'm kind'a confused as to how we would get around this limitation.  Any thoughts?

    Thursday, January 12, 2012 5:37 PM

Answers

  • If you check the blog, Ken, you will find that there are a few things that need to be done here:-

    1) 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.

    2) Create DNS CNAME record(s) which map the alias(es) to the "actual" name of SQL server

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

    So, in short, you cannot see the certificate because it has not been generated with the correct set of values. In order for a certificate to be used with SQL, it must be generated with the Subject name indicating that the Common Name (CN) is the same as the hostname or FQDN of the actual server which has SQL Server installed.(refer the Certificate Requirements section in http://technet.microsoft.com/en-us/library/ms189067.aspx)

    This means that even though you are able to connect to the instance just fine now, it's not like SQL Server is actually using the certificate in question. In order to get this to work, you will have to request a proper certificate (one which satisfies all the certificate requirements in the technet article mentioned above),  and then follow the steps listed in my blog. 

    Hope this answers your doubts. Do let me know if I can assist further.


    HarshDeep Singh
    Support Engineer, Microsoft Developer Support
    MCTS, MCITP: DBA (SQL Server 2005, SQL Server 2008)


    • Edited by HarshDeep_Singh Saturday, January 14, 2012 2:52 AM
    • Proposed as answer by HarshDeep_Singh Saturday, January 14, 2012 2:53 AM
    • Marked as answer by KenCrist Saturday, January 14, 2012 2:53 AM
    Saturday, January 14, 2012 2:51 AM

All replies

  • I ran into a similar situation on a case some time back, so decided to write a blog about this issue:-

    http://blogs.msdn.com/b/sqlserverfaq/archive/2011/08/08/implementing-ssl-encryption-for-sql-server-in-a-dns-forwarding-environment.aspx

    Hope this is what you need. Do let me know if you have any further concerns or queries.

    ---------------------------------------------------------------------------------------------
    HarshDeep Singh 
    Support Engineer, Microsoft Developer Support 
    MCTS, MCITP: DBA (SQL Server 2005, SQL Server 2008)



    • Proposed as answer by HarshDeep_Singh Friday, January 13, 2012 7:08 PM
    • Unproposed as answer by HarshDeep_Singh Friday, January 13, 2012 7:16 PM
    • Proposed as answer by HarshDeep_Singh Friday, January 13, 2012 7:43 PM
    • Edited by HarshDeep_Singh Friday, January 13, 2012 7:45 PM
    • Unproposed as answer by KenCrist Saturday, January 14, 2012 12:09 AM
    Friday, January 13, 2012 7:07 PM
  • My problem is that we can't even select the certificate from within Windows Server for SQL Server to work.  It doesn't show up in the drop down box because the CN doesn't match the server name.
    Saturday, January 14, 2012 12:09 AM
  • If you check the blog, Ken, you will find that there are a few things that need to be done here:-

    1) 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.

    2) Create DNS CNAME record(s) which map the alias(es) to the "actual" name of SQL server

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

    So, in short, you cannot see the certificate because it has not been generated with the correct set of values. In order for a certificate to be used with SQL, it must be generated with the Subject name indicating that the Common Name (CN) is the same as the hostname or FQDN of the actual server which has SQL Server installed.(refer the Certificate Requirements section in http://technet.microsoft.com/en-us/library/ms189067.aspx)

    This means that even though you are able to connect to the instance just fine now, it's not like SQL Server is actually using the certificate in question. In order to get this to work, you will have to request a proper certificate (one which satisfies all the certificate requirements in the technet article mentioned above),  and then follow the steps listed in my blog. 

    Hope this answers your doubts. Do let me know if I can assist further.


    HarshDeep Singh
    Support Engineer, Microsoft Developer Support
    MCTS, MCITP: DBA (SQL Server 2005, SQL Server 2008)


    • Edited by HarshDeep_Singh Saturday, January 14, 2012 2:52 AM
    • Proposed as answer by HarshDeep_Singh Saturday, January 14, 2012 2:53 AM
    • Marked as answer by KenCrist Saturday, January 14, 2012 2:53 AM
    Saturday, January 14, 2012 2:51 AM