First published on MSDN on Jun 04, 2015
Updates:
The recently released SQL Server 2016 Community Technology Preview 2 introduced Always Encrypted , a new security feature that ensures sensitive data is never seen in plaintext in a SQL Server instance. Always Encrypted works by transparently encrypting the data in the application, so that SQL Server will only handle the encrypted data and not plaintext values. Even if the SQL instance or the host machine is compromised, all an attacker can get is ciphertext of sensitive data.
We will begin a series of articles on Always Encrypted with a simple example of the technology that can help everyone to get started. We will show how to develop a simple console application that uses Always Encrypted to protect patient information stored in a database.
For this example, you will need to install the following:
For this simple example, we will perform the following steps using SSMS (SQL Server Management Studio) on the development machine:
a) Create a new database named Clinic .
b) Using Object Explorer , locate and open the Always Encrypted Keys folder under Security for your database. Right-click on Column Master Key and select New Column Master Key …. This will open a dialog which you will use to define a column master key for your database. The easiest option for developing new apps using Always Encrypted is to use a certificate, stored in your personal Certificate Store, as a column master key.
c) Simply, enter CMK1 as a name of your column master key, click Generate Self-Signed Certificate , and click OK . This will generate a self-signed certificate, put it in your personal store ( Certificate Store: Current User ), and create a definition of the column master key in the database.
To generate a column encryption key that will be used to encrypt sensitive data in the database, right-click on the Column Encryption Keys folder, select New Column Encryption Key , enter CEK1 as a key name and select CMK1 as an encrypting column master key for your new column encryption key. Once you click OK , a new column encryption key gets created, encrypted with the certificate you configured in step 1, and the encrypted value is uploaded to the database.
Using a New Query window in SSMS, issue the following statement:
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
[Updated] Please notice that the schema for our table has changed to reflect best practices. The following is a summary of the changes:
SSN changed to char(11) as this is not a variable-length field. No need for this field to be nchar either as SSN will not contain any Unicode characters.
ZIP code changed to char(5) as there are certain ZIP codes that start with 0s, and therefore an integer will not represent them correctly.
State has changed to 2 char characters also notice that Unicode is not needed.
Changed Birthdate from datetime2 to date as the time and precision are not suitable for this field.
Once again, thanks a lot to Denny Cherry for his feedback on how to improve our schema.
The above T-SQL creates the Patients table with two encrypted columns: SSN and BirthDate . SSN is configured to be encrypted using deterministic encryption, which supports equality lookups, joins and group by. BirthDate is encrypted using randomized encryption, which does not support any operations, but that is ok, as the app is not going to perform any computations on the BirthDate column.
Now that we have the Always Encrypted keys and the schema configured, we can create a small application that will be able to insert data into the Patients table & query it.
In Visual Studio, we will create a new console application using C#. Since the SqlClient enhancements to support Always Encrypted were introduced in .Net Framework 4.6, we need to ensure the application is using the right version of the framework. Right click on the project, select Properties , then go to the Application tab, and make sure that Target Framework option is set to “.Net Framework 4.6”.
Next we will add very simple code that connects to the database, inserts and selects data using SqlClient . You will notice that the only change required to use Always Encrypted is including “Column Encryption Setting=Enabled;” in the connection string. The complete code is included as a file attachment.
For this exmple, we are enabling the Column Encryption Setting in the connection string using a SqlConnectionStringBuilder object and setting SqlConnectionStringBuilder.ColumnEncryptionSetting to Enabled … and that’s pretty much it.
strbldr.ColumnEncryptionSetting = SqlConnectionColumnEncryptionSetting.Enabled;
Something to remark is that in order to send values that will correspond to encrypted columns, you need to use SqlParameter class. It is not possible to use literals to pass such values.
cmd.CommandText = @"INSERT INTO [dbo].[Patients] ([SSN], [FirstName], [LastName], [BirthDate]) VALUES (@SSN, @FirstName, @LastName, @BirthDate);";
SqlParameter paramSSN = cmd.CreateParameter();
paramSSN.ParameterName = @"@SSN";
paramSSN.DbType = DbType.AnsiStringFixedLength;
paramSSN.Direction = ParameterDirection.Input;
paramSSN.Value = ssn;
paramSSN.Size = 11;
cmd.Parameters.Add(paramSSN);
…
SqlParameter paramBirthdate = cmd.CreateParameter();
paramBirthdate.ParameterName = @"@BirthDate";
paramBirthdate.SqlDbType = SqlDbType.Date;
paramBirthdate.Direction = ParameterDirection.Input;
paramBirthdate.Value = birthdate;
cmd.Parameters.Add(paramBirthdate);
cmd.ExecuteNonQuery();
…
cmd.CommandText = @"SELECT [SSN], [FirstName], [LastName], [BirthDate] FROM [dbo].[Patients] WHERE [SSN] = @SSN;";
SqlParameter paramSSN = cmd.CreateParameter();
paramSSN.ParameterName = @"@SSN";
paramSSN.DbType = DbType.AnsiStringFixedLength;
paramSSN.Direction = ParameterDirection.Input;
paramSSN.Value = ssn;
paramSSN.Size = 11;
cmd.Parameters.Add(paramSSN);
SqlDataReader reader = cmd.ExecuteReader();
At this point many readers may be doubting we really encrypted anything, after all, the application seems to be simply handling plaintext as naturally as before; so, how can we verify that the data was properly encrypted?
We can use SSMS query for that. If we simply select our table, you will notice that the SSN & BirthDate columns seem to be displaying binary data.
As we have seen, Always Encrypted transparently encrypts/decrypts sensitive data in the application as long as the application has access to the certificate acting as a CMK. Users and applications without access to the CMK, including SQL Server itself, will not be able to decrypt the sensitive data.
If you connect SQL Profiler to the database while running this application, you will notice that SQL Server will receive data corresponding to the encrypted values only as ciphertext, never as plaintext.
Special thanks the Always Encrypted team for their help writing this article.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.