Enhancements to SQL Server Network Connection Security Through Breaking Changes

With the introduction of SQL Server 2022, Microsoft began a new initiative known as secure by default. This initiative is in response to vulnerabilities in the Tabular Data Stream (TDS) protocol – the protocol used for data communication between a client and SQL Server – discovered by Summit Security Group which allowed a man-in-the-middle attack. As more SQL Server workloads are moved to public clouds like Azure, connections over the Internet will be become more common. Microsoft has therefore taken steps to secure the network connection between the client and SQL Server

Changes to implement secure by default revolve around encryption. Use of encryption for the connection has long been optional and the default was not to use encryption. Secure by default now requires client drivers to use encryption unless explicitly disabled, and this is a breaking change that can cause connections using default settings to fail. It’s not the use of encryption itself that is the problem. Rather it’s because the certificate used by SQL Server to encrypt the connection is validated to ensure it’s a trusted certificate. A trusted certificate is one that is issued by a trusted certificate authority. When SQL Server uses a self-signed certificate – which, by definition, is not a trusted certificate – the validation fails and the connection is not allowed.

Certificate validation is not new. It’s always been done when encryption was used. But before secure by default, encryption was used only if enabled by the connection options. Now that the default is to require encryption, existing code and applications must change the connection settings to trust the server certificate when SQL Server uses a self-signed certificate.

Most SQL Server instances use a self-signed certificate. In fact, until a trusted certificate is installed, SQL Server always uses a self-signed certificate. As TDS encrypts the setup and authentication phases, SQL Server must have a certificate. To install a certificate – either trusted or self-signed – you must first connect to SQL Server. This creates a problem since even if the connection will not use encryption, the setup and authentication phases do. (Validation of the certificate is not done during these phases. It’s only done if encryption is used for the data exchange phase.) When another certificate is not available, SQL Server uses a fallback certificate. This is a self-signed certificate dynamically created when SQL Server starts and stored in memory. It exists only for as long as SQL Server is running and is recreated each time SQL Server is started. It’s not uncommon for a SQL Server instance to always use the fallback certificate.

Changes for secure by default only affect newer versions of the client drivers, those that were updated for SQL Server 2022. Code and applications using older client drivers will continue to work without problems. But if the client driver is updated to one that implements secure by default, the connection settings must often be changed for the connection to SQL Server to succeed.

There’s a lot of details about secure by default, the bits and pieces of which are scattered about the Internet. Since I’m involved with upgrades to SQL Server 2022 in my job with Aptum Technologies, I decided to collect this information, as much as possible, into one white paper that I can provide to my clients. It covers the reasons behind Microsoft’s secure by default initiative, provides a brief technical overview of the Tabular Data Stream protocol, explains the changes to client drivers and PowerShell modules for SQL Server, and includes links to references with additional information. It’s available as a PDF file (link below) and provides what you need to know to deal with the breaking changes introduced for secure by default.

White Paper: SQL Server Secure By Default