Problem during SSL connection to SQL Server

Question

I want to connect to my local SQL Server 2008 database using SSL.

I didn't install any certificates on the server because according to this http://msdn.microsoft.com/en-us/library/ms189067.aspx

If a trusted certificate is not installed, SQL Server will generate a self-signed certificate when the instance is started, and use the self-signed certificate to encrypt the credentials.

This is simple code

SqlConnection connection = 
    new SqlConnection(@"Data Source=somePC\SqlExpress;Initial Catalog=test;integrated security = sspi;Persist Security Info=True;Encrypt=true;");

SqlCommand command = new SqlCommand("Select count(*) from Employee", connection);
connection.Open();
int employeeCount = (int)command.ExecuteScalar();
connection.Close();`

Note that encrypt=true;

but on connection.Open() an exception is raised with message

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 chain was issued by an authority that is not trusted.)

Have can I approve this self-signed certificate ?

Dave Lucre Dave Lucre · Accepted Answer · 2013-01-06T21:00:04

You need to tell your client to trust whatever certificate is presented. A self-signed certificate will not be trusted because it's not signed by any CA your computer trusts. Change your connection by adding TrustServerCertificate=True as follows:

SqlConnection connection = 
    new SqlConnection(@"Data Source=somePC\SqlExpress;Initial Catalog=test;integrated security = sspi;Persist Security Info=True;Encrypt=true; TrustServerCertificate=True");

SqlCommand command = new SqlCommand("Select count(*) from Employee", connection);
connection.Open();
int employeeCount = (int)command.ExecuteScalar();
connection.Close();

Problem during SSL connection to SQL Server

2 Answers