Last week you learned how to setup the encryption key hierarchy in SQL Server.  Now, let’s use encryption to encrypt sensitive data in SQL Server.

 

It is quite possible that you might have sensitive data that needs encryption at a finer level of detail than the entire database. Most of the row might need to be visible to users, while certain sensitive information such as employee salary might require encryption. You might also want the ability for certain users to be able to encrypt/decrypt certain cells as shown in figure 1.

 

The solution is to use cell-level encryption in SQL Server.

 

 

clip_image001

Figure 1: Cell-level encryption with 2 different users and keys on a data table

With cell-level encryption in SQL Server, it is possible to encrypt data in individual cells within a table.

 

BENEFITS OF USING CELL-LEVEL ENCRYPTION:

(1)    Granular, user specific control on encrypting individual cells or column values rather than entire databases (compared to using Transparent Data Encryption – TDE).

(2)    Data retains its encrypted state in memory unless it is actively decrypted.

 

DRAWBACKS OF USING CELL-LEVEL ENCRYPTION:

(1)    Requires application changes and analysis of tables to locate sensitive data that needs to be encrypted.

(2)    Encryption of data introduces randomization. This makes it impossible to index data and causes a performance impact since indexes on encrypted columns cannot be used while searching for a value.

(3)    Cell-level encryption built-in functions only return varbinary type data and the output is limited to up to 8000 bytes.

 

 

IMPORTANT BUILT-INS FOR CELL-LEVEL ENCRYPTION

ENCRYPTION

·         ENCRYPTBYKEY

·         ENCRYPTBYCERT

·         ENCRYPTBYPASSPHRASE

·         ENCRYPTBYASYMKEY

 

DECRYPTION

·         DECRYPTBYKEY

·         DECRYPTBYCERT

·         DECRYPTBYPASSPHRASE

·         DECRYPTBYASYMKEY

·         DECRYPTBYKEYAUTOASYMKEY

·         DECRYPTBYKEYAUTOCERT

 

WHAT IF SOMEONE TAMPERS WITH ENCRYPTED DATA?

You can now mitigate this risk by using the @add_authenticator and @authenticator arguments of the cell-level encryption built-in’s.

 

Refer to this blog post to learn how.

 

 

HOW TO USE CELL-LEVEL ENCRYPTION?

-- Show how a column can be encrypted and decrypted and how an authenticator value can be used

 

create database demo;

use demo;

 

-- create a simple employee table

create table t_employees (

id int primary key,

name varchar(300),

salary varbinary(300));

 

-- create a key to protect the employee sensitive data, in this case - the salary

create symmetric key sk_employees with algorithm = aes_192 encryption by password = '1Str0ngPassword';

 

-- open the key so that we can use it

open symmetric key sk_employees decryption by password = 1Str0ngPassword';

 

-- verify key was opened

select * from sys.openkeys;

 

-- insert some data

-- we will use the id as an authenticator value to tie the salary to the employee id

insert into t_employees values (101, 'Alice Smith', encryptbykey(key_guid('sk_employees'), '$200000', 1, '101'));

insert into t_employees values (102, 'Bob Jones', encryptbykey(key_guid('sk_employees'), '$100000', 1, '102'));

 

-- see the result; salary is encrypted

select * from t_employees;

 

-- create a view to automatically do the decryption

-- note that when decrypting we specify that the id should be used as authenticator

create view v_employees as select id, name, convert(varchar(10), decryptbykey(salary, 1, convert(varchar(30), id))) as salary from t_employees;

 

-- see the result, the decrypted data is available

select * from v_employees;

 

-- demo the authenticator role

-- copy salary of Alice and overwrite the value for Bob

-- execute next 3 lines as batch

declare @salary varbinary(300);

select @salary = salary from t_employees where id = 101;

update t_employees set salary = @salary where id = 102;

 

-- note that both entries have the same salary blob

select * from t_employees;

 

-- see the result, the decrypted data for Bob is no longer available

-- because it doesn't match the authenticator, which is his employee id

select * from v_employees;

 

-- now close the key

close symmetric key sk_employees;

 

-- verify key was closed

select * from sys.openkeys;

 

-- see the result, we can no longer decrypt any data because the key is closed

-- to access the data again we would need to reopen the key

select * from v_employees;

 

-- cleanup

drop view v_employees;

delete from t_employees;

drop table t_employees;

drop symmetric key sk_employees;

 

use master;

 

drop database demo;

 

OOPS – MORE THAN 8000 BYTES OF CIPHER-TEXT!

Because there is a hard 8000 byte limit on the output of built-in’s for cell-level encryption, your application will need to slice the input before encrypting it!

 

Refer to this blog post to learn how.

 

WORRIED ABOUT INDEXING YOUR ENCRYPTED DATA?

Because the cell-level encryption built-in functions are nondeterministic, which means that every time a function is called, the output will be different, indexing encrypted data is not possible.  However this problem can be solved by using hashes or MAC’s of the plain-text for indexing purposes.

 

Refer to this blog post to learn how.

 

DamirB-BlogSignature