Encyrpting SQL Server Connections
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.