Choose an Encryption Algorithm
Encryption is one of several defenses-in-depth that are available to the administrator who wants to secure an instance of SQL Server.
Encryption algorithms define data transformations that cannot be easily reversed by unauthorized users. SQL Server allows administrators and developers to choose from among several algorithms, including DES, Triple DES, TRIPLE_DES_3KEY, RC2, RC4, 128-bit RC4, DESX, 128-bit AES, 192-bit AES, and 256-bit AES.
No single algorithm is ideal for all situations, and guidance on the merits of each is beyond the scope of SQL Server Books Online. However, the following general principles apply:
Strong encryption generally consumes more CPU resources than weak encryption.
Long keys generally yield stronger encryption than short keys.
Asymmetric encryption is weaker than symmetric encryption using the same key length, but it is relatively slow.
Block ciphers with long keys are stronger than stream ciphers.
Long, complex passwords are stronger than short passwords.
If you are encrypting lots of data, you should encrypt the data using a symmetric key, and encrypt the symmetric key with an asymmetric key.
Encrypted data cannot be compressed, but compressed data can be encrypted. If you use compression, you should compress data before encrypting it.
Important
The RC4 algorithm is only supported for backward compatibility. New material can only be encrypted using RC4 or RC4_128 when the database is in compatibility level 90 or 100. (Not recommended.) Use a newer algorithm such as one of the AES algorithms instead. In SQL Server 2012 and higher material encrypted using RC4 or RC4_128 can be decrypted in any compatibility level.
Repeated use of the same RC4 or RC4_128 KEY_GUID on different blocks of data will result in the same RC4 key because SQL Server does not provide a salt automatically. Using the same RC4 key repeatedly is a well-known error that will result in very weak encryption. Therefore, we have deprecated the RC4 and RC4_128 keywords. This feature will be removed in a future version of Microsoft SQL Server. Do not use this feature in new development work, and modify applications that currently use this feature as soon as possible.
For more information about encryption algorithms and encryption technology, see Key Security Concepts in the .NET Framework Developer's Guide on MSDN.
Clarification regarding DES algorithms:
DESX was incorrectly named. Symmetric keys created with ALGORITHM = DESX actually use the TRIPLE DES cipher with a 192-bit key. The DESX algorithm is not provided. This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.
Symmetric keys created with ALGORITHM = TRIPLE_DES_3KEY use TRIPLE DES with a 192-bit key.
Symmetric keys created with ALGORITHM = TRIPLE_DES use TRIPLE DES with a 128-bit key.
Related Tasks
| Encrypting using a symmetric key. | CREATE SYMMETRIC KEY (Transact-SQL) |
| Encrypting using an asymmetric key. | CREATE ASYMMETRIC KEY (Transact-SQL) |
| Encrypting using a certificate. | CREATE CERTIFICATE (Transact-SQL) |
| Encrypting database files using transparent data encryption. | Transparent Data Encryption (TDE) |
| How to encrypt one column of a table. | Encrypt a Column of Data |