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

user_59198 0
Viewed 88569

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.

sql-server-2005 ssl sql-server sql c#
3 Answers

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

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

Encrypt=Yes;hostNameInCertificate=<myservername>
user_14445480
Related