As you have learned from our previous articles, Always Encrypted is a client-side encryption technology - sensitive data is transparently encrypted and decrypted within a client application by a client driver. SQL Server does not have access to plaintext encryption keys and cannot encrypt or decrypt encrypted data. An important implication of the above is that encrypting existing data requires downloading the data to a trusted client machine, encrypting it and uploading it back to the database.
In SQL Server 2016 CTP2, using SQL Server Import Export Wizard is the easiest way to perform encryption. We will show you two approaches:
If you want to follow the examples presented in this article, you will need to install the following:
In the following examples, we assume we have a database, named Clinic , containing the Patients table, defined below.
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
The table contains existing data and we want to encrypt both the SSN and BirthDate columns.
a) Create a new database, named ClinicEncrypted .
b) Set up Always Encrypted keys (a column encryption key and a column master key) in the new database. Please refer to the example in Getting Started With Always Encrypted for how to set up the keys.
c) Re-create the Patients table in the new database, configuring both the SSN and BirthDate columns as encrypted.
CREATE TABLE [dbo].[Patients](
[PatientId] [int] IDENTITY(1,1),
[SSN] [char](11)
COLLATE Latin1_General_BIN2
ENCRYPTED WITH (ENCRYPTION_TYPE = DETERMINISTIC,
ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256',
COLUMN_ENCRYPTION_KEY = CEK1) 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]
ENCRYPTED WITH (ENCRYPTION_TYPE = RANDOMIZED,
ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256',
COLUMN_ENCRYPTION_KEY = CEK1) NOT NULL
PRIMARY KEY CLUSTERED ([PatientId] ASC) ON [PRIMARY] )
GO
Please note that you need to set the collation of the
SSN
column to the
Latin1_General_BIN2
collation – encrypted columns containing character strings must have one of the binary2 collations.
a) In SSMS, navigate to your original database, Clinic , using Object Explorer. Right click on the database, select Tasks , and then select Export Data … . This will open SQL Server Import Export Wizard. Click Next in the wizard window. (Alternatively, you can open the wizard directly.)
b) When configuring the data source, set up a connection to your original Clinic database, from which you will be exporting the data. You can use any SQL Server driver to connect to the database.
c) When configuring the data destination, set up a connection to the new database, ClinicEncrypted , you will be importing the data to. For this connection, you must:
d) Select Copy data from one or more tables or views and click Next .
e) Select all tables in the source database. Click Finish >> and then click Finish . The wizard will copy all data from your original database to the new ClinicEncrypted database. SqlClient will transparently encrypt data inserted into the SSN and BirthDate columns during the import operation. Close the wizard.
To verify the
SSN
and
BirthDate
columns in the new database are encrypted, you can query the
Patients
table in the new database (
SELECT * FROM Patients
). You should see encrypted binary values in both columns.
Note: SQL Server Import Export Wizard must be able to access the column master key, you configured in your database, in order to encrypt your columns. If you are using a certificate as a column master key, one way to ensure the wizard can access the certificate is to run the wizard on the same machine, on which the certificate was generated and installed (e.g. using SSMS) and under the context of the same user, who generated the certificate. Please, refer to Always Encrypted Key Metadata for information on how column master keys are used in the encryption process.
To complete the database-level encryption workflow, you can remove or rename the original database ( Clinic ) and rename ClinicEncrypted as Clinic . Or, you could just redirect your applications to ClinicEncrypted by changing applications’ connection strings.
When using SQL Server Import Export Wizard for encryption, an alternative to copying the entire database is to copy the tables containing encrypted columns within your original database.
Set up Always Encrypted keys (a column encryption key and a column master key) in your database ( Clinic ). Please refer to the example in Getting Started With Always Encrypted on how to set up the keys.
For each table, containing columns you want to encrypt, you need to create a new version of that table, with the schema that is identical to the schema of the original table, but has selected columns encrypted. In our example, we are going to create the new PatientsEncrypted table with the SSN and BirthDate columns configured as encrypted.
CREATE TABLE [dbo].[PatientsEncrypted](
[PatientId] [int] IDENTITY(1,1),
[SSN] [char](11)
COLLATE Latin1_General_BIN2
ENCRYPTED WITH (ENCRYPTION_TYPE = DETERMINISTIC,
ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256',
COLUMN_ENCRYPTION_KEY = CEK1) 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]
ENCRYPTED WITH (ENCRYPTION_TYPE = RANDOMIZED,
ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256',
COLUMN_ENCRYPTION_KEY = CEK1) NOT NULL
PRIMARY KEY CLUSTERED ([PatientId] ASC) ON [PRIMARY] )
GO
a) In SSMS, navigate to your database, Clinic , using Object Explorer. Right click on the database, select Tasks , and then select Export Data … . This will open SQL Server Import Export Wizard. Click Next in the wizard window.
b) When configuring the data source, set up a connection to your Clinic database. You can use any SQL Server driver for that connection. Click Next .
c) When configuring the data destination, set up a connection to the same database – Clinic . For this connection, you must:
Click Next .
d) Select Copy data from one or more tables or views and click Next .
e) Configure the Patients table as a source table and set PatientsEncrypted as a destination table for data copied from the Patients table.
f) Click Finish>> and then click Finish .
g) The wizard will copy all data from the Patients table to the PatientsEncrypted table. SqlClient will transparently encrypt the data inserted into the SSN and BirthDate columns during the import operation. Close the wizard.
To complete the table-level encryption workflow, you need to remove or rename the original table ( Patients ) and rename PatientsEncrypted as Patients . Note that if there were any dependencies of on the Patients table (e.g. foreign key relationships), you would need to remove the dependencies and recreate them after swapping the tables.
In general, we recommend using the table-level encryption workflow if one of the following is true:
We recommend the database-level encryption workflow if columns to be encrypted are contained in multiple tables and the size of tables not containing columns you want to encrypt is small (or it is acceptable for the data migration to take a long time).
Before you start encryption, you need to stop all applications (or at least stop all write transactions) using:
Before resuming your application workload, you need modify your database connection strings to include column encryption setting=Enabled . Other changes might also be required, e.g. if an application issues queries that perform unsupported operations on encrypted columns, the queries/the application logic will need to be refactored.
We highly recommend performing encryption on production databases during a planned maintenance window.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.