Encrypting Connections To SQL Server Using Certificates

Encrypting Connections To SQL Server Using Certificates

In this post we’re going to cover configuring a connection string in .NET applications for encrypting connections to SQL Server using certificates. The audience for this document is a developer that needs to configure encrypted connections from applications to a database server.

Encrypting connections with SQL Server using Certificates consists of two parts:

  • An appropriately configured connection string
  • A server certificate installed on the Database Engine (not covered in this post)

Configuring a Connection String

To configure a .NET connection string you will need to set the following parameters

  • Server – the fully qualified domain name (FQDN) of the SQL Server. The name here will need to exactly match the server common name or a subject alternative name configured in the certificate.
  • Database – the database context used for this connection
  • uid – the username of this connection
  • password – the password for the user
  • Encrypt – set to ‘yes’ to encrypt or ‘no’ or remove to disable encryption 
Optional
  • TrustServerCertificate – will bypass validating the certificate. This is useful if the certificate is untrusted, the common name or subject alternative name do not match what is in the certificate or the certificate is expired. This is useful for testing and troubleshooting. Enabling this in production environments is strongly discouraged as the destination certificate is not validated.

****Configuring a Connection String On a Single Instance

When connecting to a single instance, a valid certificate must be installed on the instance. The server parameter in the connection string needs to be configured as the subject/common name in the certificate and the server configured in the connection string must match this name. In figure 1, the subject is sql14-a.lab.centinosystem.com the connection string server parameter should match this name.

Certificate

Figure 1 – Certificate with Subject/Common Name

****Configuring a Connection String On Availability Groups

A valid certificate must be installed on all servers/replicas in the Availability Group. The subject/common name in the certificate should match the local server. The subject alternative names in the certificate should match the availability group listener DNS name. The server parameter of the connection string will use this name. In figure 2, the AG listener is ag1lst.lab.centinosystems.com, the connection string server parameter should match this name.

Subject Alternative Name

Figure 2 – Certificate with Subject Alternative Names

****Configuring a Connection String With Aliases

Often application connection strings are configured using an alias (DNS CNAME), rather than the actual host name (A record) of the SQL Server. Upon the connection’s DNS request, the DNS server responds to the CNAME request for the alias with the A record data for the actual server.  This means the certificates common name or subject alternative name do not have to include the alias (DNS CNAME). This applies to single instances and Availability Groups.

Example Connection Strings

  1. Example connection to a single instance when the server has a valid certificate

    «span style=“color: #a31516; -webkit-text-stroke-color: #a31516;">connectionStrings>«span style=“color: #a31516; -webkit-text-stroke-color: #a31516;">add name=“myConnectionString” connectionString=“server=sql14-a.lab.centinosystems.com;database=TestDB1;uid=user1;password=s3cur31y;Encrypt=yes”/></connectionStrings>

  2. Example connection to an availability group listener when each server has a valid certificate with the appropriate subject alternative name

    «/span>connectionStrings>«span style=“color: #a31516; -webkit-text-stroke-color: #a31516;">add name=“myConnectionString” connectionString=“server=ag1lst.lab.centinosystems.com;database=TestDB1;uid=user1;password=s3cur31y;Encrypt=yes”/></connectionStrings>

Errors

Here are a few errors that you may encounter when connecting to SQL Server with an encrypted connection. Basically when validation fails there will be an exception thrown with information similar to these examples.

When opening a connection string asking for encryption, but an untrusted certificate

System.Data.SqlClient.SqlException (0x80131904): A connection was successfully established with the server, but then an error occurred during the login process. (provider: SSL Provider, error: 0 – The certificate chain was issued by an authority that is not trusted.) —> System.ComponentModel.Win32Exception (0x80004005): The certificate chain was issued by an authority that is not trusted

When opening a connection string, but the certificate is expired

System.Data.SqlClient.SqlException (0x80131904): A connection was successfully established with the server, but then an error occurred during the login process. (provider: SSL Provider, error: 0 – The received certificate has expired.) —> System.ComponentModel.Win32Exception (0x80004005): The received certificate has expired

References:

Connection String Syntax – https://msdn.microsoft.com/en-us/library/ms254500(v=vs.110).aspx

Enable Encrypted Connections to the Database Engine – https://msdn.microsoft.com/en-us/library/ms191192(v=sql.110).aspx