By: Rajendra Gupta | Updated: 2024-05-27 | Comments (1) | Related: > SQL Server Management Studio
Problem
Microsoft released a major version upgrade to SQL Server Management Studio (SSMS), version 20. What are the changes, and how does it impact users working with SQL Server? Let's explore this in this article.
Solution
SSMS v20 was released on March 19, 2024. It is a standalone client tool for Windows and Linux SQL Server instances that significantly enhances user experience and security for database professionals.
You can download the SSMS v20 General Availability release (GA) from Microsoft Docs.
Click on the downloaded SSMS-Setup-ENU.exe to install SSMS. The installation looks like previous SSMS versions except for one change. Previously, Azure Data Studio (ADS) was also installed with SSMS 18 and 19 installations. There was no GUI option to uncheck the ADS installation. The only option was to install SSMS with the command line using the flag DoNotInstallAzureDataStudio=1.
SSMS-Setup-ENU.exe /Passive DoNotInstallAzureDataStudio=1
However, many people did not want to install ADS or SSMS. Therefore, Microsoft removed the ADS installation with SSMS v20. You need to download and install Azure Data Studio separately if you require it.
Note: SSMS 20 is a separate installation from SSMS 18 or 19. You can use both SSMS 19 and 20 on the same server.
Let's talk about the SSMS v20 changes.
Version/Name Change
As shown below, previous versions are named Microsoft SQL Server Management Studio <version>. However, the latest GA has been renamed SQL Server Management Studio 20.
SSMS 20 components and versions are listed below.
Security and Connection Changes
Look at the login and connection properties tabs below comparing SSMS 19 and 20.
Changes from SSMS 19 to SSMS 20
- SSMS 19 and earlier versions have two checkboxes in the connection properties: Encrypt Connection and Trust Server Certificate.
- SSMS 20 removed the encryption options from the Connection Properties tab and added a Connection Security section on the Login tab.
- Additionally, SSMS 20 renames Encrypt Connection to Encryption. SSMS 20 includes three encryption values: Optional, Mandatory, and Strict.
- The Trust Server Certificate has also been moved from the Connection Properties tab to the Logins tab.
Encryption Options in SSMS 20
The SSMS 20 Encryption dropdown has three options:
- Optional
- Mandatory
- Strict
Optional
By default, the SQL Server connection is not encrypted if you do not check the Encrypt Connection in SSMS 19 or earlier. SSMS 20 uses the value Optional for non-encrypted connections.
Mandatory
Mandatory is like checking the Encrypt Connection box in SSMS v19 or earlier. It is the default value for any connections. Therefore, if you do not have a valid, trusted certificate on your SQL instance, you get the following error:
With the Optional and Mandatory Encryption, you can put a check on the Trust Server Certificate. However, it disables the text box for placing the hostname in the certificate. If you have a self-signed certificate, you must check the Trust Server Certificate for the mandatory Encryption.
Strict
Strict Encryption is a new feature for SQL Server 2022 onwards. You must configure a TLS certificate (signed by the trusted root authority) for this Encryption in the SQL Server instance. In Strict mode, you do not have the option to use a Trust Server Certificate. The Trust Server Certificate option is disabled to prevent man-in-the-middle attacks. The supplied certificate must pass the certificate validation for a successful connection.
By default, SQL Server matches the Common Name (CN) or the Subject Alternate Name (SAN) in the certificate with the server name we specify in the SSMS connection. It might not work for a DNS alias. Therefore, you can provide a hostname (certificate servername) in the SSMS connection - Host name in certificatesection.
The following figure shows how you can put the hostname (certificate ServerName) in the SSMS connection security section.
TDS Versions Used
SQL Server uses the following TDS versions with different encryption methods:
- TDS 8.0 is used for Strict encryption to communicate between the client and the server.
- For Mandatory Encryption, it uses TDS 7.x with the valid server certificate installed.
- For Optional, it uses TDS 7.x only if it is required by the SQL Server (Force Encryption = ON in SQL Server Configuration Manager).
Note: You must use Strict Encryption with Azure SQL Database and Azure SQL Managed instance as they support encrypted connections configured with trusted certificates.
Import Settings and Save Connections
Once you launch SSMS 20 for the first time, you can import SSMS user settings and save connections from the MRU list from SSMS 19 and 18.
Note: If you import the existing SSMS 18 and 19 connections into SSMS 20, they might not connect due to the changes in the encryption (default=mandatory).
However, you can add a Trust Server Certificate for a self-signed certificate to all the imported connections using the following check box - Remember my selection from all connections imported from older versions.
Note: Due to improved security measures, this Trust Server Certificate setting cannot be added as a default option for all new SSMS connections.
You can also use SSMS options > SQL Server Object Explorer > Commands and set the connection security from the default Prompt to Always in the Trust Server Certificate for the imported connections, as shown below.
Default Prompt Value:
Modified to Always Value:
Encryption Method
If you hover your mouse over a query window connection in the status bar, it also shows the encryption method. As shown below, I use mandatory Encryption for the connection below.
SSMS 20 uses the Transport Layer Security (TLS) 1.3 with Tabular Data Stream TDS 8.0.
SSMS 20 connection authentication includes Microsoft Entra.
SSMS Roadmap
Microsoft announced their SSMS future roadmap. Here are a few valuable pointers:
- Microsoft is evaluating AI for SSMS and exploring the SSMS Co-Pilot. You can give suggestions at https://aka.ms/ssms-copilot-feedback.
- SSMS will be migrating to Visual Studio 2022 shell for the SSMS 21.
- Currently, SSMS uses different setups for different languages. SSMS 21 is integrating all of them into a single installer.
- Azure Data Studio is famous for extensions that enhance the ADS features as per user requirements. With the SSMS move to Visual Studio shell, extensions can also play a significant role in SSMS.
Next Steps
- Explore the SSMS 20 changes for encrypt connections and be familiar with them before using them daily.
- Go through the SSMS 20 release notes for detailed information.
- Read about TDS 8.0 and how it uses Strict Encryption.
About the author
Rajendra Gupta is a Consultant DBA with 14+ years of extensive experience in database administration including large critical OLAP, OLTP, Reporting and SharePoint databases.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2024-05-27
