Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
SQL Server SSL and Certificate. Certificate not Listed. Certificate's CN Name does not match

Answered SQL Server SSL and Certificate. Certificate not Listed. Certificate's CN Name does not match

  • Tuesday, May 03, 2011 2:34 PM
     
     
    Sign In to Vote
    0

    I have tried very hard to get SSL to work on SQL Server 2008 R2, running on Windows Server 2008 R2. But, can't. I am able to connect a query window with encryption (after a lot of tinkering and trying)! But, I can't get the Object Explorer in SQL Management Studio to use encryption. I'm also using SMS 2008 R2.

    The specific error I get when I try is:

    "Failed to connect to server domainName.com" +

    "A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: SSL Provider: 0 - The certificate's CN name does nto match the passed value.) (Microsoft SQL Server)"

    The certificate works without any warnings or errors for my website and also now works without a problem for the query window, even when I force encryption.


    When I try to select a certificate for SQL server to use, the list of certificates is blank, which means that the certificate isn't valid or isn't found. But it should be valid! Why won't it show up?

    Certificate is issued to "domainName.com"

    Server name is: "domainName".

    My Server domain is "domainName.com"

    I have the certificates stored in both the Local Computer Personal store and Trusted Root CA Store.

    The certificate's intended purpose is "Server Authentication, Client Authentication"

    I've enabled "Trust Server Certificate" in the SQL Server Configuration Manager.

    I think the main problem is that the certificate doesn't show up in the list of certificates for SQL server. But, how can I diagnose why? I can't find a reason why it shouldn't be there. Could it be that the certificate is issued to "domainName.com" and the server name is "domainName"? I've been banging my head against this thing for a while.

     

     

All Replies

  • Wednesday, May 04, 2011 2:38 AM
    Moderator
     
     
    Sign In to Vote
    0

    Hi davidhere40,

     

    Thank you for your post.

     

    I agree that this issue might be caused by the certificates are not installed properly.

     

    I would like to suggest you try to follow the below link about How to enable SSL encryption for an instance of SQL Server by using Microsoft Management Console:

    http://support.microsoft.com/kb/316898/en-us

     

    In addition, please take a look on the suggestions given by Charles from the below thread:

    http://social.msdn.microsoft.com/Forums/en/sqlgetstarted/thread/9b29f5e8-67fc-40d1-9bde-1521cbdfe07e

     

    If there are any progress, please feel free to let me know.

     

    Thanks,
    Weilin Qiao

    Please remember to mark the replies as answers if they help and unmark them if they provide no help. This can be beneficial to other community members reading the thread.
     
  • Thursday, May 05, 2011 4:10 AM
     
     
    Sign In to Vote
    0
    I was finally able to get SQL Server to list a certificate in the drop down list it provides by getting a brand new certificate for "domainName.domainName.com" which is my server's FQDN. So, I selected it, and restarted the service. Still the exact same problem! I would try to connect to the database using the server name as "domainName.domainName.com" but it tells me that no sql server instance exists at that address. It will only want to connect to the ip address or "domainName.com". How would I set it up so that sql server will listen on the FQDN "domainName.domainName.com"?
     
  • Thursday, May 05, 2011 5:17 AM
    Moderator
     
     
    Sign In to Vote
    0

    Hi davidhere40,

     

    Thank you for your updating.

     

    When we connect to SQL Sever, the server name in the connection string is as follows:

    • For default instance(MSSQLSERVER), we could just use the computer name as the server name.
    • For named instance, the server name should be computer_name\instance_name

     

    Please specify the correct server name of SQL Server as above mentioned, and also make sure the client trust the certificate by following steps:

     

    Copy your certificate into your client machine -> double-click to install -> select "Install Certificate". -> click "Next" -> select "Place all certificates in the following store" --> click "Browser" -> select "Trusted Root Certification Authorities" -> select OK and Finish.

     

    Thanks,
    Weilin Qiao

    Please remember to mark the replies as answers if they help and unmark them if they provide no help. This can be beneficial to other community members reading the thread.
     
  • Thursday, May 05, 2011 2:07 PM
     
     
    Sign In to Vote
    0

    I can't use the computer name or "computer_name\instance_name" to connect using SQL Authentication because it is a remote server.

    I am able to connect a query window *with* encryption by using "domainName.com" as the servername when using SQL Management Studio, but I cannot use SMS Object Explorer to connect with encryption.

    I've tried many variations such as "computerName.domainName.com" (it can't find anything there). I've tried "domainName.com\instance_name", etc. It doesn't find anything to connect to for any of those.

     
  • Thursday, May 05, 2011 2:18 PM
     
     
    Sign In to Vote
    0

    My Server's DNS Manager also has: Name="domainName.com" Type="Name Server (NS)" Data="domainName.domainName.com.".  Notice the extra dot at the end. I didn't do that as far as I know.

    My DomainName is the same as my ComputerName. I also have one entry for Name="domainName" Type="Host (A)" and Data="<my server ip address>"

    I'm not sure if any of those details are meaningful.

     
  • Friday, May 06, 2011 1:27 AM
    Moderator
     
      Has Code
    Sign In to Vote
    0

    Hi davidhere40,

     

    What do you mean you could not use Object Explorer to connect with encryption? Could you please paste the steps of your operation?

     

    Also please check that you have check on the Encrypt Connection on the Connection Properties page when you using SQL Server Management Studio to connect.

     

    And then please check the results of the below query, if the encryption_option is TRUE, then this connection is encrypted:

     

     

    SELECT b.session_id, a.original_login_name, a.program_name, b.net_transport, b.protocol_type, b.encrypt_option
