Working with the new SQL Server PS module and -Encrypt parameter

With the introduction of the new SQL Server PS module, Microsoft has made the -Encrypt parameter mandatory for Invoke-Sqlcmd cmdlet.

Invoke-Sqlcmd -ServerInstance $server -query $sql -Encrypt Optional

In our environment:

• We have a custom PowerShell module containing numerous Invoke-Sqlcmd calls.
• We manage thousands of servers, each with different versions of the SQL Server PS module.

Given the variability in SQL Server PS module versions across our servers, we aim to leverage the default parameter option in PowerShell. To achieve this, we've included the following line in the $PROFILE.AllUsersAllHosts profile:

$PSDefaultParameterValues['Invoke-Sqlcmd:Encrypt'] = 'Optional'

This setup works seamlessly for scripts executed from Visual Studio Code. However, functions within our custom module that call Invoke-Sqlcmd encounter failures. To address this, we've implemented an alternative approach by creating a function in the module with the following line:

$PSDefaultParameterValues['Invoke-Sqlcmd:Encrypt'] = 'Optional'

Consequently, before utilizing any function from the module, we execute the function responsible for setting the optional value.

Considering these challenges, is there a more efficient alternative? Moreover, when a PowerShell module is loaded, in which scope does it operate? How can we establish the optional value within that scope?

Try placing this at the top of your module, outside any function.

$PSDefaultParameterValues['Invoke-Sqlcmd:Encrypt'] = 'Optional' ;