SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

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

      <connectionStrings><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

      <connectionStrings><add name=myConnectionStringconnectionString=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

The post Encrypting Connections To SQL Server Using Certificates appeared first on Centino Systems Blog.

Anthony Nocentino's Blog

Anthony Nocentino is the founder of Centino Systems. As an Enterprise Architect he works with clients to find right technology for their business, designing and deploying it, providing expertise on system performance and architecture. Creating well-designed, maintainable SQL Server and Linux based systems that enable clients to collect meaningful data that they can act upon. Anthony has a Bachelors and Masters in Computer Science and is working towards a Ph.D focusing on high performance/low latency data access algorithms on solid state disks. Anthony has a unique blend of academic and professional experience leveraged to help customers solve their hardest IT problems.

Comments

Leave a comment on the original post [www.centinosystems.com, opens in a new window]

Loading comments...