powershell - connection to sql server via keyvault

Question

I'm working with a powershell script that I need to connect to SSISDB database via keyvault but getting error:

Exception calling "Open" with "0" argument(s): "Login failed for user ''."

Here is the connection string I'm using in code:

$env        = Get-AutomationVariable -Name 'Env'
$vaultName  = 'ab-'+$env.ToLower()+'-bi-keyvault'
$dataSource = 'ab'+$env.ToLower()+'dbserver01.database.windows.net'
Write-Output "vaultName: " $vaultName

$password = Get-AzKeyVaultSecret -VaultName $vaultName -Name 'DBPassword' 
$userName = Get-AzKeyVaultSecret -VaultName $vaultName -Name 'DBUsername' 
$passwordSecret = $password.SecretValueText    
$userNameSecret = $userName.SecretValueText


##########################################################

Write-Output "SSISDB"
Write-Output "JOB START"
# Create connection to Master DB
$database   = 'SSISDB'
Write-Output "Running job on " $database

$MasterDatabaseConnection = New-Object System.Data.SqlClient.SqlConnection
$MasterDatabaseConnection.ConnectionString = "Data Source=$dataSource;Initial Catalog=$database;Integrated Security=False;User ID=$userNameSecret ;Password=$passwordSecret ;Connect Timeout=60;Encrypt=False;TrustServerCertificate=False"

If I check connection with using userid/password directly in code , it does work but not with keyvault. Do you have any idea that what is the issue with my code?

question from:https://stackoverflow.com/questions/65940425/connection-to-sql-server-via-keyvault

Answer 1

Get-AzKeyVaultSecret doesn't return the value of the secret object by default.

Per the docs, you should use:

$passwordSecret = Get-AzKeyVaultSecret -VaultName $vaultName -Name 'DBPassword' -AsPlainText
$userNameSecret = Get-AzKeyVaultSecret -VaultName $vaultName -Name 'DBUsername' -AsPlainText

I have no idea why SecretValueText doesn't work.