Encrypt or decrypt a column with PowerShell

Encrypt a column

  1. Log in to the client using the <domain>\dbuser account.

  2. Launch PowerShell and run the Encrypt_Column_Named_Password.ps1 script (below).

    # Import the SqlServer module.
Import-Module SqlServer -MinimumVersion 22.0.59

# Set up connection and database SMO objects
$sqlConnectionString = "Data Source=MSSQL-AE2025SRV;Initial Catalog=TestDatabase;Integrated Security=True;Multiple Active Result Sets=False;Connect Timeout=30;Encrypt=True;Trust Server Certificate=True;Packet Size=4096;Application Name=`"Microsoft SQL Server Management Studio`""
$smoDatabase = Get-SqlDatabase -ConnectionString $sqlConnectionString

# Change encryption schema
$encryptionChanges = @()

# Add changes for table [dbo].[TestTable]
$encryptionChanges += New-SqlColumnEncryptionSettings -ColumnName dbo.TestTable.Password -EncryptionType Randomized -EncryptionKey "MyCEK"
Set-SqlColumnEncryption -ColumnEncryptionSettings $encryptionChanges -InputObject $smoDatabase

    The command line is:

    > PowerShell -ExecutionPolicy Bypass -File Encrypt_Column_Named_Password.ps1

  3. Present the OCS, select the HSM, and enter the passphrase.

  4. Launch Microsoft SQL Server Management Studio. Do as indicated in encrypt-decrypt-column-with-ssms.adoc#view-encrypted-column-ssms to verify the column has been encrypted.

Remove column encryption

  1. Log in to the client using the <domain>\dbuser account.

  2. Launch PowerShell and run the Decrypt_Column_Named_Password.ps1 script (below).

    # Import the SqlServer module.
Import-Module SqlServer -MinimumVersion 22.0.59

# Set up connection and database SMO objects
$sqlConnectionString = "Data Source=MSSQL-AE2025SRV;Initial Catalog=TestDatabase;Integrated Security=True;Multiple Active Result Sets=False;Connect Timeout=30;Encrypt=True;Trust Server Certificate=True;Packet Size=4096;Application Name=`"Microsoft SQL Server Management Studio`""
$smoDatabase = Get-SqlDatabase -ConnectionString $sqlConnectionString

# Change encryption schema
$encryptionChanges = @()

# Add changes for table [dbo].[TestTable]
$encryptionChanges += New-SqlColumnEncryptionSettings -ColumnName dbo.TestTable.Password -EncryptionType Plaintext
Set-SqlColumnEncryption -ColumnEncryptionSettings $encryptionChanges -InputObject $smoDatabase

    The command line is:

    > PowerShell -ExecutionPolicy Bypass -File Decrypt_Column_Named_Password.ps1

  3. Present the OCS, select the HSM, and enter the passphrase.

  4. Launch Microsoft SQL Server Management Studio. Do as indicated in encrypt-decrypt-column-with-ssms.adoc#view-encrypted-column-ssms to verify the column has been decrypted.