Strongly encrypted SQL Server Management Studio 20 connection to Microsoft SQL Server 2022

This article is about what's new in SQL Server Management Studio 20 (SSMS), and is the second in a series of articles on this topic. The first article can be read here: Upcoming changes for SQL Server Management Studio (SSMS) – Part 1.The final article in the series is: Upcoming changes for SQL Server Management Studio (SSMS) – Part 3. SSMS 20 may surprise you immediately after installation, when you suddenly discover that by default SSMS is supposed to encrypt the connection to the database server. This article is aimed at helping SSMS users overcome this problem and achieve better connection security using the new encryption mechanisms in SQL Server 2022.

What is TDS, TLS and encryption

The Tabular Data Stream (TDS) protocol is an application layer protocol used by clients to connect to SQL Server.

SQL Server uses Transport Layer Security (TLS) to encrypt data that travels over the network between the SQL Server instance and the client application. TLS has different versions, each with different levels of security and performance. The latest version is TLS 1.3 it was published in 2018.

Encryption is the process of encoding data to protect it from unauthorized access. Encryption can be applied to static data (stored on disk) or in transit (transmitted over a network).

TDS 8.0 was introduced to support mandatory encryption on SQL Server connections, and a TLS handshake precedes any TDS message. TDS 8.0 is compatible with TLS 1.3, as well as TLS 1.2 and earlier.

How encryption is provided in SSMS 20

SSMS 20 is the first version of SSMS to support strong encryption and TLS 1.3. You can enable it by upgrading to the Microsoft.Data.SqlClient (MDS) 5.1.4 data access library. MDS, which was used by SSMS 19 and later, as well as other SQL Server tools.

Strong encryption is a new encryption provisioning option introduced in MDS 5. To use TLS 1.3, the client and server must support and have a trusted certificate installed. Strong encryption is now the most secure encryption option.

Enable encryption for connections to SQL Server

To support encryption, you must enable mandatory encryption or strong encryption on your server. How this is done will be described below. Using the highest level of encryption supported by both client and server provides the highest level of security. For SQL Server 2022, you can use strong encryption, and for SQL Server 2019 and earlier, the highest level of encryption is mandatory encryption.

Connection from SSMS 20

You can check out the specially created manual page: Connect with SQL Server Management Studio, which is designed to help you navigate the settings of the new SSMS 20 connection dialog. These changes will affect everyone who will connect to SQL Server. We strongly recommend that you understand what has changed and how it will affect the connection process, before You will install SSMS 20 for yourself.

If you will be importing connection data from a previous version of SSMS, we strongly recommend that you first remember the parameters set there. For new connections, you need to pay attention to the properties that were moved to the first tab of the connection dialog and find out how they relate to the settings of the connected SQL Server.

Note: encryption is now required by default. When using the default value of Mandatory, any connection to an instance of SQL Server that does not have a trusted certificate installed will generate an error “The certificate received from the remote server was issued by an untrusted certificate authority”:

thumbnail image 1 captioned Screenshot of error from SSMS when connecting with Mandatory encryption but no trusted certificate — *Screenshot of SSMS error when connecting with required encryption, but without a trusted certificate*

More information can be found in the article Error “The certificate received from the remote server was issued by an untrusted certificate authority” when connecting to SQL Server.

If you need to use certificates to encrypt the connection, the solution can be found in the same article.

Features that do not support strong encryption

Some SQL Server features do not support strong encryption. The current list can be found at TDS 8.0-SQL Server. Unsupported SSMS-specific scenarios and known issues are described in the following article: SQL Server Management Studio (SSMS) Release Notes.

At the time of publication, the list of features that do not support forced strong encryption is as follows: