My Current set up uses Azure Key Vault.
I have set up a Variable group in my Azure DevOPs which is linked to the Variable group. I have also give my pipeline permission to access the Variable group. However I'm getting an issue trying to get the varable and use it in an Inline Script in the Yaml. How do I escape the Variable so that it will allow the secret to have Special Characters in it and access it in Inline PS?
trigger:
- #none
pool:
name: 'DeployAgentPool' # name of the self-hosted agent pool
variables:
group: Onyx-Production-Variable-Group
------ other code -------
- stage: BackupDatabase
displayName: 'Backup SQL Database (Front End)'
jobs:
- job: BackupDatabaseJob
displayName: 'Backup SQL Database(Front End)'
pool:
vmImage: 'windows-latest'
steps:
- task: AzureCLI@2
displayName: 'Backup SQL Database (Front End)'
inputs:
azureSubscription: '$(AzureSubscription)'
scriptType: ps
scriptLocation: 'inlineScript'
inlineScript: |
Write-Output "Debug: Checking Key Vault variable linkage"
if ($env:STORAGEACCOUNTKEYDBASEBK -ne $null) {
Write-Output "Key Vault secret successfully retrieved."
$backupFileName = "SQLBK$(Get-Date -Format yyyyMMddHHmmss).bacpac"
$password = $(SqlAdmin2)
$stonyxdbasebakStorageAccountKey = $env:STORAGEACCOUNTKEYDBASEBK
Write-Output "Password length: $($password.Length)"
Write-Output "Storage account key length: $($stonyxdbasebakStorageAccountKey.Length)"
az sql db export --admin-password $password `
--admin-user "sqladmin" `
--storage-key '$stonyxdbasebakStorageAccountKey' ` <<<---Does Not WORK
--storage-key-type "StorageAccessKey" `
--storage-uri "https://s.blob.core.windows.net/frontendbk/$backupFileName" `
--name "sqldb-frontend-prod" `
--resource-group "$(ResourceGroup)" `
--server "sql-onyx-prod"
Write-Output "Database backup completed successfully!"
} else {
Write-Output "Key Vault secret not retrieved. Check variable group and pipeline linkage."
}
env:
STORAGEACCOUNTKEYDBASEBK: $(storageAccountKeyDbaseBk)}
Here is the Log:
2024-11-26T18:27:18.2894352Z Debug: Checking Key Vault variable linkage
2024-11-26T18:27:18.2917555Z Key Vault secret successfully retrieved.
2024-11-26T18:27:18.2940017Z Password length: 16
2024-11-26T18:27:18.2941703Z Storage account key length: 27
2024-11-26T18:27:23.2179512Z ERROR: (InvalidImportExportStorageKeyFormat) The ImportExport operation failed because of invalid storage key format.
2024-11-26T18:27:23.2180164Z Code: InvalidImportExportStorageKeyFormat
2024-11-26T18:27:23.2180652Z Message: The ImportExport operation failed because of invalid storage key format.
Everything else kept same. Here is the value stored in Keyvault: (Its Skewed on purpose)
Xm5MDLVQ0+EvJEEr+GIfqfhYyA5zsAuk3f3RSTyzh1WfLyRmQZqDG+vmhwGlI45UvxAFUWSNM+AStt5gNAQ==
If I change to : --storage-key $stonyxdbasebakStorageAccountKey:
2024-11-27T12:44:29.5750465Z Debug: Checking Key Vault variable linkage
2024-11-27T12:44:29.5771106Z Key Vault secret successfully retrieved.
2024-11-27T12:44:29.5859694Z Password length: 16
2024-11-27T12:44:29.5860520Z Storage account key length: 27
2024-11-27T12:44:29.5863688Z --storage-key-type was unexpected at this time.
2024-11-27T12:44:29.5877003Z D:\a\1\s> "C:\Program Files\Microsoft SDKs\Azure\CLI2\wbin\\..\python.exe" -IBm azure.cli sql db export --admin-password *** --admin-user sqladmin --storage-key $(storageAccountKeyDbaseBk) --storage-key-type StorageAccessKey --storage-uri https://s.blob.core.windows.net/frontendbk/SQLBK20241127124429.bacpac --name sqldb-frontend-prod --resource-group RG-ONYX-PROD --server sql-onyx-prod
IF I change to : --storage-key "$stonyxdbasebakStorageAccountKey":
2024-11-27T12:46:31.2358276Z Key Vault secret successfully retrieved.
2024-11-27T12:46:31.2372104Z Password length: 16
2024-11-27T12:46:31.2384041Z Storage account key length: 27
2024-11-27T12:46:31.2505543Z --storage-key-type was unexpected at this time.
2024-11-27T12:46:31.2507143Z D:\a\1\s> "C:\Program Files\Microsoft SDKs\Azure\CLI2\wbin\\..\python.exe" -IBm azure.cli sql db export --admin-password *** --admin-user sqladmin --storage-key $(storageAccountKeyDbaseBk) --storage-key-type StorageAccessKey --storage-uri https://s.blob.core.windows.net/frontendbk/SQLBK20241127124631.bacpac --name sqldb-frontend-prod --resource-group RG-ONYX-PROD --server sql-onyx-prod
If I change to: --storage-key $env:STORAGEACCOUNTKEYDBASEBK:
2024-11-27T12:51:09.3582802Z Debug: Checking Key Vault variable linkage
2024-11-27T12:51:09.3609111Z Key Vault secret successfully retrieved.
2024-11-27T12:51:09.3728853Z Password length: 16
2024-11-27T12:51:09.3731111Z Storage account key length: 27
2024-11-27T12:51:09.3734574Z --storage-key-type was unexpected at this time.
2024-11-27T12:51:09.3735930Z D:\a\1\s> "C:\Program Files\Microsoft SDKs\Azure\CLI2\wbin\\..\python.exe" -IBm azure.cli sql db export --admin-password *** --admin-user sqladmin --storage-key $(storageAccountKeyDbaseBk) --storage-key-type StorageAccessKey --storage-uri https://s.blob.core.windows.net/frontendbk/SQLBK20241127125109.bacpac --name sqldb-frontend-prod --resource-group RG-ONYX-PROD --server sql-onyx-prod
EDIT 2 USING KEYVAULT TASK
I've officially switched to using the KeyVault Task. Which works and is acceptable in this case. However there is still the issue with the special characters not being escaped correctly.. (also I really hate this PW).. Here is the code that is being run. and the following log. I'm doing a comparison in clear text for testing purposes on what the pw is and should be. It passes that test, but, when it is used in the AZ Cmd it gets hung up on the special characters.
- task: AzureCLI@2
displayName: 'Backup Front End SQL Database to Frontend Blob'
inputs:
azureSubscription: '$(AzureSubscription)'
scriptType: ps
scriptLocation: 'inlineScript'
inlineScript: |
Write-Output "Debug: Checking Key Vault variable linkage"
if ([string]::IsNullOrEmpty($env:STORAGEACCOUNTKEYDBASEBK)) {
Write-Output "The environment variable STORAGEACCOUNTKEYDBASEBK is either null or empty."
} else {
Write-Output "The environment variable STORAGEACCOUNTKEYDBASEBK has a value: $env:STORAGEACCOUNTKEYDBASEBK"
}
if ($env:SQLADMINPW -ne $null) {
Write-Output "SQL ADMIN secret successfully retrieved."
}
if ($env:STORAGEACCOUNTKEYDBASEBK -ne $null) {
Write-Output "Storage Key secret successfully retrieved."
$backupFileName = "SQLBK$(Get-Date -Format yyyyMMddHHmmss).bacpac"
#$password = $(SqlAdmin2)
#$password = $($env:SQLADMINPW)
$password = $env:SQLADMINPW
# Perform the comparison
if ($password -eq 'me*()oPR3!swet') {
Write-Output "Password matches expected value."
} else {
Write-Output "Password does not match the expected value."
}
Write-Output "Password length: $($password.Length)"
Write-Output "Executing command: az sql db export --admin-password [REDACTED] --admin-user sqladmin --storage-key "$($stonyxdbasebakStorageAccountKey)" --storage-key-type StorageAccessKey --storage-uri https://s.blob.core.windows.net/frontendbk/$backupFileName --name sqldb-frontend-prod --resource-group $(ResourceGroup) --server sql-onyx-prod"
az sql db export `
--admin-password $password `
--admin-user "sqladmin" `
--storage-key $env:STORAGEACCOUNTKEYDBASEBK `
--storage-key-type "StorageAccessKey" `
--storage-uri "https://s.blob.core.windows.net/frontendbk/$backupFileName" `
--name "sqldb-frontend-prod" `
--resource-group "$(ResourceGroup)" `
--server "sql-onyx-prod" `
Write-Output "Database backup completed successfully!"
} else {
Write-Output "Key Vault secret not retrieved. Check variable group and pipeline linkage."
}
env:
STORAGEACCOUNTKEYDBASEBK: $(storageAccountKeyDbaseBk) #works
SQLADMINPW: $(SqlAdminPassword) #actual value: me*()oPR3!swet
Logs fail with Unexpected value due to special characters.
2024-12-04T13:15:57.9194778Z Debug: Checking Key Vault variable linkage
2024-12-04T13:15:57.9295801Z The environment variable STORAGEACCOUNTKEYDBASEBK has a value: ***
2024-12-04T13:15:57.9309317Z SQL ADMIN secret successfully retrieved.
2024-12-04T13:15:57.9313711Z Storage Key secret successfully retrieved.
2024-12-04T13:15:57.9364242Z Password matches expected value.
2024-12-04T13:15:57.9395776Z Password length: 14
2024-12-04T13:15:57.9399725Z Storage account key length: 88
2024-12-04T13:15:57.9402513Z Executing command: az sql db export --admin-password [REDACTED] --admin-user sqladmin --storage-key
2024-12-04T13:15:57.9404924Z ***
2024-12-04T13:15:57.9408024Z --storage-key-type StorageAccessKey --storage-uri https://stonyxdbasebakprod.blob.core.windows.net/frontendbk/SQLBK20241204131557.bacpac --name sqldb-frontend-prod --resource-group RG-ONYX-PROD --server sql-onyx-prod
2024-12-04T13:15:57.9410176Z Debug Password (unmasked): ***
2024-12-04T13:15:58.0099423Z oPR3!swet was unexpected at this time.
More attempts: az sql db export --admin-password $env:SQLADMINPW`
2024-12-04T15:09:26.7271654Z The environment variable STORAGEACCOUNTKEYDBASEBK has a value: ***
2024-12-04T15:09:26.7286251Z SQL ADMIN secret successfully retrieved.
2024-12-04T15:09:26.7291313Z Storage Key secret successfully retrieved.
2024-12-04T15:09:26.7341046Z Password matches expected value.
2024-12-04T15:09:26.7372240Z Password length: 14
2024-12-04T15:09:26.7391037Z Executing command: az sql db export --admin-password [REDACTED] --admin-user sqladmin --storage-key
2024-12-04T15:09:26.7392021Z --storage-key-type StorageAccessKey --storage-uri https://stonyxdbasebakprod.blob.core.windows.net/frontendbk/SQLBK20241204150926.bacpac --name sqldb-frontend-prod --resource-group RG-ONYX-PROD --server sql-onyx-prod
2024-12-04T15:09:26.8114675Z oPR3!swet was unexpected at this time.
az sql db export --admin-password "$password"`
Identical result to above.
The issue in this situation is that the parameter needs to be wrapped in quotes inside the variable.
Process to solve this issue.
Verify the variables are coming in from either Pipeline, KeyVault, etc. (since you can't see them, because they are secrets, you can do a comparison eq with the Actual value you paste in.
Try the {az sql db export} cmd with the actual values.
Replicate the values in the paramaters. Im my case I had to enclose the Pwd in quotes inside the variable. Then pass that to the cmd.
stage: BackupFrontEndDatabase displayName: 'Backup Database (Front End) Stage' jobs: job: BackupDatabaseJob displayName: 'Backup Database(Front End) Job'
steps:
- task: AzureKeyVault@2
displayName: 'Retrieve Key Vault Secrets for StorageAccountKey'
inputs:
azureSubscription: '$(AzureSubscription)'
KeyVaultName: 'kv-onyx-$(Environment)'
SecretsFilter: '*' #Retrieve all secrets. you could specify exactly which ones.
- task: AzureCLI@2
displayName: 'Backup Front End SQL Database to Frontend Blob'
inputs:
azureSubscription: '$(AzureSubscription)'
scriptType: ps
scriptLocation: 'inlineScript'
inlineScript: |
Write-Output "Debug: Checking Key Vault variable linkage"
if ($env:STORAGEACCOUNTKEYDBASEBK -ne $null) {
Write-Output "Storage Key secret successfully retrieved."
$backupFileName = "SQLBK$(Get-Date -Format yyyyMMddHHmmss).bacpac"
# Define Storage Account and Container
$storageAccountName = "s$(Environment)"
$containerName = "dbasebk"
$password = "`"$env:SQLADMINPW`"" # THIS IS THE KEY.
# Execute the SQL export command
az sql db export `
--admin-password $password `
--admin-user "sqladmin" `
--storage-key $env:STORAGEACCOUNTKEYDBASEBK `
--storage-key-type "StorageAccessKey" `
--storage-uri "https://$storageAccountName.blob.core.windows.net/$containerName/$backupFileName" `
--name "sqldb-frontend-$(Environment)" `
--resource-group "$(ResourceGroup)" `
--server "sql-onyx-$(Environment)"
Write-Output "Database backup completed successfully!"
} else {
Write-Output "Key Vault secret not retrieved. Check variable group and pipeline linkage."
}
env:
STORAGEACCOUNTKEYDBASEBK: $(storageAccountKeyDbaseBk)
SQLADMINPW: $(SqlAdminPassword) # <<<<<< actual value: me*()oPR3!swet