Search code examples
sql-serverpowershell

Encryption PowerShell script generated by SQL Server Management Studio executes but doesn't work

In SSMS I tried adding encryption to columns. If I do this in the dialog box, the process works and the field is encrypted. If I try generating a PowerShell script to set the encryption, after many error messages I've finally got the code to execute without error but nothing happens in the database. This is the generated script with all the defaults and standard things and I'm running PowerShell as an administrator. 

Import-Module SqlServer
# Set up connection and database SMO objects

$sqlConnectionString = "Data Source=MyPC\SQLEXPRESS;Initial Catalog=MyDatabase;Integrated Security=True;MultipleActiveResultSets=False;Connect Timeout=30;Encrypt=False;TrustServerCertificate=False;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].[MyTable]
$encryptionChanges += New-SqlColumnEncryptionSettings -ColumnName dbo.MyTable.MyField -EncryptionType Randomized -EncryptionKey "CEK_Auto1"

Set-SqlColumnEncryption -ColumnEncryptionSettings $encryptionChanges -InputObject $smoDatabase

Any ideas why the code runs but doesn't encrypt the columns?

Solution

  • https://learn.microsoft.com/en-us/sql/powershell/download-sql-server-ps-module?view=sql-server-2017

    https://learn.microsoft.com/en-us/sql/relational-databases/security/encryption/configure-column-encryption-using-powershell?view=sql-server-ver15

    Following the instructions above with some modifications

    Run PowerShell as admin

    To give permissions in this session for the file to be executed This should give correct permission

    Set-ExecutionPolicy -ExecutionPolicy RemoteSigned -Scope CurrentUser

    To install SQL Server modules so it PowerShell can access it

    Install-Module -Name SqlServer -AllowClobber

    View available commands in SqlServer module

    Get-Command -Module SqlServer

    If New-SqlColumnEncryptionSettings isn't visible, copy files from

    sqlserver.21.1.18218.nupkg.zip

    downloaded from

    https://www.powershellgallery.com/packages/SqlServer/21.1.18218

    to

    C:\Program Files\WindowsPowerShell\Modules\SqlServer\21.1.18218

    Then run the powershell script using 

    .\test.ps1