SQLServerPSModule
SQLServerPSModule copied to clipboard
Invoke-sqlcmd can't decrypt data if we use access token instead of connection string
Problem Statement If we use connectionstring with Invoke-sqlcmd, we can specify "Column Encryption Setting = Enabled" to view plain text data if the column is encrypted.
$connStr = "Server=$serverInstance;Database=$tDBName;User Id=$($adminUsername);Password=$($adminPassword);Column Encryption Setting=Enabled;TrustServerCertificate=true;"
Invoke-Sqlcmd -ConnectionString $connStr -Query "SELECT [$columnName] FROM [$tableName]"
But if we use Access token instead of connection string, there is no way to specify "Column Encryption Setting = Enabled" due to which only encrypted data is displayed & it never tries to decrypt the data.
Connect-AzAccount
$access_token = (Get-AzAccessToken -ResourceUrl 'https://database.windows.net').Token
Invoke-Sqlcmd -ServerInstance $serverInstance -Database $AEPSTestDBName -AccessToken $access_token -Query "SELECT [$column1Name] FROM [$tableName]"
Expected: We should be able to set "Column Encryption Setting = Enabled" so that we can view decrypted/plain text data. Actual: Cannot set "Column Encryption Setting = Enabled", so only encrypted data is visible to the user.