Nick Grattan's Blog

About Microsoft SharePoint, .NET, Natural Language Processing and Machine Learning

Encyrpting SQL Server Connections

leave a comment »

In SQL Server 2005 connections between client and server can be encrypted using SSL even if a X.509 certificate has not been installed on the server. When a certificate is not present SQL Server will automatically generate one.

By default, the certificate chain up to a root CA will be checked, and if a recognised root CA is not found, the connection will fail with the following exception message:

A connection was successfully established with the server, but then an error occurred during the login process. (provider: SSL Provider, error: 31 – Encryption(ssl/tls) handshake failed)

You can request that the certificate check is not made by using the TrustServerCertificate connection string parameter. So, the full connection string to specify SSL encryption without checking the certificate is:

string connectionstring =
            “Server=(local);Database=AdventureWorks; Integrated Security=SSPI;Encrypt=true;TrustServerCertificate=true”;

It’s best to install a recognised certificate, but this approach is better than sending data in plain text across insecure networks.

Advertisements

Written by Nick Grattan

August 8, 2007 at 8:41 pm

Posted in Uncategorized

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: