Always Encrypted – Restoring Old Database Backup with C#

always-encryptedsql server

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.

Best Answer

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;
            }
Related Question