If you didn’t know, since SQL Server 2000, we’ve been able to secure communication channels with support for Securing SQL Server TLS. It’s encouraging to see that Microsoft has kept up with the security of the product even as cryptographic techniques have grown more complex over time.
Microsoft announced support for TLS 1.2 encryption for SQL Server 2008, 2008 R2, 2012, and 2014 in January 2016. Since then, support for SQL Server 2016 and 2017 has been added to that announcement. Prior to the recent round of vulnerabilities against SSL, Microsoft solely supported basic SSL encryption in SQL Server; however, they now advise switching to TLS 1.2. To answer your question, TLS and SSL are essentially the same thing; the latter is just shiny and new albeit more secure.
You can encrypt connections between SQL Server and calling a client using TLS. An initial handshake is conducted to negotiate the cipher suite from which future communication should be conducted when a client requests an encrypted connection to a SQL Server that is configured for TLS. After an agreement is reached, SQL Server delivers the client its TLS certificate, which the client needs to verify and trust using a copy of the Certification Authority (CA) certificate. Lastly, a secure connection is formed if the TLS certificate is trusted and satisfies further requirements.
DLH.io attempts to force TLS but will not fake a TLS connection if one is not available. You may be prompted to improve the security of your SQL Server environment or we recommend using an SSH SSL client which might be slightly slower due to your network traffic with this extra SSH client server between DLH.io and your SQL Server database.
Performance is always a consideration with extra layers of security. This is because creating a secure connection requires an additional network round trip, and the Net-Libraries on the client and server manage the overhead of encryption and decryption. In my experience, the overhead varies depending on the volume of data being transported and how noisy your application is, therefore testing is always advised to compare throughput times.
About Certificates
You’ll need a certificate to get started, which you can receive from an internal CA or buy from a third party. Self-signed certificates are excellent for testing purposes, but because of their vulnerability to “man in the middle” attacks, they are not advised for use in production applications. Even if a certificate is not being used for channel encryption, since SQL Server 2005, a self-signed certificate is automatically issued at startup to encrypt the contents of the login packets.
The SHA256 algorithm is now used to generate the self-signed certificate starting with SQL Server 2017. Even though this is more secure than its predecessor, when encrypting connections, it is still advised to request certificates from a reliable CA.
For SQL Server to load the certificate effectively, it needs to fulfill specific conditions. There are a few things to keep in mind. First, the fully qualified domain name (FQDN) or host name of the SQL Server must match the Common Name (CN) specified in the Subject attribute of the certificate. The Subject Alternate Name (SAN) property’s DNS name field functions in the same way. You can select different domain names or servers to use while connecting to SQL Server by using the SAN parameter. For instance, if XXX-SQL01 is the host name for my SQL Server, then:
- The value of XXX-SQL01.contoso.com should be entered in the Subject property CN box.
- The XXX-SQL01.contoso.com and XXX-SQL01 should be included in the SAN property DNS field.
The SQL Server service account needs read rights to the private key in order to load the certificate for encryption after it has been imported into the local machine certificate store (on the SQL Server). The SQL Server service won’t start and the certificate won’t load properly without this read permission. That information will also be stated in an error that is captured in the SQL Server Error Log. The procedure is a little more involved if you wish to encrypt connections to a SQL Server failover cluster instance.
On the active cluster node, you should request a certificate and store it in the local machine certificate store. However, this time the clustered SQL Server Virtual Network Name (VNN) FQDN should be contained in the CN column. For instance, if CONTOSO-SQLFCI is my SQL Server VNN, then:
- It is necessary for the Subject property CN field to have CONTOSO-SQLFCI.contoso.com.
- It is necessary to provide CONTOSO-SQLFCI.contoso.com and CONTOSO-SQLFCI in the SAN property DNS field.
This is where things get a little more complicated.
The certificate can be imported on other clustered nodes when it has been installed on the active node and exported (along with its private key). One noteworthy detail that is unique to clustered installations is that the certificate’s registry Thumbprint value is null This can be fixed by copying the thumbprint from the certificate, formatting it without spaces, and pasting it into the corresponding registry key:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.<Instance_Name>\MSSQLServer\SuperSocketNetLib\Certificate
For the modification to take effect, restart the previously active node after failing over the clustered instance. To update the Thumbprint in the registry, import the same certificate onto the newly active node and repeat the previous steps.
Within SQL Server 2019 (CTP 2.0). With SQL Server Configuration Manager, you can not only examine and validate certificates that are about to expire, but you can also distribute them around all of your Availability Replicas and failover cluster instance nodes. This is great news since it makes the process of configuring deployments for high availability much simpler.
After the certificate has been successfully installed and requested, SQL Server Configuration Manager must connect it to the database engine service. To select your certificate from the drop-down list on the “Certificate” tab, just expand “SQL Server Network Configuration,” right-click “Protocols for ,” select “Properties,” and then choose it.
The encryption needs to be enabled next. You can set “Force Encryption = Yes” in SQL Server Configuration Manager to impose encryption on the server, ensuring that all clients connecting to it do so. But be advised that clients that do not support encryption will not establish a connection.
You can set “Encrypt = True” on the application connection string if you only want to let particular customers request encrypted connections. Thus, the connection between that client and server will be encrypted, provided that the server has an installed valid server certificate. The connection string argument “TrustServerCertificate” may be familiar to you. By avoiding the certificate validation step, this determines if the channel is encrypted. In other words, the client does not compare the TLS certificate to the CA certificate that is being issued. Encryption on connection strings is generally best enabled with “TrustServerCertificate = False” set. Because it helps to thwart man-in-the-middle attacks, forcing the transport layer to validate the certificate chain when you don’t trust the server certificate makes the system intrinsically more secure.
Launch SQL Server Management Studio (SSMS), connect to the instance (you might need to select “Encrypt Connection” if you’re in charge of encryption on the client), and run the following query to make sure that your connections are encrypted:
SELECT encrypt_option FROM sys.dm_exec_connections WHERE session_id = @@SPID
If your setup and encryption is working correctly, TRUE will be returned.
Microsoft Azure SQL Server Azure SQL Databases offer a managed method for configuring encrypted connections, as one might anticipate. All you have to do is add the parameters “Encrypt = True” and “TrustServerCertificate = False” to the application connection string.
Some References
- https://blogs.msdn.microsoft.com/jorgepc/2008/02/19/enabling-certificate-for-ssl-on-a-sql-server-2005-clustered-installation/
- https://support.microsoft.com/en-us/help/2023869/sql-server-fails-to-start-with-error-17182-tdssniclient-initialization
- https://docs.microsoft.com/en-us/sql/sql-server/what-s-new-in-sql-server-ver15?view=sqlallproducts-allversions#security
