1

Is there any way to update an encrypted (always encrypted) column with PowerShell or nothing yet? The master key is stored in Azure Key Vault.

I'm able to update a column on a not encrypted column using the PowerShell below:

$sqlConn = New-Object System.Data.SqlClient.SqlConnection
$sqlConn.ConnectionString = "Server=MyServer;Integrated Security=true; Initial Catalog=MyDatabase; Column Encryption Setting=enabled;"

$sqlConn.Open()

$sqlcmd = New-Object System.Data.SqlClient.SqlCommand
$sqlcmd.Connection = $sqlConn

$sqlcmd.CommandText = "UPDATE dbo.MyTable SET Col1 = @col1"

$sqlcmd.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@col1",[Data.SQLDBType]::NVARCHAR, 50)))
$sqlcmd.Parameters["@col1"].Value = "MyNewValue"

$sqlcmd.ExecuteNonQuery()

$sqlConn.Close()

I'm able to update an encrypted column if I connect to Microsoft SQL Server Management Studio, connecting to my database with a user that has access to the master key, stored in Azure Keyvault, if I'm using parametrization (System.Data.SqlClient.SqlParameter) like this:

DECLARE @myNewValue nvarchar(50) = 'MyNewValue'

UPDATE [dbo].[MyTable]
SET col1 = @myNewValue

Does anyone know how to do it with Powershell?

Thank you!

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Lekster001
  • 63
  • 1
  • 6
  • You already said this works if you use the correct account. – Panagiotis Kanavos Mar 01 '21 at 18:19
  • @PanagiotisKanavos No, I said it works in SQL. My question is how to do the same I did in SQL into a PowerShell script. – Lekster001 Mar 01 '21 at 18:30
  • SSMS isn't SQL Server. As far as the server is concerned, it's just another client. You need to use the correct client library though. The System.Data.SqlClient library that comes with .NET (and the old Powershell) doesn't support Always Encrypted. You need to [use Microsoft.Data.SqlClient](https://learn.microsoft.com/en-us/sql/connect/ado-net/sql/sqlclient-support-always-encrypted?view=sql-server-ver15) – Panagiotis Kanavos Mar 01 '21 at 18:37
  • Not sure to understand. What you say is that everything I can do in c# .net core, I can do it in PowerShell? By Exemple, I just did a c# .Net Core Console example that works fine to update an encrypted column. It's possible to do they same in PowerShell? https://www.codepile.net/pile/eAOqEPnr – Lekster001 Mar 02 '21 at 13:57
  • Yes. Powershell is a .NET application. Powershell Core is a .NET Core application. The Powershell core you posted uses .NET objects and methods. – Panagiotis Kanavos Mar 02 '21 at 14:00
  • Has this issue been resolved? – Allen Wu Mar 29 '21 at 06:16
  • @AllenWu Not yet but we have a Jira in this sprint to find a solution so if we found it, I'll post it here. – Lekster001 Mar 30 '21 at 11:38
  • @Lekster001 Thanks:) We appreciate it if you post the solution, which will help others. – Allen Wu Mar 31 '21 at 01:15

0 Answers0