Encrypt or decrypt a column with PowerShell

To encrypt or decrypt a column with PowerShell:

Encrypt a column

To encrypt a column:

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

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

    # Import the SqlServer module.
Import-Module SqlServer

# Set up connection and database SMO objects
$sqlConnectionString = "Data Source=MS-SQL-AE-Srv.interop.com; Initial Catalog=TestDatabase; Integrated Security=True; MultipleActiveResultSets=False; Connect Timeout=30; Encrypt=True; TrustServerCertificate=True; Packet Size=4096; Application Name=`"Microsoft SQL Server Management Studio`""
$smoDatabase = Get-SqlDatabase -ConnectionString $sqlConnectionString

# If your encryption changes involve keys in Azure Key Vault, uncomment one of the lines below in order to authenticate:
#   * Prompt for a username and password:
#Add-SqlAzureAuthenticationContext -Interactive

#   * Enter a Client ID, Secret, and Tenant ID:
#Add-SqlAzureAuthenticationContext -ClientID '<Client ID>' -Secret '<Secret>' -Tenant '<Tenant ID>'

# 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

To remove column encryption:

  1. Launch PowerShell on the client computer and run the Decrypt_Column_Named_Password.ps1 script (below).

    # Import the SqlServer module.
Import-Module SqlServer

# Set up connection and database SMO objects
$sqlConnectionString = "Data Source=MS-SQL-AE-Srv.interop.com; Initial Catalog=TestDatabase; Integrated Security=True; MultipleActiveResultSets=False; Connect Timeout=30; Encrypt=True; TrustServerCertificate=True; Packet Size=4096; Application Name=`"Microsoft SQL Server Management Studio`""
$smoDatabase = Get-SqlDatabase -ConnectionString $sqlConnectionString

# If your encryption changes involve keys in Azure Key Vault, uncomment one of the lines below in order to authenticate:
#   * Prompt for a username and password:
#Add-SqlAzureAuthenticationContext -Interactive

#   * Enter a Client ID, Secret, and Tenant ID:
#Add-SqlAzureAuthenticationContext -ClientID '<Client ID>' -Secret '<Secret>' -Tenant '<Tenant ID>'

# 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

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

  3. 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.