Home > MSDN > Documentation > SQL Server > Security > Encryption

SQL Server Security: Encryption

This section provides comprehensive documentation on implementing and managing encryption features within Microsoft SQL Server. Secure your sensitive data by understanding and utilizing the various encryption capabilities available.

Table of Contents

Introduction to SQL Server Encryption

Data encryption is a critical component of a robust security strategy for SQL Server. It helps protect data from unauthorized access, both at rest (stored on disk) and in transit (moving across a network). SQL Server offers several layers and types of encryption to address various security needs.

Transparent Data Encryption (TDE)

Transparent Data Encryption (TDE) encrypts SQL Server data files (.mdf, .ndf) and log files (.ldf) at rest. This protects the physical data files from being read if the underlying storage is stolen or accessed inappropriately. TDE works by encrypting the data at the page level before it is written to disk and decrypting it when it is read into memory.

Key aspects of TDE:

For detailed steps on implementing TDE, refer to the official TDE documentation.

Using Certificates for Encryption

Certificates are fundamental to SQL Server's encryption capabilities, particularly for protecting encryption keys. You can create self-signed certificates within SQL Server or use certificates issued by a Certificate Authority (CA).

-- Example: Creating a self-signed certificate
CREATE CERTIFICATE MyDatabaseCert
   WITH SUBJECT = 'My Database Encryption Certificate',
        EXPIRY_DATE = '2025-12-31';

Symmetric Key Encryption

Symmetric keys use the same key for both encryption and decryption. They are generally faster than asymmetric keys, making them suitable for encrypting large amounts of data. Symmetric keys in SQL Server are often protected by certificates or asymmetric keys.

-- Example: Creating a symmetric key
CREATE SYMMETRIC KEY MySymmetricKey
   WITH PASSWORD = 'VeryStrongPassword123!';

-- Encrypting data with a symmetric key
OPEN SYMMETRIC KEY MySymmetricKey DECRYPTION BY PASSWORD = 'VeryStrongPassword123!';
SELECT EncryptBySymmetricKey(CERT_ID('MyDatabaseCert'), 'MySensitiveData') AS EncryptedData;
CLOSE SYMMETRIC KEY MySymmetricKey;

Asymmetric Key Encryption

Asymmetric keys use a pair of keys: a public key for encryption and a private key for decryption. This is useful for securely sharing keys or for signing data. The private key must be kept highly secure.

-- Example: Creating an asymmetric key
CREATE ASYMMETRIC KEY MyAsymmetricKey
   WITH ALGORITHM = 'RSA_512';

-- Encrypting data with an asymmetric key
OPEN ASYMMETRIC KEY MyAsymmetricKey;
SELECT EncryptByAsymmetricKey(AsymmetricKey_ID('MyAsymmetricKey'), 'MySensitiveData') AS EncryptedData;
CLOSE ASYMMETRIC KEY MyAsymmetricKey;

Column-Level Encryption

Encrypt specific columns within tables to protect sensitive information, such as credit card numbers or personal identification details. This can be achieved using symmetric or asymmetric keys with functions like EncryptBySymmetricKey and EncryptByAsymmetricKey.

Considerations for Column-Level Encryption:

Dynamic Data Masking

Dynamic Data Masking (DDM) limits sensitive data exposure by masking it to non-privileged users. DDM doesn't change the actual data in the database; rather, it transforms the data returned in a query result. This is a presentation-layer security feature.

-- Example: Masking an email column
ALTER TABLE dbo.Customers
ALTER COLUMN Email ADD MASKED WITH (FUNCTION = 'email()');

Best Practices for Data Encryption

‹ Previous: Auditing Next: Permissions ›