New Encryption and Certificate Defaults in Microsoft's SQL Server Connection Provider

Mar 5, 2023 · 5 min read  ·
Share on:

Note: This blog post is going to borrow a bit from Microsoft's official documentation in "Connect with Azure Data Studio" and from our book, Learn dbatools in a Month of Lunches. It also addresses dbatools 2.0, which is currently in pre-release.

Microsoft recently made some changes to the default encryption settings of the SQL Server connection provider. This change, which is more secure by default, currently impacts dbatools 2.0, Azure Data Studio and I imagine it'll impact future versions of SQL Server Management Studio. It'll actually prevent you from connecting entirely if your SQL Server isn't properly setup for connecting over TLS/SSL.

This is what Azure Data Studio has to say about it when you open up newer versions

Azure Data Studio 1.40 and later includes an important change to the Encrypt property, which is now enabled (set to True) by default for MSSQL provider connections, and SQL Server must be configured with TLS certificates signed by a trusted root certificate authority.

And this is what dbatools 2.0 has to say about it (complete with some dot matrix printer ASCII art)

Historically, and I was suprised by this, most apps including SSMS have defaulted to connecting over an unencrypted channel and it was set to Trust server certificate by default.

Now about year or two ago, Microsoft set Trust server certificate to False by default in newer SQL connection libraries. We started using the new defaults in dbatools 1.1 and it tripped up a lot of people. In the most recently released libraries, Trust server certificate continues to be set to False AND Encrypt is now set to True by default.

So, to be clear, here's what new connection strings will include by default

  • Encrypt=True (aka Mandatory)
  • Trust Server Certificate=False

If your environment isn't setup to support encrypted connections or your certificate isn't setup right, you may run into a variety of error messages including:

  • The target principal name is incorrect
  • An existing connection was forcibly closed by the remote host
  • The certificate chain was issued by an authority that is not trusted
  • The instance of SQL Server you attempted to connect to does not support encryption
  • The remote certificate was rejected by the provided RemoteCertificateValidationCallback

How to fix these errors

In the event that you receive one of the above errors, don't fret. You have two options:

  • Setup SQL Server connection encryption in SQL Server Configuration Manager or dbatools (recommended)
  • Revert to the previous, less secure defaults

If you're tempted to revert to the less secure defaults, you should ask your security team to sniff the SQL Server's traffic and tell you what they can see. I was so shocked the first time I saw it -- an app role password was right there, staring me in the face. Ever since, I always encrypt connections. And surprisingly, I can't recall running into any application that had issues or even needed to change its connection string.

Setting up SQL Server connection encryption

If you'd like to setup the whole thing using a GUI, check out SQLShack's article, "How to set and use encrypted SQL Server connections".

If you'd like to use dbatools, our book covers this in Chapter 24 "Security and Encryption", but here's the code.

 1# Does your AD account have permission to create certificates? Try New-DbaComputerCertificate
 2New-DbaComputerCertificate -ComputerName sql1 -Dns sql1.ad.local, sql1
 3
 4# If you don't, you can create a Certificate Signing Request that'll work
 5New-DbaComputerCertificateSigningRequest -ComputerName sql1 -Dns sql1.ad.local, sql1
 6
 7# Unsure if any candidates already exist? Check certs that can be used for encryption        
 8Get-DbaComputerCertificate -ComputerName sql1
 9
10# Already have a cert? Use it!
11Set-DbaNetworkCertificate -SqlInstance SqlInstance -Thumbprint 1245FB1ACBCA44D3EE9640F81B6BA14A92F3D6E2
12
13# Or get a visual and use some piping        
14Get-DbaComputerCertificate | Out-GridView -PassThru | Set-DbaNetworkCertificate -SqlInstance sql1
15
16# Once you've got your certs setup, Force Network Encryption        
17Enable-DbaForceNetworkEncryption -SqlInstance sql1

Now you're set and you don't even have to worry about future changes made by Microsoft. But I do recommend setting up a certifiate expiration test and have it alert you if you're about to need to do some updates.

 1# Then setup a check that alerts you if any certs are about to expire
 2Test-DbaComputerCertificateExpiration -ComputerName sql1
 3
 4# But if you'd like to see some output, set a higher therashold
 5Test-DbaComputerCertificateExpiration -ComputerName sqlcs -Threshold 1000
 6
 7ComputerName      : SQLCS
 8Store             : LocalMachine
 9Folder            : My
10Name              : SQL Server
11DnsNameList       : {sqlcs, sqlcs.ad.local}
12Thumbprint        : F8094891311522237B4B5D0C6EC6C97F423FD961
13NotBefore         : 10/11/2022 8:47:37 PM
14NotAfter          : 10/10/2024 8:47:37 PM
15Subject           : CN=sqlcs.ad.local
16Issuer            : CN=ad-DC1-CA, DC=ad, DC=local
17Algorithm         : sha256RSA
18ExpiredOrExpiring : True
19Note              : This certificate expires in 584 days

Reverting to previous defaults

If you'd like to go back to the way it used to be, I don't know how other apps do it but here's how to do it in dbatools:

1# Set defaults just for this session
2Set-DbatoolsConfig -FullName sql.connection.trustcert -Value $true
3Set-DbatoolsConfig -FullName sql.connection.encrypt -Value $false
4
5# Set these defaults for all future sessions on this machine
6Set-DbatoolsConfig -FullName sql.connection.trustcert -Value $true -Register
7Set-DbatoolsConfig -FullName sql.connection.encrypt -Value $false -Register

Of you can use the new command available in dbatools 2.0, Set-DbatoolsInsecureConnection:

1# Set these defaults for all future sessions on this machine
2Set-DbatoolsInsecureConnection
3
4# Set defaults just for this session
5Set-DbatoolsInsecureConnection -SessionOnly

This will have to happen on every computer where you run dbatools. If you have a CI/CD pipeline, this will have to be added to your code. That's what we did for our own pipeline.

1      - name: Set encryption values
2        run: |
3          Set-DbatoolsConfig -FullName sql.connection.trustcert -Value $true -Register
4          Set-DbatoolsConfig -FullName sql.connection.encrypt -Value Optional -Register

Checking to see what's connected

If you'd like to see which connections are encrypted, you can use the super useful Get-DbaProcess command.

1Get-DbaProcess -SqlInstance sqlcs |
2Where EncryptOption -ne $null |
3Select Login, Program, ClientNetAddress, NetTransport, EncryptOption

If this doesn't work for you, first, let us know in this issue and second, please check your Kerberos settings using Test-DbaSpn and set them as necessary. The new SMO libraries seem to be a bit more strict about Kerberos as well.

Want to try dbatools 2.0?

If you'd like to give it whirl, you can install dbatools from the PowerShell Gallery.

1Install-Module dbatools

There are a bunch of changes and bug fixes and it should even load faster for you. We're really excited about this release! (Just dreading the encryption messaging haha)