FROM sys.dm_exec_sessions a, sys.dm_exec_connections b
WHERE a.session_id = b.session_id

     

     

    Thanks,
    Weilin Qiao

    Please remember to mark the replies as answers if they help and unmark them if they provide no help. This can be beneficial to other community members reading the thread.

     
  • Friday, May 06, 2011 2:15 AM
     
     
    Sign In to Vote
    0

    Here are the results of the query from an encrypted SQL Management Studio Query Window:

    session_id  original_login_name    program_name                                      net_transport                            protocol_type                            encrypt_option
    ----------- -----------------------------------------------------------------------------------------------------------------------------------------------------------------------
    52          david                           Microsoft SQL Server Management Studio - Query    TCP                                      TSQL                                     TRUE
    53          david                           Microsoft SQL Server Management Studio - Query    TCP                                      TSQL                                     TRUE
    55          david                           Microsoft SQL Server Management Studio - Query    TCP                                      TSQL                                     TRUE
    54          david                           Microsoft SQL Server Management Studio - Query    TCP                                      TSQL                                     TRUE

    (4 row(s) affected)

     

    But, for SQL Management Studio Object Explorer, this is what I try and the result:

    1. Open SQL Management Studio

    2. View --> Object Explorer (if it is not already open)

    3. At the top left of Object Explorer is a drop down menu labeled "Connect". Select Connect --> Database Engine

    4. The typical "Connect to Server" dialog pops up and asks for "Server Name" "Authentication" "login" and "password". I pass it the following parameters:

    For Server Name I use "domainName.com".

    For Authentication I use "SQL Server Authentication"

    Login: David

    Password: the password I have used to connect before

    5. Then click the "Options >>" button on the bottom right of the dialog. It will show the "Connection Properties" where I select "Encrypt Connect".

    6. Click "connect"

    7. Error:

    "Failed to connect to server domainName.com. (Microsoft.SqlServer.ConnectionInfo)" +

    "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 nto match the passed value.) (Microsoft SQL Server)"

     

    The same exact operation works perfectly when connecting using a query window instead of trying to connect the Object Explorer to the Database Engine. That's what I mean by one works with encryption and the other doesn't.

     


     
  • Friday, May 06, 2011 2:21 AM
    Moderator
     
     
    Sign In to Vote
    0

    Hi davidhere40,

     

    This is strange. Have you tried the below steps?

     

    Copy your certificate into your client machine -> double-click to install -> select "Install Certificate". -> click "Next" -> select "Place all certificates in the following store" --> click "Browser" -> select "Trusted Root Certification Authorities" -> select OK and Finish.

     

    Thanks,
    Weilin Qiao

    Please remember to mark the replies as answers if they help and unmark them if they provide no help. This can be beneficial to other community members reading the thread.
     
  • Friday, May 06, 2011 2:45 AM
     
     
    Sign In to Vote
    0

    Weilin,

    Yeah, I just downloaded both certificates from godady. One for "domainName.com" and the other for "computerName.domainName.com" (same as "domainName.domainName.com").

    I installed both of them and their "gd_bundle.crt" or "gd_iis_intermediates.p7b" certificates. I'm not sure what those do really, but I tried installing both in the Trusted Root CA store.

    No change at all :S I'm completely stumped.

     
  • Friday, May 06, 2011 3:32 AM
    Moderator
     
     
    Sign In to Vote
    0

    Hi davidhere40,

     

    Does the client must validate your server in your case? If not, we do not need specify the Encrypt Connection on the connection properties, but the connection is still encrypted.

     

    Please take a look the answer of ChunSong from the below thread:

    http://int.social.technet.microsoft.com/Forums/en/sqltools/thread/45b62ed2-1274-4edb-be3e-26aa78ceff29

     

    Thanks,
    Weilin Qiao

    Please remember to mark the replies as answers if they help and unmark them if they provide no help. This can be beneficial to other community members reading the thread.
     
  • Friday, May 06, 2011 4:05 AM
     
     
    Sign In to Vote
    0

    Interesting, but when I right click on the Database Engine in object explorer there is a link labeled "Connection Properties". When I click that and check the properties, it says it is not encrypted. If I right click on the database and click "new query" that window is encrypted. So, I wonder if the connection properties are true or not.

    I have force encryption enabled on the server under "SQL Server Network Configuration" --> "Protocols for MSSQLSERVER".

    But, I have two other configuration properties that don't have it "force encryption" enabled. Above and below the "SQL Server Network Configuration" are "SQL Native Client 10.0 Configuration (32bit)" and "SQL Native Client 10.0 Configuration". If I right click on those items there is another option for "Force Protocol Encryption". I was afraid to enable those because no one said to do so in any tutorial I found and I don't want to encrypt something unnecessarily, such as communication within the server that isn't over the internet. I tried enabling them anyway, temporarily, but it didn't change the Object Explorer connection properties and I still got the error, if I tried to force encryption on the client.

     
  • Friday, May 06, 2011 6:11 AM
    Moderator
     
     
    Sign In to Vote
    0

    Interesting, but when I right click on the Database Engine in object explorer there is a link labeled "Connection Properties". When I click that and check the properties, it says it is not encrypted. If I right click on the database and click "new query" that window is encrypted. So, I wonder if the connection properties are true or not.

    I have force encryption enabled on the server under "SQL Server Network Configuration" --> "Protocols for MSSQLSERVER".

    But, I have two other configuration properties that don't have it "force encryption" enabled. Above and below the "SQL Server Network Configuration" are "SQL Native Client 10.0 Configuration (32bit)" and "SQL Native Client 10.0 Configuration". If I right click on those items there is another option for "Force Protocol Encryption". I was afraid to enable those because no one said to do so in any tutorial I found and I don't want to encrypt something unnecessarily, such as communication within the server that isn't over the internet. I tried enabling them anyway, temporarily, but it didn't change the Object Explorer connection properties and I still got the error, if I tried to force encryption on the client.


    Hi davidhere40,

     

    If you have enabled the encryption on your sever side, the connection will be always encrypted.

     

    However, when the Encrypt Option on Connection Properties and the Force Protocol Encryption under the SQL Native Client 10.0 Configuration(32bit) and SQL Native Client 10.0 Configuration, it means that client will attempt to validate the sever using the SSL certificate, if the certificate is not trusted by the client, the connection will fail.

     

    Now it is a little confusing, let's start all over again.

     

    On the server side:

     

    1. Please install the certificate with the FQDN "computerName.domainName.com" and install it both in Personal and Trusted Root Certification Authorities store, please refer this link about how to install.
    2. Enable the Force Protocol Encryption:
      SQL Server Configuration Manager -> SQL Server Network Configuration -> Protocols for MSSQLSERVER -> Right click Properties -> Flags.
    3. Specify the certificate for SQL Server:
      SQL Server Configuration Manager -> SQL Server Network Configuration -> Protocols for MSSQLSERVER -> Right click Properties -> Certificate

     

    On the client side:

    1. Copy the Godaddy certificates to client machine and install this certificate to the Trusted Root Certification Authorities store, please follow this article.
    2. (Optional steps)If you also want to force client to validate the server, you have two choices:
      • Per machine basis:
        SQL Server Configuration Manager-> SQL Native Client Configuration -> Right click and select properties ->Force protocol encryption -> Yes
      • Per connection basis:
        • Using SSMS, Option -> Connection Properties -> select Encrypt Option.
        • Connection string. E.g., using the "Encrypt=yes" keyword as a connection attribute for an ODBC connection.

    If there are any more questions, please feel free to let me know.

     

    Thanks,
    Weilin Qiao

    Please remember to mark the replies as answers if they help and unmark them if they provide no help. This can be beneficial to other community members reading the thread.

     
  • Friday, May 06, 2011 1:35 PM
     
     
    Sign In to Vote
    0

    Weilin,

    I followed the instructions carefully. If the client's "Force Encryption" checkbox is not checked, then the connection properties says it is not encrypted. You can access the connection properties by right-clicking the Database Engine in Object explorer and then clicking the "Connection Properties" link. It has a property labeled "Encrypted". It says "No". So, either there is a bug, or the connection is not encrypted.

    Thanks,

    Dave


     
  • Thursday, May 12, 2011 6:52 AM
    Moderator
     
     Answered
    Sign In to Vote
    0

    Hi davidhere40,

     

    If you want to know whether this connection is encrypted or not, I would like to suggest you using the below query:

     

    SELECT b.session_id, a.original_login_name, a.program_name, b.net_transport, b.protocol_type, b.encrypt_option

    FROM sys.dm_exec_sessions a, sys.dm_exec_connections b

    WHERE a.session_id = b.session_id

     

    If you are concerning about this Encrypted label, I would like to suggest you submit a feed back to Microsoft Connect at this link https://connect.microsoft.com/SQLServer/Feedback. This connect site will serve as a connecting point between you and Microsoft, and ultimately the large community for you and Microsoft to interact with.

     

    Thanks,
    Weilin Qiao

    Please remember to mark the replies as answers if they help and unmark them if they provide no help. This can be beneficial to other community members reading the thread.
    • Marked As Answer by davidhere40 Thursday, May 12, 2011 1:23 PM
    •  
     
  • Thursday, May 12, 2011 1:29 PM
     
     
    Sign In to Vote
    0

    Weilin,

    Thank you!

    I ran the query and it comes back with the following results:

    The last column is "encryption_option".

    51    IIS APPPOOL\DefaultAppPool    .Net SqlClient Data Provider    Shared memory    TSQL    TRUE
    52    david    Microsoft SQL Server Management Studio - Query    TCP    TSQL    TRUE
    53    IIS APPPOOL\DefaultAppPool    .Net SqlClient Data Provider    Shared memory    TSQL    TRUE
    54    david    Microsoft SQL Server Management Studio    TCP    TSQL    TRUE
    55    david    Microsoft SQL Server Management Studio    TCP    TSQL    TRUE
    56    david    Microsoft SQL Server Management Studio    TCP    TSQL    TRUE

    Every one of them says encryption is on. I disconnected all queries and then connected one query to live and refreshed the live database object explorer item. When I did the refresh it added a couple more connections. The shared memory are from my live website.

    So, apparently they are all connected with encryption on, despite what the connection properties say. It is possible that it is not encrypted, but the only way to verify is to really use a network packet sniffer/watcher on my pc to see whats really going in and out. I've spent too much time on this already though and I've given up on investigating any further. I spent about a good week on it.

    Thanks for the help!

    Dave