The second post in our series about SQL Server Management Studio (SSMS) coincides with the SSMS 20 Preview 1 release – now available! However, before you download and install the Preview release to check out the updated SSMS 20 connection dialog, we recommend you read (or review) the first post to familiarize yourself with the changes.
In addition, if you are not the person who manages your SQL Server, we highly recommend talking to your SQL Server administrator to verify what options you should select when connecting, based on whether your installation is configured to support encryption.
If you are the SQL Server administrator, and you’ve read the first post, you might be all set. But if you are interested in additional details of what’s new as it relates to the SSMS 20 connection dialog, read on. If you’re not, then please review the release notes for SSMS 20 Preview 1 before you download and install it.
As a reminder…if you encounter issues navigating the connection changes in SSMS 20, you can temporarily workaround them with SSMS 19.x installed side-by-side.
Terminology review - 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 is transmitted across a network between an instance of SQL Server and a client application. TLS has different versions, each with different levels of security and performance. The latest version is TLS 1.3, which was published in 2018.
Encryption is a process of converting data into an unreadable, encoded format to protect it from unauthorized access. Encryption can be applied to data at rest (stored on disk) or in transit (transferred over a network).
TDS 8.0 was introduced to support mandatory encryption when using SQL Server, and the TLS handshake precedes any TDS message. TDS 8.0 is compatible with TLS 1.3, as well as TLS 1.2 and earlier.
Know before you go: Strict encryption in SSMS 20
SSMS 20 is the first major version of SSMS that supports Strict encryption and TLS 1.3, thanks to the migration to Microsoft.Data.SqlClient (MDS) 5.1.4. MDS is the data access library used by SSMS 19 and higher, as well as other SQL Server tools.
Strict is a new option for the encryption property, introduced in MDS 5, and strict encryption requires a trusted server certificate for encrypted connections, and can use of TLS 1.3. To use TLS 1.3, the client and the server must both support TLS 1.3 and have a trusted certificate installed. Strict encryption is the most secure option for encryption in transit and is recommended for connections to Azure SQL Database and Azure SQL Managed Instance, which both support TDS 8.0 and are configured with trusted certificates. In addition, with SSMS 20, Federal Risk and Authorization Management Program (FedRamp) customers have the option of end-to-end encryption support for TLS 1.3.
Enabling encryption for SQL Server
Support for encryption typically starts with enabling Force Encryption or Force Strict Encryption on the server. For more information, see Configure SQL Server Encryption.
Using the highest level of encryption that is supported by both the client and the server provides the highest level of security. For Azure SQL Database and Azure SQL Managed Instance, that is Strict encryption. For SQL Server 2022 you can use Strict encryption if you have a trusted certificate installed. For SQL Server 2019 and lower, Mandatory encryption provides the highest level of encryption.
Connecting from SSMS
We’ve provided a new Learn page, Connect with SQL Server Management Studio, to help folks navigate the changes in SSMS 20 connection dialog. These changes will affect any user connecting to SQL Server. Again, we strongly recommend you understand these changes and their impact before end users install SSMS 20 and try to connect.
If you import connections from a previous version of SSMS, we recommend that you review the options selected before connecting.
New connections require reviewing the properties which have moved to the Login page of the dialog, and understanding how your SQL Server is configured.
Take note: the default value for Encryption is now Mandatory. With the default value of Mandatory, any connection to a SQL Server installation that does not have a trusted certificate installed generates the error:
Screenshot of error from SSMS when connecting with Mandatory encryption but no trusted certificate
For more information, see "The certificate received from the remote server was issued by an untrusted certificate authority" error when you connect to SQL Server.
Features that don't support strict encryption
There are some features of SQL Server that do not support strict encryption. The current list can be found in TDS 8.0 - SQL Server. Unsupported scenarios specific to SSMS are documented in the release notes.
What's next?
For the SQL and SSMS enthusiasts out there, we've heard you're curious about the upcoming changes in SSMS. While SSMS 20 brings some important security features, we're already planning for the next major release: SSMS 21. We will cover the roadmap in the next post in this series.
As a friendly reminder, we regularly review items on the SQL Feedback site. If you spot something you love, give it an upvote and add a comment. Comments help us understand your scenario, and how the change or feature solves it.
If you don't find your desired functionality after searching the site, add your request! Tell us how that change or feature would be useful, and how it solves your problem.
Votes and comments are our currency. Bugs get priority, but beyond that, we look at your requests and consider things like...Does it improve user efficiency or solve a workflow problem? Will it benefit the broader SSMS community?
"But wait," you say, "what about dark mode and the debugger?" Rest assured, we will discuss both, and more, in our third post. Stay tuned!
In the meantime, we encourage you explore the SSMS 20 release notes and the updated documentation. We've focused on the connection dialog for this release, and we hope you'll embrace these changes as they pave the way for an updated SSMS and continued investment in its future.
Microsoft
