Patients
table with the following schema. To make things more interesting, make sure the table contains some data.
CREATE TABLE [dbo].[Patients](
[PatientId] [int] IDENTITY(1,1),
[SSN] [char](11) NOT NULL,
[FirstName] [nvarchar](50) NULL,
[LastName] [nvarchar](50) NULL,
[MiddleName] [nvarchar](50) NULL,
[StreetAddress] [nvarchar](50) NULL,
[City] [nvarchar](50) NULL,
[ZipCode] [char](5) NULL,
[State] [char](2) NULL,
[BirthDate] [date] NOT NULL
PRIMARY KEY CLUSTERED ([PatientId] ASC) ON [PRIMARY] )
GO
$cert = New-SelfSignedCertificate -Subject "AlwaysEncryptedCert" -CertStoreLocation Cert:CurrentUser\My -KeyExportPolicy Exportable -Type DocumentEncryptionCert -KeyUsage DataEncipherment -KeySpec KeyExchange
Import-Module "SqlServer"
$serverName = "myserver"
$databaseName = "Clinic"
$connStr = "Server = " + $serverName + "; Database = " + $databaseName + "; Integrated Security = True"
$connection = New-Object Microsoft.SqlServer.Management.Common.ServerConnection
$connection.ConnectionString = $connStr
$connection.Connect()
$server = New-Object Microsoft.SqlServer.Management.Smo.Server($connection)
$database = $server.Databases[$databaseName]
SqlColumnMasterKeySettings
object that contains information about the location of your column master key.
SqlColumnMasterKeySettings
is an object that exists in memory (in PowerShell).
$cmkSettings = New-SqlCertificateStoreColumnMasterKeySettings -CertificateStoreLocation "CurrentUser" -Thumbprint $cert.Thumbprint
$cmkName = "CMK1"
$cmk = New-SqlColumnMasterKey -Name $cmkName -InputObject $database -ColumnMasterKeySettings $cmkSettings
CREATE COLUMN MASTER KEY
Transact-SQL statement against the target database.
$cmk | Select-Object -Property *
$cekName = "CEK1"
New-SqlColumnEncryptionKey -Name $cekName -InputObject $database -ColumnMasterKey $cmkName
$cmkName
), from the database.
CREATE COLUMN ENCRYPTION KEY
Transact-SQL statement against the target database.
$cek | Select-Object -Property *
ColumnEncryptionKeyValues
property, which is an array of objects. Let's print the content of that array:
$cek.ColumnEncryptionKeyValues | Select-Object -Property *
ColumnEncryptionKeyValues
array contains encrypted values of the given column encryption key, along with the information on how each value was produced (the name of column master key used to encrypted the column encryption key and the name of the encryption algorithm). Normally (and this is the case above), a column encryption key has just one encrypted value. However, during a column master key rotation, it can have up to two values.
SqlColumnEncryptionSettings
objects, each of which describes target encryption settings for one column in the target database. In our example, we want to encrypt two columns in the
Patients
table:
SSN
and
BirthDate
. Hence, we create an array with two elements. Each
SqlColumnEncryptionSettings
object specifies the type of encryption (
Randomized
or
Deterministic
) for the target column, and the name of the metadata object describing the column encryption key to be used to encrypt the column.
$ces = @()
$ces += New-SqlColumnEncryptionSettings -ColumnName "dbo.Patients.SSN" -EncryptionType "Deterministic" -EncryptionKey "CEK1"
$ces += New-SqlColumnEncryptionSettings -ColumnName "dbo.Patients.BirthDate" -EncryptionType "Randomized" -EncryptionKey "CEK1"
Set-SqlColumnEncryption -InputObject $database -ColumnEncryptionSettings $ces -LogFileDirectory .
Set-SqlColumnEncryption
cmdlet transparently:
Patients
table, but the two specified columns (
SSN
and
BirthDate
) are configured as encrypted.
Patients
table.
Set-SqlColumnEncryption
locks the target table making it unavailable to write transactions throughout the duration of the entire operation. In SSMS 17.0 and later versions,
Set-SqlColumnEncryption
also supports the online mode, which minimizes the duration of downtime. We will discuss the online mode in a later blog post.
.
). Please note, that log file generation is only supported in SSMS 17.0 and later versions.
12/12/2016 5:34:59 PM INFO MainThread Logger initialized.
12/12/2016 5:34:59 PM INFO MainThread Acquiring database model and preparing data migration.
12/12/2016 5:35:10 PM INFO [dbo].[Patients] Data migration for table '[dbo].[Patients]' started.
12/12/2016 5:35:10 PM INFO [dbo].[Patients] Processing Table '[dbo].[Patients]'. 100.00 % done.
12/12/2016 5:35:10 PM INFO MainThread Finalizing data migration.
12/12/2016 5:35:11 PM INFO MainThread Deploying the specified encryption settings completed in 0d:0h:0m:11s.
SSN
and
BirthDate
columns are indeed encrypted.
Invoke-Sqlcmd -Query "SELECT TOP(1) * FROM Patients" -ConnectionString $connStr
SSN
and
BirthDate
columns appear as byte arrays, as the columns are encrypted.
Column Encryption Setting = Enabled
to the connection string. This instructs the client driver to decrypt the data retrieved from the encrypted columns.
$connStr = $connStr + "; Column Encryption Setting = Enabled"
Invoke-Sqlcmd -Query "SELECT TOP(1) * FROM Patients" -ConnectionString $connStr
SSN
and
BirthDate
columns now appear in plaintext. The
Invoke-Sqlcmd
cmdlet can successfully decrypt the data, as it runs on the machine containing the column master key.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.