Adventures in System Administration

Category: Microsoft SQL Server

Articles dealing with Microsoft SQL Server.

SQL Server – Secure By Default

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.

Loading SQL Server PowerShell Module: SqlServer or SQLPS

I recently created a PowerShell script to restore SQL Server databases. A customer migration required that I restore 2800 plus databases. As the data directory was in a different location on the new server, this meant using the MOVE clause in the RESTORE DATABASE command to place the data files in the correct directory. This was far too many databases to do this manually. But using PowerShell also meant using a SQL Server module.

Two modules are available for SQL Server: SQLPS and SqlServer. SQLPS is the original module. Although no longer updated, it is still included with SQL Server which Microsoft says is for backwards compatibility. It is also the module that SQL Server Agent automatically loads when a job type (or subsystem) is PowerShell. SqlServer is the new and supported module and is installed from the PowerShell gallery. This is the module Microsoft recommends you use for all new development.

Copyright © 2025 Charles Rutledge

Powered by WordPress & Theme by Anders Norén