Data Encryption in SQL Server (ADO.NET)
SQL Server 2005 provides functions to encrypt and decrypt data using a certificate, asymmetric key, or symmetric key. It manages all of these in an internal certificate store. The store uses an encryption hierarchy that secures certificates and keys at one level with the layer above it in the hierarchy. This feature area of SQL Server 2005 is called Secret Storage.
The fastest mode of encryption supported by the encryption functions is symmetric key encryption. This mode is suitable for handling large volumes of data. The symmetric keys can be encrypted by certificates, passwords or other symmetric keys.
Keys and Algorithms
SQL Server 2005 supports several symmetric key encryption algorithms, including DES, Triple DES, RC2, RC4, 128-bit RC4, DESX, 128-bit AES, 192-bit AES, and 256-bit AES. The algorithms are implemented using the Windows Crypto API.
Within the scope of a database connection, SQL Server 2005 can maintain multiple open symmetric keys. An open key is retrieved from the store and is available for decrypting data. When a piece of data is decrypted, there is no need to specify the symmetric key to use. Each encrypted value contains the key identifier (key GUID) of the key used to encrypt it. The engine matches the encrypted byte stream to an open symmetric key, if the correct key has been decrypted and is open. This key is then used to perform decryption and return the data. If the correct key is not open, NULL is returned.
For an example that shows how to work with encrypted data in a database, see How to: Encrypt a Column of Data in SQL Server Books Online.
External Resources
For more information on data encryption, see the following resources.
SQL Server Encryption in SQL Server 2008 Books Online |
Provides an overview of encryption in SQL Server 2008. This topic includes links to additional topics and how-to's. |
Encryption Hierarchy and Encryption How-to Topics in SQL Server 2005 Books Online |
Provides an overview of encryption in SQL Server 2005. This topic provides links to additional topics and how-to's. |
Using Encryption and Obfuscation in SQL Server 2000 Books Online |
Describes SQL Server 2000 encryption options. SQL Server 2000 can optionally use the Secure Sockets Layer (SSL) to encrypt all data transmitted between an application computer and an instance of SQL Server. |
See Also
Concepts
Application Security Scenarios in SQL Server (ADO.NET)
Authentication in SQL Server (ADO.NET)
Server and Database Roles in SQL Server (ADO.NET)
Ownership and User-Schema Separation in SQL Server (ADO.NET)
Authorization and Permissions in SQL Server (ADO.NET)