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 · Accepted Answer · 2013-01-06 21:00:04Z

10

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();

answered Jan 6, 2013 at 21:00

Dave Lucre

1,1651 gold badge14 silver badges16 bronze badges

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

Comments

Preet Sangha · Accepted Answer · 2014-07-20 03:39:41Z

2

In order for RPC over Http to work you must have a Trusted CA Root Certificate installed and configured. In a situation where you are using a self-signed cert you will need to install the certificate into the Trusted Root Certification Authorities store.

Install the cert into your trusted root store.

edited Jul 20, 2014 at 3:39

answered Aug 11, 2011 at 12:59

Preet Sangha

65.7k20 gold badges152 silver badges227 bronze badges

1 Comment

Disappointed Over a year ago

actually a haven't it. SQL Server will generate a self-signed certificate when the instance is started

Collectives™ on Stack Overflow

Problem during SSL connection to SQL Server

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related