Events
Mar 31, 11 PM - Apr 2, 11 PM
The biggest SQL, Fabric and Power BI learning event. March 31 – April 2. Use code FABINSIDER to save $400.
Register todayThis browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
Applies to:
SQL Server
Azure SQL Database
Azure SQL Managed Instance
Azure Synapse Analytics
Analytics Platform System (PDW)
Encryption is one of several defenses available to the administrator who wants to secure an instance of SQL Server.
Encryption algorithms define data transformations that can't be easily reversed by unauthorized users. Administrators and developers can choose from among several algorithms in SQL Server, including DES, Triple DES, TRIPLE_DES_3KEY, RC2, RC4, 128-bit RC4, DESX, 128-bit AES, 192-bit AES, and 256-bit AES.
Beginning with SQL Server 2016 (13.x), all algorithms other than AES_128
, AES_192
, and AES_256
are deprecated. To use older algorithms (not recommended), you must set the database to database compatibility level 120 or lower.
No single algorithm is ideal for all situations, and guidance on the merits of each is beyond the scope of this article. 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 slower than symmetric encryption.
Long, complex passwords are stronger than short passwords.
Symmetric encryption is recommended when the key is only stored locally. Asymmetric encryption is recommended when keys need to be shared across the wire.
If you're encrypting lots of data, you should encrypt the data using a symmetric key, and encrypt the symmetric key with an asymmetric key.
Encrypted data can't 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.
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 (11.x) and later versions, 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 results in the same RC4 key because SQL Server doesn't provide a salt automatically. Using the same RC4 key repeatedly is a well-known error that results in weak encryption. Therefore, we have deprecated the RC4
and RC4_128
keywords. This feature will be removed in a future version of SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.
DESX was incorrectly named. Symmetric keys created with ALGORITHM = DESX
actually use the Triple DES cipher with a 192-bit key. The DESX algorithm isn't provided. This feature will be removed in a future version of 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.
Events
Mar 31, 11 PM - Apr 2, 11 PM
The biggest SQL, Fabric and Power BI learning event. March 31 – April 2. Use code FABINSIDER to save $400.
Register todayTraining
Certification
Microsoft Certified: Azure Database Administrator Associate - Certifications
Administer an SQL Server database infrastructure for cloud, on-premises and hybrid relational databases using the Microsoft PaaS relational database offerings.
Documentation
Encryption hierarchy - SQL Server
Learn about the hierarchical encryption and key management infrastructure in SQL Server. Store keys in an Extensible Key Management module.
SQL Server encryption - SQL Server
Use these resources to understand how SQL Server uses encryption to enhance security for your databases.
Learn how to encrypt a column of data by using symmetric encryption in SQL Server using Transact-SQL, sometimes known as column-level or cell-level encryption.