SQL Server Management Studio (SSMS) 19.1 and Recent Changes, Part 1

[Guest erinstellato]

Toward the end of May the SQL Tools team released SQL Server Management Studio (SSMS) 19.1, which had many bug fixes as well as new functionality and a couple noteworthy changes. In addition, as users have been upgrading to SSMS 19.x, we’ve heard reports of confusion and misunderstanding related to selected settings. In this series of posts we’ll clarify the impact of changes between 18.12.1 and 19.x, as well as discuss what’s new in 19.1.

 

 

 

Dependency Changes

 

 

SQL Server Management Studio (SSMS), like many other applications, has dependencies on client libraries and drivers. For SSMS, there are three, of which you may or may not be aware:

 

 

 

• SQL Management Objects (SMO)
  
• DacFx
  
• Microsoft.Data.Sqlclient (MDS)
  

 

The versions of any dependency can get bumped as part of release. For example, between SSMS 18.12.1 and SSMS 19.0 we moved from DacFx 150 to DacFx 160. In the SSMS 19 release we also migrated from System.Data.Sqlclient (SDS) 3.x to Microsoft.Data.Sqlclient (MDS) 3.x. As previously shared, MDS is the new data access driver for SQL Server, and we were able to pick up the change in SSMS 19.

 

 

 

MDS 3.x changes the way that connections for SQL Server are handled, which users have noticed as they have updated to SSMS 19 and then connect to SQL Server 2022 and earlier. Most notably, in MDS 3.x there is validation of certificates, which previously did not occur in SDS 3.x. Let’s walk through the how this is configured and what you may see in SSMS 19.x.

 

 

 

SQL Server Configuration

 

 

Within Configuration Manager, go to SQL Server Network Configuration, then Protocols, and then right-click and select Properties. The Force Encryption option is available, and the default value is No:

 

 

 

Screenshot of Protocol Properties for a SQL Server

 

 

 

This option exists in earlier versions of SQL Server, not only SQL Server 2022. If Force Encryption is set to Yes and a verifiable certificate is not installed, in SSMS 18.x with SDS 3.x the driver does not validate the certificate. In SSMS 19.x, with MDS 3.x, it does.

 

 

 

SSMS Configuration

 

 

As a result, those users who start to use SSMS 19.x with this configuration will notice the need to select the Trust server certificate option after selecting Options>> from the main Connection dialog:

 

 

 

Screenshot of options pane in SSMS Connection dialog

 

 

 

In SSMS 19 with Force Encryption set to True, it is necessary to have the option Trust Server Certificate selected. Otherwise, you can run into this error:

 

 

 

Screenshot of connection error with Force Encryption set to True, without Trust Server Certificate select in SSMS 19

 

 

 

Connection errors can also occur if your certificate is not properly configured, and more information can be found in Configure SQL Server Database Engine for encrypting connections. One lesson from the field worth sharing: when creating a certificate, use the option -DnsName to specify the DNS name(s) for the server. The DNS name will be used for certificate validation. You may also need to add the NETBIOS name of the server to the list of Subject Alternative Names. Example commands for creating a self-signed certificate with additional Subject Alternative Names can be found in the New Self Signed Certificate documentation.

 

 

 

Do not confuse this new behavior related to Force Encryption and certificates with the Strict Encryption option that was introduced in SQL Server 2022 with TDS 8.0 and TLS 1.3. The Strict Encryption option for connections is not available in SSMS 19, though it is available in Azure Data Studio. SSMS 20 will include a migration to MDS 5.x, which is the version of the Microsoft.Data.Sqlclient driver that supports Strict Encryption and used by Azure Data Studio.

 

 

 

ADAL to MSAL

 

 

Migrating to Microsoft.Data.Sqlclient 3.x means we also migrated from the Azure Active Directory Authentication Library (ADAL) to the Microsoft Authentication Library (MSAL). Support and development for ADAL ends in June 2023, and no new features have been added since June 30, 2020. MDS uses MSAL.NET and is actively updated with MSAL library updates. MSAL was developed because the Microsoft identity platform endpoint changed significantly and MSAL leverages all benefits of the updated identity platform endpoint.

 

 

 

Please note that independent of this migration to MSAL in SSMS,

anyone who uses System.Data.Sqlclient (which uses ADAL), or ADAL itself in their application should begin to move to Microsoft.Data.Sqlclient and MSAL to avoid potential security incidents

. Existing legacy applications will continue to function after ADAL’s retirement at the end of June 2023. Critical security fixes will continue to be made to ADAL and System.Data.Sqlclient when necessary; however, it is

strongly recommended

to move applications to MSAL and MDS as ADAL and SDS will no longer be actively maintained. Further, if you have any issues with ADAL or SDS that are not related to critical security fixes, the recommendation will be to move to MSAL and MDS.

 

 

 

Migrating to MSAL brings additional Azure AD options available in the Connection drop down, including:

 

• Azure Active Directory - Service Principal
  
• Azure Active Directory - Managed Identity
  
• Azure Active Directory - Default
  

 

 

 

Screenshot of Authentication drop down in SSMS 19

 

 

 

Azure Active Directory - Service Principal requires a username and password, while username is optional for Azure Active Directory - Managed Identity. For more information on the different Azure AD options, see Use Azure Active Directory authentication.

 

 

 

If you encounter issues with authentication, there are two new output options for logging available in SSMS:

 

 

 

• SQL Client
  
• Azure Active Directory Interactive authentication
  

 

 

 

The options are enabled on the General pane within Tools >> Options >> Output Window, and you can leverage this additional information when troubleshooting:

 

 

 

Screenshot of Output Window logging options

 

 

 

Once enabled, use View >> Output (or CTRL + ALT +O) to open the output window, and then select the appropriate channel to see the logging details. For users that continue to use Azure Active Directory – Password or Azure Active Directory – Integrated the only output available is from the SQL Client channel. If you encounter problems with either authentication option, it is recommended to switch to Azure Active Directory – Universal with MFA which allows for additional logging.

 

 

 

In addition, when using Azure Active Directory – Universal with MFA, the connection experience may be improved by enabling Use system default web browser and Web Account Manager. Both options are available on the Azure Cloud pane within Tools >> Options >> Azure Services:

 

 

 

Screenshot of Azure Cloud options in SSMS 19

 

 

 

In SSMS 19.1, the default value for Use system default web browser was changed to True. This only applies to new installations; anyone upgrading from an earlier version of SSMS 19 will need to change the option to True manually.

 

 

 

Summary

 

 

As Microsoft continues to prioritize security improvements across SQL, relevant changes and options will be reflected in the data tools. We have made initial steps to support security-related changes in SQL with SSMS 19, and will continue to move in that direction with subsequent releases.

 

Continue reading...