This article is one of several articles discussing some of the best practices for encrypting data. This article demonstrates how the @add_authenticator argument of the ENCRYPTBYKEY function can help prevent tampering with encrypted data.
Imagine the following scenario: The DBA is encrypting the salary column for all employees in such a way that people with authorization to access the table, but no access to the encryption key can see and manipulate the table, but cannot access the salary in plaintext. Mallory is one such employee, who has SELECT, INSERT & UPDATE on the table as required for her daily job, but no access to the encryption keys protecting the salary column.
CREATE TABLE employees( employee_id int identity primary key, name nvarchar(256), salary_crypt varbinary(8000));
go
CREATE CERTIFICATE cert_demo WITH SUBJECT = 'Encryption demo';
CREATE SYMMETRIC KEY key_employee WITH ALGORITHM = AES_256
ENCRYPTION BY CERTIFICATE cert_demo;
OPEN SYMMETRIC KEY key_employee DECRYPTION BY CERTIFICATE cert_demo;
INSERT INTO employees VALUES ( N'Alice',
ENCRYPTBYKEY( key_guid('key_employee'),
CONVERT(varbinary(100), 50000.00)));
INSERT INTO employees VALUES ( N'Bob',
CONVERT(varbinary(100), 1000.00)));
INSERT INTO employees VALUES ( N'Mallory',
--1 Alice 5000
--2 Bob 1000
--3 Mallory 1000
SELECT employee_id, name, CONVERT(decimal,
DECRYPTBYKEY(salary_crypt)) AS salary FROM employees;
CLOSE SYMMETRIC KEY key_employee;
Go
CREATE USER [mallory] WITHOUT LOGIN;
GRANT UPDATE ON employees TO [mallory];
GRANT SELECT ON employees TO [mallory];
In this scenario, Mallory may not be able to recover the plaintext from anybody else in the company, but she may still be able to modify her own salary. She may not be able to see the salary for Alice, her manager, but she can easily guess that Alice’s salary is higher than her own. What would Mallory do? Simply copy Alice’s salary into her own row.
EXECUTE AS USER = 'mallory';
--Msg 15151, Level 16, State 1, Line 1
--Cannot find the symmetric key 'key_employee', because it does not exist or you do not have permission.
--1 Alice 0x... (Alice’s salary)
--2 Bob 0x... (Bob’s salary)
--3 Mallory 0x... (Mallory’s salary)
SELECT * FROM employees;
-- Mallory can copy Alice’s salary into her own row
-- Alice's ID = 1
-- Mallory's ID = 3
DECLARE @ceo_grade_salary varbinary(8000);
SELECT @ceo_grade_salary = salary_crypt FROM employees WHERE employee_id = 1;
UPDATE employees SET salary_crypt = @ceo_grade_salary WHERE employee_id = 3;
--3 Mallory 0x... (Alice’s salary)
-- Looks like a successful attack at a glance…
REVERT;
If the ciphertext for salary was created without any form of integrity check that takes into account the context in which the value is meaningful (i.e. it hasn’t been copied from one row to another), mallory’s attack may be successful.
-- ... and it was indeed a successful attack!
--
--3 Mallory 5000
In order to prevent these kind of attacks using SQL Server encryption built-ins, the application developer may make use of the @add_authenticator parameter set to 1 and set the @authenticator parameter to a unique-per-row, immutable value such as the employee ID in this example (which also happens to be the primary key in this case).
DROP TABLE employees;
CREATE TABLE employees( employee_id int identity primary key,
name nvarchar(256), salary_crypt varbinary(8000));
-- This time we will use the employee ID as
-- @authenticator for the encryption field
-- Given the simplicity of the nature of this demo, I will create the
-- rows first (to populate the ID) and add the salaries later
INSERT INTO employees VALUES ( N'Alice', null);
INSERT INTO employees VALUES ( N'Bob', null);
INSERT INTO employees VALUES ( N'Mallory', null);
-- Update each salary using the employee_id as @authenticator
UPDATE employees SET salary_crypt =
CONVERT(varbinary(100), 5000.00),
1, CONVERT(varbinary(8000), employee_id))
WHERE employee_id = 1;
CONVERT(varbinary(100), 1000.00),
WHERE employee_id = 2;
WHERE employee_id = 3;
DECRYPTBYKEY(salary_crypt, 1, CONVERT(varbinary(8000), employee_id))) AS salary FROM employees;
When using the @add_authenticator = 1 parameter during encryption, the @athenticator value is going to be used along to the plaintext to generate a hash (SHA-1) that is going to be verified during decryption. If the value for @authenticator specified during the decryption call cannot generate a matching hash (or not specified at all) the decryption call will fail and return NULL.
-- Assuming the same attack as before
-- Did Mallory succeeded this time?
-- ... no, she didn’t! She got a null salary this time,
-- This result would be a good indication of tampering
--3 Mallory null
DECRYPTBYKEY(salary_crypt, 1, CONVERT(varbinary(8000), employee_id)))
AS salary FROM employees;
I hope this information helps.
-Raul
Special thanks to Jack Richins & Rick Byham for their feedback while writing this article.
The implementation of RC4/RC4_128 in SQL Server does not salt the key and this severely weakens the security of data that is encrypted using the RC4/RC4_128 algorithm.
In cryptography, an initialization vector (IV) is a fixed size input to a cryptographic algorithm that is typically required to be random or pseudorandom. Salting of cipher keys makes sure that the encryption algorithm always uses a randomized (IV) value. This leads to the following properties of the cipher-text data -
Figure: Encryption without salted keys
Figure: Encryption with salted keys
Since SQL Server does not salt RC4 or RC4_128 keys, similar data that is encrypted by using the same RC4/RC4_128 key repeatedly will result in the same cipher-text output.
Let us understand the implications of using the RC4 or RC4_128 cipher with the help of an example:
--Step (1) : Create a database testDB
CREATE DATABASE testDB;
USE testDB
GO
--Step (2) : Create an RC4 symmetric key object protected by a password in testDB
CREATE SYMMETRIC KEY sym_key_RC4
WITH ALGORITHM = RC4
ENCRYPTION BY PASSWORD = 'SomeStr0ngPassword';
--Step (3) : Open the RC4 key to use for encryption
OPEN SYMMETRIC KEY sym_key_RC4 DECRYPTION BY PASSWORD = 'SomeStr0ngPassword';
--Step (4) : Experiment using the RC4 cipher to encrypt data
SELECT encryptbykey(key_guid('sym_key_RC4'), 'abc');
-- Output : 0x0053ED707ACDC54F83C4B273B29D819B01000000EADEA0D236B4D17BF321EB
--Step (5) : Close the RC4 symmetric key
CLOSE SYMMETRIC KEY myRC4;
Notice that when data ‘abc’ is encrypted by using the RC4 symmetric key ‘sym_key_RC4’, the output representing the cipher-text is identical both times. It might appear intuitive to solve this problem by programmatically adding different salt values such as ‘0123456789ABCDEF’ and ‘FED6753925243232’ through the application code as shown below –
SELECT encryptbykey(key_guid('sym_key_RC4'), '0123456789ABCDEFabcdefg' );
--Output :
0x00E53ACDE34CAE4BA2140D6A246F6CBC 01000000 A6FC3B9FB44D4CC1 A8575A5FD06AFFA42FFCBD2DCF68F3F0 89FD6BC5947987
SELECT encryptbykey(key_guid('sym_key_RC4'), 'FED6753925243232abcdefg' );
0x00E53ACDE34CAE4BA2140D6A246F6CBC 01000000 A6FC3B9FB44D4CC1 DE232C5AD36AFAAA25F0CE5BBF1E8584 89FD6BC5947987
However, because RC4/RC4_128 is a stream cipher, the additional salt does not help hide patterns across multiple usages of the key.
To detect the use of RC4/RC4_128 symmetric key objects, users can use the Microsoft Best Practices Analyzer tool for SQL Server 2008 R2 [2], Policy Based Management [3] or directly query the sys.symmetric_keys catalog view using the query below –
SELECT * FROM sys.symmetric_keys
WHERE algorithm_desc = 'RC4'
OR algorithm_desc = 'RC4_128';
To mitigate this problem, developers are advised to use stronger cipher algorithms such as the AES family of algorithms for protecting sensitive data as shown in the example below –
--Step (1) : Create an AES_256 symmetric key object protected by a password
CREATE SYMMETRIC KEY sym_key_aes256
WITH ALGORITHM = AES_256
--Step (2) : Open the AES-256 key to use for encryption
OPEN SYMMETRIC KEY sym_key_aes256 DECRYPTION BY PASSWORD = 'SomeStr0ngPassword';
--Step (3) : Experiment using the AES-256 cipher to encrypt data
SELECT encryptbykey(key_guid('sym_key_aes256'), 'abc');
-- Output call 1: 0x0067F1EFBC6DE347AB1C383CD1E1CBA801000000D95D9B9257F15A5B3F32EC8E2B11FB66B5EF589B240E31F72FA832BFF67BAE7A
-- Output call 2: 0x0067F1EFBC6DE347AB1C383CD1E1CBA80100000065A91373165552336A88CA70B6E6FFC61E84152D93BFCD834DD6F965DF22B475
--Step (4): Close the AES-256 symmetric key
CLOSE SYMMETRIC KEY sym_key_aes256;
Additional links –
[1] Why encryption should be salted?
http://blogs.msdn.com/b/lcris/archive/2006/05/08/why-encryption-should-be-salted-and-a-small-c-demo.aspx
[2] Microsoft SQL Server 2008 R2 Best Practices Analyzer
http://www.microsoft.com/downloads/en/details.aspx?displaylang=en&FamilyID=0fd439d7-4bff-4df7-a52f-9a1be8725591
[3] Policy Based Management How-To’s
http://technet.microsoft.com/en-us/library/bb510408.aspx
[4] EncryptByKey Cryptographic Message Description
http://blogs.msdn.com/b/sqlsecurity/archive/2009/03/29/sql-server-encryptbykey-cryptographic-message-description.aspx
---
Thanks to folks from the SQL Server Core Security Team for their feedback.
Don Pinto - SQL Server Engine