configuredb.ps1

# Generated by SQL Server Management Studio at 15:24 on 20/04/2020

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

$sqlConnectionString = "Data Source=.;Initial Catalog=EFCoreEncryption;Integrated Security=True;MultipleActiveResultSets=False;Connect Timeout=30;Encrypt=False;TrustServerCertificate=False;Packet Size=4096;Application Name=`"Microsoft SQL Server Management Studio`""
$database = Get-SqlDatabase -ConnectionString $sqlConnectionString

# Create Encryption Key
$certificate = New-SelfSignedCertificate -Subject "Always Encrypted Auto Generated" -CertStoreLocation "Cert:LocalMachine\My" -KeyExportPolicy Exportable -Type DocumentEncryptionCert -KeyUsage KeyEncipherment -KeySpec KeyExchange -KeyLength 2048

# Register the Master Encryption Key
$cmk = "Sample_CMK"
$settings = New-SqlCertificateStoreColumnMasterKeySettings -CertificateStoreLocation "LocalMachine" -Thumbprint $certificate.Thumbprint
New-SqlColumnMasterKey -Name $cmk -ColumnMasterKeySettings $settings -InputObject $database

# Register the Column Encryption Key
$cek = "Message_CEK"
New-SqlColumnEncryptionKey -Name $cek -InputObject $database -ColumnMasterKey $cmk

# Change encryption schema

$encryptionChanges = @()

# Add changes for table [dbo].[Messages]
$encryptionChanges += New-SqlColumnEncryptionSettings -ColumnName dbo.Messages.Content -EncryptionType Randomized -EncryptionKey $cek

Set-SqlColumnEncryption -ColumnEncryptionSettings $encryptionChanges -InputObject $database