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 ?

Answer 1

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

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