SQL Server Tips ----- CELL-LEVEL ENCRYPTION IN SQL SERVER

Now that you know howto setup the encryption key hierarchy based on the last security tips andtricks post, let us use encryption to encrypt sensitive data in SQL Server.

 

It is quite possible that you might have sensitive data that needsencryption at a finer level of detail than the entire database. Most of the rowmight need to be visible to users, while certain sensitive information such asemployee salary might require encryption. You might also want the ability forcertain 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.

 

 

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

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

 

BENEFITS OF USING CELL-LEVEL ENCRYPTION:

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

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

 

DRAWBACKS OF USING CELL-LEVEL ENCRYPTION:

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

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

Cell-levelencryption built-in functions only return varbinary type data and the output islimited 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.

 

-- 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.