Using Encrypt=yes in a Sql Server connection string -> "provider: SSL Provider, error: 0 - The certificate's CN name does not match the passed value."

Question

I'm using Encrypt=yes in a SQL Server connection string, as I need the TCPIP traffic to be encrypted, but on opening the connection I get an error:

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

Any suggestions how to fix this? I assume I need some sort of certificate relationship between my servers, but have no idea where to start.

I need this for two connections, one each to a SQL 2000 server and one to a 2005 server.

German Latorre · Accepted Answer · 2017-03-06 00:57:07Z

128

Your database connection can be configured to encrypt traffic and to accept any certificate from your server. Not a grand solution, but it worked for me.

The resulting connection string should look like this:

"[...];Encrypt=True;TrustServerCertificate=True"

edited Mar 6, 2017 at 0:57

answered May 21, 2012 at 8:21

German Latorre

11.4k15 gold badges51 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Juliën Over a year ago

Indeed. A minor addition: Beginning in .NET Framework 4.5, when TrustServerCertificate is false and Encrypt is true, the server name (or IP address) in a SQL Server SSL certificate must exactly match the server name (or IP address) specified in the connection string. Otherwise, the connection attempt will fail. More info at MSDN

Sunil Singhal Over a year ago

Don't set trustServerCertificate=true ever in LIVE. There is another connection property "hostNameInCertificate" which you can use. Please refer this Microsoft Doc

vic Over a year ago

Using the native client 11.0 driver, I had to use "yes" instead of "true".

Vort3x · Accepted Answer · 2011-12-14 06:42:50Z

14

I realize this is pretty old, but thought this might still help someone.

If the server you are connecting to does not have a certificate installed, a default certificate number is generated each time the server is restarted. When this happens the CN number changes, and might not match the one you have.

I read this recently, but I'm still trying to find the link for you. I'd suggest you makes sure that the server you are connecting to has explicitly installed a certificate, and that your client has it too.

I'll update this as soon as I find the link.

answered Dec 14, 2011 at 6:42

Vort3x

1,8282 gold badges21 silver badges37 bronze badges

1 Comment

Ceisc Over a year ago

I appreciate that I'm a bit late to the party, but if anyone still needs the source of that info they can go here technet.microsoft.com/en-us/library/ms189067(v=sql.105).aspx

Henrik Høyer · Accepted Answer · 2023-07-26 10:20:13Z

8

You can't encrypt the the connection without also having a certificate installed on the Server. By default SQL Server will present a self signed certificate, and this is (and should be) rejected by clients.

Your options are:

a) The solution is to install a real certificate on the SQL Server:

Certificate Management (SQL Server Configuration Manager)

b) If you aren't able to install a real certificate on the SQL Server (a few $/year). You can issue a self signed certificate and trust this specific certificate on your client machines.

c) If you really do want to ignore this security problem. Please don't do this you do have the option to add an "ignore this security warning flag" (TrustServerCertificate) to the connectstring:

Encrypt=Yes;TrustServerCertificate=Yes

(added 2023-07-27) If you are using ODBC Driver for SQL Server version 18.x or newer:

Encrypt=Yes;hostNameInCertificate=<myservername>

d) If you are using JDBC there is an addition connectstring property that can be used instead of TrustServerCertificate

Encrypt=Yes;hostNameInCertificate=<myservername>

edited Jul 26, 2023 at 10:20

answered Apr 8, 2021 at 13:42

Henrik Høyer

1,8091 gold badge26 silver badges31 bronze badges

2 Comments

Martin Over a year ago

Real certificate, could you elaborate what you mean?

Henrik Høyer Over a year ago

A certificate from a trusted Certificate Authorities like LetsEncypt, Comodo, DigiSign etc...

Wayne Yan · Accepted Answer · 2023-08-03 07:24:37Z

4

Note that a public CA is not not necessary if the SQL server is part of a well organized domain. Windows Active Directory Certificate Services can also be used to issue certificates to machines on the domain.In combination with group policy you can also ensure that all servers on the domain are issued with the root certificate (i.e. the root certificate is automatically placed in "Trusted Root Certification Authorities"). This is a better solution than a "self-signed" certificate.

answered Aug 3, 2023 at 7:24

Wayne Yan

713 bronze badges

Collectives™ on Stack Overflow

Using Encrypt=yes in a Sql Server connection string -> "provider: SSL Provider, error: 0 - The certificate's CN name does not match the passed value."

4 Answers 4

3 Comments

1 Comment

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

1 Comment

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related