Choosing 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 2005 allows administrators and developers to choose from among several algorithms, including DES, Triple DES, RC2, RC4, 128-bit RC4, DESX, 128-bit AES, 192-bit AES, and 256-bit AES.

Note

AES encryption is not supported on Windows XP or Windows Server 2000.

No one 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 stronger 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.

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.
  • Symmetric keys created with ALGORITHM = TRIPLE_DES use TRIPLE DES with a 128-bit key.

See Also

Other Resources

Encryption Hierarchy
CREATE SYMMETRIC KEY (Transact-SQL)
CREATE ASYMMETRIC KEY (Transact-SQL)
CREATE CERTIFICATE (Transact-SQL)
Encryption How-to Topics

Help and Information

Getting SQL Server 2005 Assistance

Change History

Release History

14 April 2006

New content:
  • Added notice about AES availability.

17 November 2008

New content:
  • Added clarification about the DES algorithms.