search

microsoft / SQLServerPSModule

This repo is the home of SQL Server PowerShell Module development.
MIT License
45 stars 1 forks source link

Invoke-sqlcmd can't decrypt data if we use access token instead of connection string #83

Open sejagada opened 3 weeks ago

sejagada commented 3 weeks ago

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.