Revisiting the RC4 / RC4_128 Cipher
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 -
- Encrypting the same piece of data two times by using the same key will produce two different cipher-text values. For example, a table might have a column value appearing multiple times. When encrypted, a user cannot recognize the presence of similar plain-text values by just comparing the cipher-text values.
- This mechanism adds additional protection against cryptanalysis 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
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' );
--Output :
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
ENCRYPTION BY PASSWORD = 'SomeStr0ngPassword';
--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
SELECT encryptbykey(key_guid('sym_key_aes256'), 'abc');
-- 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?
[2] Microsoft SQL Server 2008 R2 Best Practices Analyzer
[3] Policy Based Management How-To’s
https://technet.microsoft.com/en-us/library/bb510408.aspx
[4] EncryptByKey Cryptographic Message Description
---
Thanks to folks from the SQL Server Core Security Team for their feedback.
Don Pinto - SQL Server Engine