Powershell Invoke-Sqlcmd with conditional parameters?

Question

I am working on a powershell script That is part of our build pipeline and is included in the repo. The script creates a local Db if it does not exist and is ran with every deployment.

However, when ran on my local machine for test deployment, I have to include the -TrustServerCertificate parameter. Otherwise the script will error.

When the pipeline runs on the server with the parameter it errors.

Invoke-Sqlcmd -ServerInstance $Server -Database "master" -TrustServerCertificate -InputFile $CreateDatabaseScript -Variable "DatabaseName=$Database"

What is a good way to handle this so that I do not have to manually toggle that parameter back and forth when doing work in the repo? I dont' want to accidently push the file with the parameter.

Answer 1

Use splatting to pass a table of parameter arguments to the command:

$additionalArgs = @{}
if ($env:COMPUTERNAME -eq 'my-dev-computer') {
   $additionalArgs['TrustServerCertificate'] = $true
}

Invoke-Sqlcmd -ServerInstance $Server -Database "master" @additionalArgs -InputFile $CreateDatabaseScript -Variable "DatabaseName=$Database" 

Now, if you run the script on your local dev machine, the $additionalArgs table will be populated with the TrustServerCertificate = $true, whereas anywhere else the table will be empty and the switch won't be passed to Invoke-SqlCmd

Answer 2

-TrustServerCertificate is a switch parameter - the way it's normally used is if you specify the switch in the call it passes a value of $true into the cmdlet parameter, and if you omit it the value it passes the switch value as $false instead.

However, there's a longer syntax where you can explicitly specify the value for the switch - e.g.:

Invoke-Sqlcmd ... -TrustServerCertificate:$true

# or
Invoke-Sqlcmd ... -TrustServerCertificate:$false

# or even
Invoke-Sqlcmd ... -TrustServerCertificate:$myvariable

If you initialise the value of $myvariable based on your environment you can enable or disable certificate trust as appropriate:

$TrustServerCertificate = $env:COMPUTERNAME -eq 'my-dev-computer'

Invoke-Sqlcmd `
    -ServerInstance $Server `
    -Database       "master" `
    -TrustServerCertificate:$TrustServerCertificate `
    -InputFile      $CreateDatabaseScript `
    -Variable       "DatabaseName=$Database"