Azure-pipelines-tasks: Azure Powershell cannot use secret variable

[martinnormark]

I am using the Azure Powershell tasks to run database copy, backup and delete tasks.

I have some secret variables, among others the SQL server admin password:

In the export task I use New-AzureRmSqlDatabaseExport to backup the database to a storage account.

But it fails with the given reason:

##[error]Cannot bind parameter 'AdministratorLoginPassword'. Cannot convert the ******** value of type "System.String" to type "System.Security.SecureString".

Script for the entire task:

$rg = "$(resourcegroup)"
$server = "$(servername)"
$db = "$(destdb)"
$storagekey = "$(storagekey)"
$storageuri = "$(storageuri)"
$uid = "$adminuid)"
$pwd = "$(adminpwd)"

New-AzureRmSqlDatabaseExport -ResourceGroupName $rg -ServerName $server -DatabaseName $db -StorageKeyType "StorageAccessKey" -StorageKey $storagekey -StorageUri $storageuri -AdministratorLogin $uid -AdministratorLoginPassword $pwd

I have another task, also an Azure Powershell task, where I use the same secret variable without any problems:

Install-PackageProvider -Name NuGet -Force -Scope CurrentUser
Install-Module -Name SqlServer -Force -Verbose -Scope CurrentUser

$server = "[databasename].database.windows.net"
$uid = "$(adminuid)"
$pwd = "$(adminpwd)"
$db = "$(destdb)"

Write-Host "Cleaning tables..."

Invoke-SqlCmd -ServerInstance $server -U $uid -P $pwd -Database $db -QueryTimeout 65535 -Query "QUERY"

[KrishnaAdityaB]

@martinnormark The secret variable value entered in Release Definition is available as _System.String_ in the tasks.
_Invoke-SqlCmd_ expects a _System.String_ for _-P_. And so it was successful.
But _New-AzureRmSqlDatabaseExport_ expects a _System.Security.SecureString_ for _-AdministratorLoginPassword_. And so the error.

You have to convert the value from one type to other. See if this link helps https://docs.microsoft.com/en-us/powershell/module/microsoft.powershell.security/convertto-securestring?view=powershell-5.1

[martinnormark]

@KrishnaAdityaB God damn, I was sure I'd already tried this - it works 🎉

Setting the $pwd variable should be done like this for it to work:

$pwd = ConvertTo-SecureString "$(adminpwd)" -AsPlainText -Force