0

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.

0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.