$cert = New-SelfSignedCertificate -Subject "AlwaysEncryptedCert" -CertStoreLocation Cert:CurrentUserMy -KeyExportPolicy Exportable -Type DocumentEncryptionCert -KeyUsage KeyEncipherment -KeySpec KeyExchange -KeyLength 2048
$cert.Thumbprint
CREATE COLUMN MASTER KEY [ColumnMasterKey1]
WITH
(
KEY_STORE_PROVIDER_NAME = N'[SomeProviderName]',
KEY_PATH = N'[SomeKeyPath]'
)
GO
KEY_STORE_PROVIDER_NAME
property to correspond to the key store containing your column master key, and you need to set
KEY_PATH
to identify your column master key in the key store. See
CREATE COLUMN MASTER KEY (Transact-SQL)
for more information. Here is how to do that for a column master key that is a certificate stored in the Current User certificate store location. Note, that the key path contains the certificate thumbprint, which is the output of the PowerShell script, you executed in the
Create a column master key in your certificate store
step above.
CREATE COLUMN MASTER KEY [ColumnMasterKey1]
WITH
(
KEY_STORE_PROVIDER_NAME = N'MSSQL_CERTIFICATE_STORE',
KEY_PATH = N'CurrentUser/My/7DF4AC73178D365291DF943E0F09653DDE296FC7'
)
GO
# Import the SqlServer module
Import-Module "SqlServer" -DisableNameChecking
# Create a SqlColumnMasterKeySettings object for your column master key.
$cmkSettings = New-SqlCertificateStoreColumnMasterKeySettings -CertificateStoreLocation "CurrentUser" -Thumbprint $cert.Thumbprint
# Generate a column encryption key, encrypt it with the column master key to produce an encrypted value of the column encryption key.
$encryptedValue = New-SqlColumnEncryptionKeyEncryptedValue -TargetColumnMasterKeySettings $cmkSettings
$encryptedValue
CREATE COLUMN ENCRYPTION KEY [ColumnEncryptionKey1]
WITH VALUES
(
COLUMN_MASTER_KEY = [SomeCMKName],
ALGORITHM = N'AEAD_AES_256_CBC_HMAC_SHA_256',
ENCRYPTED_VALUE = [SomeEncryptedValue]
);
COLUMN_MASTER_KEY
property to the name of the metadata object for the column master key that encrypts the column encryption key. Set
ALGORITHM
to
'RSA_OAEP'
. Copy the hexadecimal string, produced in step 1, as the value of the
ENCRYPTED_VALUE
property. Here is how the modified T-SQL should look like.
CREATE COLUMN ENCRYPTION KEY [ColumnEncryptionKey1]
WITH VALUES
(
COLUMN_MASTER_KEY = [ColumnMasterKey1],
ALGORITHM = 'RSA_OAEP',
ENCRYPTED_VALUE = 0x016E000001630075007200720065006E00740075007300650072002F006D0079002F003700640066003400610063003700330031003700380064003300360035003200390031006400660039003400330065003000660030003900360035003300640064006500320039003600660063003700A4E3998FA143C159CB3F54B819D1E95827F40122E563B9DCBB855F366B1192979EE73374992A2D074DF7F4B19B0D6BE84D674FDFD574A52C1FD09F375D954B8EA01D63F002D36BF7BBE3AE8D3279EAA189F589F4FE520CF76A0064929E094FC1BDEE91B7C53DAB47F09FA3888C5DF07C210A604329F9F5CEB4495E00F7CD7055993ACA7E1D7D6885553D06312C60396A0DF2449144FFC32008B7636901C2B9EDEC8588BAE8DA079496DDA9BCF8367AE618C3D1F1504559A523379C29C8BA8658972BA3A25F46DDE3FC50CB198A3D3EE24CE92E7739CE8443E01A12BD7ABB58790812EF49AB592D0E537D808E1205C92B00D188EB221740CA8891B43EF58CC1968735F7AE779F5A98DF2BC1B983E16D767BD0926AF4EB6847B7D928D0548D77EC76C935DF37C4FB2E410B4925E26BD52648FABF2ED08DDFFD49B13EE5CEC923A1CA3F222AB7E104E0C0649D0D158DBA33687DC1C6FBBF5573F057CF0ADCEF4033ECB832B57C658127109DB871D83CB7FAD6ADADC05089424EECAA57A27BA41AD71AA17109F710004BFC71FF0E30E826F131FB8A63D0284B5EC33C1FA34AD2982E5613F243F8F916C5A4DF9C46210255580E29DE4FC0DE189A013078D4C6E1BA672CE549F4B52672D2FC06B94B9E7F95BFB2238EF73B0694706AF068FD07A874E4ADAF86DD405F2A68D9ABEA50292824555AD4E319A3B024D189D08E8E43CAD235
)
GO
CREATE TABLE
T-SQL statement for the table will show up in the T-SQL editor in the main pane. We'll use a Patients table as our sample throughout the rest of this article.
CREATE TABLE [dbo].[Patients] (
[PatientID] INT IDENTITY (1, 1) NOT NULL,
[SSN] CHAR (11) NOT NULL,
[FirstName] NVARCHAR (50) NULL,
[LastName] NVARCHAR (50) NOT NULL,
[MiddleName] NVARCHAR (50) NULL,
[StreetAddress] NVARCHAR (50) NOT NULL,
[City] NVARCHAR (50) NOT NULL,
[ZipCode] CHAR (5) NOT NULL,
[State] CHAR (2) NOT NULL,
[BirthDate] DATE NOT NULL,
CONSTRAINT [PK_dbo.Patients] PRIMARY KEY CLUSTERED ([PatientID] ASC)
);
ENCRYPTED WITH
clause for each column you want to encrypt. Specify:
COLUMN_ENCRYPTION_KEY
- this should be the name of your column encryption key metadata object.
ENCRYPTION_TYPE
- set it to
RANDOMIZED
for randomized encryption or
DETERMINISTC
for deterministic encryption. See
Always Encrypted
on MSDN or
Getting Started with Always Encrypted
on this blog for more information.
ALGORITHM
- it must be set to
'AEAD_AES_256_CBC_HMAC_SHA_256'
CREATE TABLE [dbo].[Patients] (
[PatientID] INT IDENTITY (1, 1) NOT NULL,
[SSN] CHAR (11)
COLLATE Latin1_General_BIN2 ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [ColumnEncryptionKey1], ENCRYPTION_TYPE = Deterministic, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256')
NOT NULL,
[FirstName] NVARCHAR (50) NULL,
[LastName] NVARCHAR (50) NOT NULL,
[MiddleName] NVARCHAR (50) NULL,
[StreetAddress] NVARCHAR (50) NOT NULL,
[City] NVARCHAR (50) NOT NULL,
[ZipCode] CHAR (5) NOT NULL,
[State] CHAR (2) NOT NULL,
[BirthDate] DATE
ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [ColumnEncryptionKey1], ENCRYPTION_TYPE = Deterministic, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256')
NOT NULL,
CONSTRAINT [PK_dbo.Patients] PRIMARY KEY CLUSTERED ([PatientID] ASC)
);
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.