Configure SSL Connection Encryption in MS SQL Server

If you are using untrusted/public networks to connect clients/apps to your Microsoft SQL Server database, it is recommended to use traffic encryption. Let’s see how to configure and enable mandatory SSL/TLS traffic encryption for client connection to Microsoft SQL Server.

To enable encryption for an MS SQL Server connection, you need to a TLS/SSL certificate on your server. It may be a commercial certificate, a certificate issued by your CA, or a self-signed certificate. A TLS certificate must meet the following requirements:

• Enhanced Key Usage — Server Authentication 1.3.6.1.5.5.7.3.1 ;
• Valid expiration date;
• The certificate must be located in the user (certmgr.msc) or computer (certlm.msc) certificate store;
• The certificate must have the server name on which MSSQL is running in its Subject Name;
• Clients must trust the certificate.

You can create a self-signed certificate using PowerShell (it is recommended to use self-signed certificates in test environments only):

New-SelfSignedCertificate -DnsName srvdb01.woshub.com -CertStoreLocation cert:\LocalMachine\My

If you are going to access your MSSQL instance not only by hostname but also by other names (for example, if an Always-On High Availability Group listener with an RDS Connection Broker database is located on the server), add all available names to the Subject Alternative Name. To issue a self-signed certificate with multiple SANs valid for three years:

$curdate = Get-Date
$add3year = $curdate.AddYears(3)
$newcert=New-SelfSignedCertificate -DnsName srvdb01, srvdb01.woshub.loc,SQLRDDBCL.woshub.com -CertStoreLocation cert:\LocalMachine\My -notafter $add3year

Import the certificate to the list of trusted certificates:

$certFile = Export-Certificate -Cert $newcert -FilePath C:\certname.cer
Import-Certificate -CertStoreLocation Cert:\LocalMachine\AuthRoot -FilePath $certFile.FullName

Then bind your certificate to the installed SQL Server instance:

1. Run the SQL Server Configuration Manager, go to SQL Server Network Configuration, and open the properties of Protocols for MSSQLSERVER;
2. Enable Force Encryption;
3. Go to the Certificate tab and select the certificate you imported to the user or computer certificate store in the dropdown list;
4. Restart MS SQL to apply the changes.

To force the client to always use an encrypted connection to your MSSQL server:

• Open Server Configuration Manager on the client -> SQL Native Client Configuration -> Properties -> enable Force Protocol Encryption.
• You can also use the following connection string that makes the client use SSL/TLS to connect to the MSSQL server: Data Source=srvdb01.woshub.com;Integrated Security=False;User ID=test;Password=[Password];Encrypt=True

Cannot connect to SRVDB01.
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.) (Microsoft SQL Server, Error: -2146893019).

To make SQL Server Management Studio (SSMS) always use encryption to connect to an SQL Server database, open the connection window (Connect to Server) -> Options -> enable the Encrypt Connection option.

If “The target principal name is incorrect” error appears, make sure you are using the SQL Server name specified in the Subject Name or the certificate SAN.

You can connect to your SQL Server using the SqlServer PowerShell module and make sure if your SQL Server supports connection encryption:

Invoke-Sqlcmd -ServerInstance "srvdb01" -Query "SELECT DISTINCT encrypt_option FROM sys.dm_exec_connections WHERE session_id = @@SPID"

In our example, the SQL Server supports encrypted connections (encrypt_option=TRUE).

Azure SQL databases support encrypted connections only. Even if the Encrypt connection option in SSMS is not checked, the connection to Azure SQL will be encrypted (see the article Database Security in Azure SQL).