SQL Server Doesn't like my SSL Certificate's CN (Certificate Name) and refuses to use the Certificate.

Posted on 2011-05-03
Last Modified: 2012-05-11
Even if you don't know how to solve my specific problem, can you please tell me if my server settings are normal or are strange? That may help me fix the problem.

 think my server settings and active directory settings are screwing up SQL Server 2008 and making reject my SSL certificate .

My Server Name is "DomainName".

My certificate is issued to "".

My active directory domain is "DomainName0".

SQL Server says that my certificate name does not match the passed in value when I connect to it using SSL in SMS object explorer. I'm connecting to it using SQL Authentication with server = "".In my DNS on my server (same machine) I only have one entry for "domainName" "Host (A)" that points to the ip address. I do not have an entry for "". Should I?
The error I get when trying to connect to SQL Server using SQL Authentication is:
"Failed to connect to server"
"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 not match the passed value.) (Microsoft SQL Server)"

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.

The certificate doesn't show up in the list of certificates for SQL server, which means it doesn't like the Certificate Name compared to what it expects... but I cant' figure out what it expects.

To top it all off, the ssl connection *does*(!) work for the query window. If I force it to encrypt, it connects and queries just fine. No errors. No warnings, except that I had to enable "trust server certificate" to get it to work.
Question by:daveycrocket
    LVL 57

    Expert Comment

    by:Raja Jegan R
    Follow the steps mentioned over here:

    It relates to Configuring a Citrix application using SQL Server as backend and the steps specified at SQL Server applies to your scenario..

    Author Comment

    The article says to request a new certificate. I'm not sure I need to do this because I already have a certificate issued to "".

    And when follow the instructions here:, which is linked from the article you suggested, it tells me to go to the Personal certificate store, right click --> All Tasks --> Request New Certificate. I can't get passed "Certificate Enrollment" in the "Request New Certificate" wizard. I get the message "Certificate types are not available".

    I think it has something to do with the fact that my login is "domainName0/david". which is my active directory domain. Something is weird about that. Is my active directory domain correct? Shouldn't it be something like ""?

    The certificate list in sql server still comes up empty, so I either need to fix what's wrong with the certificate or fix the domain issues. Any ideas?
    LVL 57

    Expert Comment

    by:Raja Jegan R
    Yes, since you have the certificate available with you there is no need to request for a new certificate and instead you can use the Existing certificate and Register your certificate directly.

    >> Is my active directory domain correct? Shouldn't it be something like ""

    Your Active Directory domain name should be something like and David can be a login within your Active Directory

    >> The certificate list in sql server still comes up empty, so I either need to fix what's wrong with the certificate or fix the domain issues.

    You need to Register or upload your New Certificate. Steps given below:

    Management Console (MMC) and add the Certificates snap-in:

    1. Click Start, and then click Run.
    2. Type mmc and click OK.
    3. Click Console in the new MMC you created, and then click Add/Remove Snap-in.
    4. In the new window that appears, click Add -> Highlight Certificates -> click Add
    5. Choose the Computer account option -> click Next -> Select Local Computer on the next screen -> click Finish.
    6. Click Close -> Click OK.

    Author Comment

    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 "" which is my server's FQDN. So, I selected it, and restarted the service. Still nothing the exact same problem! I would try to connect to the database using the server name as "" but it tells me that no sql server instance exists at that address. It will only want to connect to the ip address or "". How would I set it up so that sql server will listen on the FQDN ""?
    LVL 57

    Expert Comment

    by:Raja Jegan R

    Do you mean

    >> but it tells me that no sql server instance exists at that address.

    Have you enabled Remote connections for your SQL Server. If not, then do the steps below:

    Then you would be able to connect to your SQL Server instance.
    If SQL Server is configured to listen on a port other than default port 1433, then you need to specify Server name like this, portno

    where portno is the port on which SQL Server listens.

    Author Comment

    Yes, You're right that it is "". But, my domainName is the same as my computer name.

    I do have remote connections enabled. In fact, I can connect user a new query window *with encryption on*! But, I have to user TrustServerCertificate=true in the extra parameters of the connection. The only time it doesn't work is for SQL Management Studio's Object Explorer. I want to be able to edit the live database structure and explore the schema with encryption on.
    LVL 57

    Expert Comment

    by:Raja Jegan R
    >> But, I have to user TrustServerCertificate=true in the extra parameters of the connection.

    Yes, it should be that way..

    >> The only time it doesn't work is for SQL Management Studio's Object Explorer.

    Ok, To connect to this particular server using SSL, you need to force SSMS to use Encrypted connection by following the steps below:

    1. In SSMS, Click View -> Registered Servers
    2. Right Click Local Server Groups and Choose New Server Registration
    3. Provide your Server name and Click Connection Properties Tab
    4. Check Encrypt Connection option

    From now onwards, whenever you connect to this server, it will use Encrypted connection.

    Hope this helps.

    Author Comment


    It doesn't work. If I register the server like that, and check the box to "force encryption" it will give me the same exact error about the CN Name not matching. If I uncheck "force encryption", it connects. Then I check the connection properties of Object explorer using the following method, but it 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.


    LVL 57

    Expert Comment

    by:Raja Jegan R
    Ok, there are two things which I wanted to clarify

    1. Where are you trying to enforce Encryption from either Server or Client.

    If from Server, then follow the steps below:

    If from Client, then follow the steps below:

    Once either one of the above is done properly, you should be able to use Encrypted connections in SQL Server.
    Another link that might be of use for you:

    Author Comment

    I'm using the server to enforce it. The article requires that the sql service be running under the same account as the configuration manager unless you are running the service under LocalSystem, etc. I am running it under LocalSystem and am an administrator.

    I have carefully imported the public key certificate of my Certificate's CA to the Trusted Root CA folder for local computer on the client.

    It also says to force encryption on the server for the setting "SQL Server Native Client Configuration". I tried that yesterday temporarily. I set it to force encryption = true, restarted the sql server service. Tried again, same error. If I don't enfoce encryption on the client, I check the connection, it says encrypted=NO.

    I've checked that the certificate covers Server Authentication ( SQL server has accepted the certificate, so it must meet the other requirements, which I've checked as best I can.

    I'm telling you, I have followed the instructions multiple times carefully. It doesn't work. I'm completely stumped.
    LVL 57

    Expert Comment

    by:Raja Jegan R
    Ok, Kindly confirm whether you have applied encryption at instance level by doing the below steps:

    1. Open SQL Server Configuration Manager.
    2. Expand SQL Server Network Configuration -> Expand it
    3. Right Click Protocols for your instance -> Click Certificates tab
    4. Import your certificate over there

    Ideally performing all steps should work.
    If not, then try applying all Latest updates and Service packs for your SQL Server instance and Windows OS and retry the configurations again.

    Accepted Solution

    An administrator from microsoft was able to help me finally solve the mystery:

    The following query tells me whether my connections are encrypted or not:

    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

    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!

    LVL 57

    Expert Comment

    by:Raja Jegan R
    Thanks Dave for posting the solution here..

    Author Closing Comment

    no one else said how to figure out that the connection really was encrypted when the connection properties said it wasn't.

    Featured Post

    Find Ransomware Secrets With All-Source Analysis

    Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

    Join & Write a Comment

    #SSL #TLS #Citrix #HTTPS #PKI #Compliance #Certificate #Encryption #StoreFront #Web Interface #Citrix XenApp
    Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
    This tutorial will walk an individual through the process of transferring the five major, necessary Active Directory Roles, commonly referred to as the FSMO roles from a Windows Server 2008 domain controller to a Windows Server 2012 domain controlle…
    This tutorial will walk an individual through the process of configuring their Windows Server 2012 domain controller to synchronize its time with a trusted, external resource. Use Google, Bing, or other preferred search engine to locate trusted NTP …

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now