Invoke-Sqlcmd -Encrypt default value

[NJK-Work]

Since the -encrypt parameter was just added, all of our existing scripts cannot use the new 22.0 version of the module since it defaults to "Mandatory", but it appears we need "Optional" in order to make a connection. We would have to update dozens of scripts (probably closer to over 100) to add this parameter, as "optional", to make them work with the new module - but then adding that parameter also breaks the script if the user has not updated to the new module yet (since the old module is unaware of that new parameter). So we are stuck trying to coordinate all the scripts to be updated and all the users getting the new module at the same time.

Would it be possible for you to set the default value to "Optional" instead, so that if a user has the new module but is using a script that has not yet been updated to include that parameter, it will still be able connect to the DB? I am not a DBA...I just utilize this module to pull data from tables that we use in our scripts - so I don't fully understand the ramifications of setting it one way or the other. I just know that the scripts now fail to connect to our databases if the default value of "Mandatory" is used and setting it to optional allows it to work.

Thanks NJK

[Matteo-T]

@NJK-Work - the behavior you see was quite intentional and, ultimately, it is reflecting the direction where the Microsoft.Data.SqlClient driver is going, which is to have a default value of Mandatory.

PowerShell is pretty good about versioning and allowing having multiple version SxS, precisely to handle cases like this one. Have you considered using Import-Module SqlServer -RequiredVersion ... or import-module sqlserver -MinimumVersion or #Requires ... in your scripts?

[Matteo-T]

@erinstellato-ms, FYI

[NJK-Work]

I understand. Thank you for taking the time to respond and explain to me.

NJK

[erinstellato-ms]

@NJK-Work if you have suggestions about how we can make the documentation more clear, feel free to share here. Thanks.