SQL encryption, certificate defaults and dbatools

SQL encryption, certificate defaults and dbatools

I have been taking advantage of my work break to develop new skills. This post is a reminder to myself about changes in SQL Server connection encryption and how it affects the PowerShell module dbatools.

I have started reading the book Learn dbatools in a Month of Lunches by Chrissy LaMaire, Rob Sewell, Jess Pomfret, and Cláudio Silva. To prepare for working through the examples, I upgraded my dbatools module to the latest version (2.0.4) and removed all of the old versions.

Right from the start I was unable to connect to my local test server instance. I quickly discovered that it was because of changes to encryption and certificate defaults. As my computer is not on a network with access to a certificate server, and I don’t want to deal with a third-party certificate authority, I chose to temporarily revert to the less secure defaults by running the following command for my session:

Set-DbatoolsInsecureConnection -SessionOnly

It’s imperative to note that employing an insecure connection in this manner exposes sensitive information, including passwords, in plain text. By incorporating the parameter -SessionOnly I ensured the secure defaults would be re-established once I closed the PowerShell window.

It’s essential that your working environment is appropriately configured to align with the new secure defaults, rather than resorting to less secure older defaults, which I’ve temporarily employed for local testing. A valuable resource is the blog post linked from the dbatools Slack channel, titled New Encryption and Certificate Defaults in Microsoft’s SQL Server Connection Provider. This post outlines the steps for securely configuring new connections to employ encryption via a certificate.

In this article, I’ve explained the process of overriding the new secure defaults for SQL Server connection encryption, particularly suited for local testing and learning scenarios in the absence of a trusted instance certificate.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.