Always Encrypted after restoring an old database backup using C#

Question

I have a Windows Form application. It allows users to restore a SQL Express database.

Latest version of SQL Server in app now is SQL Server 2017. Customer can restore an old backup of a SQL Server 2008 R2 database.

I want to enable Always Encrypted (AE) after performing the restore using C#.

Could anyone please help me to achieve this?

There is no issue enabling AE for a new database. I would like to have AE after restoring an old database into the application. The restore happens via a T-SQL command.

I tried to enable AE using PowerShell. I am getting sqlserver module errors. Is there any way to add AE using T-SQL?

The error is:

System.Management.Automation.CommandNotFoundException:
'The term 'Get-SqlDatabase' is not recognized as the name of a cmdlet, function, script file, or operable program. Check the spelling of the name, or if a path was included, verify that the path is correct and try again.'

This errors in PowerShell; however, I really want to do AE without PowerShell in C#.

I tried:

Alter table test3 
 alter column C3 varchar(50) COLLATE Latin1_General_BIN2 
 ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK_Auto1], 
 ENCRYPTION_TYPE = Deterministic, 
 ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NOT NULL

This command doesn't work when there is data in the table.

Answer 1

I could achieve always encrypted using c#. generate powershell script using management studio.

Add below import modules in script at top.

Import-Module -Name "SqlServer" -MinimumVersion "21.0.17262"

Then run PS script using below code

 using (PowerShell powerShellInstance = PowerShell.Create())
 {
 powerShellInstance.AddScript(
 "Set-ExecutionPolicy -Scope Process -ExecutionPolicy ByPass");
 powerShellInstance.AddScript(scriptFileText, true);
 var psOutput = powerShellInstance.Invoke();
 foreach (PSObject outputItem in psOutput)
 {
 if (outputItem != null)
 {
 //Log output if error 
 }
 }
 success = true;
 }