Secure by default: breaking changes going from v21 to v22

With the adoption of Microsoft.Data.SqlClient 5.x, the module is embracing the desire to make the connections to remote endpoints more secure.

As a result of that, consumer of the module should be aware of some breaking changes as they migrate from v21.

Here's what you need to know:

• In v22, breaking changes started to trickle into the module (as a result of adopting a new SQL Client driver, a new SMO, and some intentional changes to the cmdlets).

• Invoke-Sqlcmd has been updated to adopt the new defaults that Microsoft.Data.SqlClient provides and uses.

  • Connection string properties: TrustServerCertificate, Encrypt and HostNameInCertificate
  • Cmdlet Parameters: -TrustServerCertificate [default=false], -Encrypt [default=Mandatory], and -HostNameInCertificate
  • How to get the v21 behavior: use a mix of -TrustServerCertificate and -Encrypt Optional depending on your environment.

• The rest of the cmdlets:

  • Started exposing the new parameters you see on Invoke-Sqlcmd (-TrustServerCertificate, -Encrypt and -HostNameInCertificate)
  • For v22, in order to minimize the disruption in existing scripts and allow a more gradual migration, the new "secure by default" is not enforced. This means that the behavior of the cmdlet is similar to v21.
  • For those cmdlets, however, the user can start using the new "secure by default" parameters: this would allow two things:
    • an incremental updates of existing script such that they will be ready by the time v23 is adopted
    • enable scenarios that would otherwise be impossible using the old default behavior (e.g. -Encrypt Strict)
  • Cmdlets that allow passing a -ConnectionString argument (and bypass SMO when connecting to the endpoint), would exhibit the new "secure by default" behavior.
    • How to get the v21 behavior: you will have to update the connection string you are passing to specify the less secure options in the connection string.

• The PSProvider SQLSERVER: is still using the less secure connectivity mechanism (again: this is a side effect of relying on the SMO machinery when establishing the connection to the endpoint). This behavior will most likely change in v23 to confirm to the 'secure by default'.