Sharing of thoughts and information is what blogging is all about. This way we can learn from each other. Post A Comment!These postings are provided "AS IS" with no warranties, and confers no rights. You assume all risk for your use.
Anthony Bartolo Twitter | LinkedIn
Pierre Roman Twitter | LinkedIn
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.
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
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;
-- create a simple employee table
create table t_employees (
id int primary key,
-- 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
-- see the result, the decrypted data for Bob is no longer available
-- because it doesn't match the authenticator, which is his employee id
-- now close the key
close symmetric key sk_employees;
-- verify key was closed
-- 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
drop view v_employees;
delete from t_employees;
drop table t_employees;
drop symmetric key sk_employees;
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!
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.