[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2934
  • Last Modified:

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

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 "DomainName.com".

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 = "DomainName.com".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 "....com". Should I?
The error I get when trying to connect to SQL Server using SQL Authentication 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 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.
  • 7
  • 7
1 Solution
Raja Jegan RSQL Server DBA & ArchitectCommented:
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..
daveycrocketAuthor Commented:
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 "domainName.com".

And when follow the instructions here:http://support.microsoft.com/kb/316898, 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 "domainName.com/david"?

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?
Raja Jegan RSQL Server DBA & ArchitectCommented:
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 "domainName.com/david"

Your Active Directory domain name should be something like domainName.com 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.
Creating Active Directory Users from a Text File

If your organization has a need to mass-create AD user accounts, watch this video to see how its done without the need for scripting or other unnecessary complexities.

daveycrocketAuthor Commented:
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 nothing 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"?
Raja Jegan RSQL Server DBA & ArchitectCommented:
>> domainName.domainName.com

Do you mean Computername.domainname.com

>> 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

domainName.domainName.com, portno

where portno is the port on which SQL Server listens.
daveycrocketAuthor Commented:
Yes, You're right that it is "ComputerName.DomainName.com". 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.
Raja Jegan RSQL Server DBA & ArchitectCommented:
>> 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.
daveycrocketAuthor Commented:

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.


Raja Jegan RSQL Server DBA & ArchitectCommented:
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:
daveycrocketAuthor Commented:
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.
Raja Jegan RSQL Server DBA & ArchitectCommented:
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.
daveycrocketAuthor Commented:
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!

Raja Jegan RSQL Server DBA & ArchitectCommented:
Thanks Dave for posting the solution here..
daveycrocketAuthor Commented:
no one else said how to figure out that the connection really was encrypted when the connection properties said it wasn't.

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

  • 7
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now