Types of Database Encryption in SQL Server

SQL Server offers several encryption options to protect sensitive data. Here are the main types of database encryption in SQL Server:

Transparent Data Encryption (TDE)

Transparent Data Encryption (TDE) is a feature introduced in SQL Server 2008 that encrypts the entire database at the file level. It protects data at rest, including data and log files, without requiring changes to the application. TDE uses a database encryption key (DEK) to encrypt the data files and a server certificate or asymmetric key to protect the DEK.

To enable TDE:

1. Create a database master key and a certificate protected by the master key
2. Create a database encryption key (DEK) protected by the certificate
3. Set the database to use encryption

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'strong_password';
CREATE CERTIFICATE MyServerCert WITH SUBJECT = 'My Database Encryption Key Certificate'; 
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE MyServerCert;
ALTER DATABASE MyDatabase
SET ENCRYPTION ON;

Always Encrypted

Always Encrypted is a feature introduced in SQL Server 2016 that allows client applications to encrypt sensitive data and never reveal the encryption keys to SQL Server. It protects data at rest and in motion. Always Encrypted supports deterministic and randomized encryption modes.

To use Always Encrypted:

1. Configure the application to use Always Encrypted
2. Create encrypted columns in the database
3. Use SqlParameter objects to pass encrypted data to SQL Server

string connString = "Server=database.example.com;Database=Clinic;Column Encryption Setting=enabled";
using (SqlConnection conn = new SqlConnection(connString)) {
    SqlCommand cmd = conn.CreateCommand();
    cmd.CommandText = "SELECT FirstName, LastName, BirthDate FROM Patients WHERE SSN = @SSN";
    SqlParameter paramSSN = cmd.CreateParameter();
    paramSSN.ParameterName = "@SSN";
    paramSSN.Value = "795-73-9838";
    cmd.Parameters.Add(paramSSN);
    using (SqlDataReader reader = cmd.ExecuteReader()) {
        while (reader.Read()) {
            Console.WriteLine("{0}, {1}, {2}", reader[0], reader[1], ((DateTime)reader[2]).ToShortDateString());
        }
    }
}

Column-level Encryption

Column-level encryption allows you to encrypt individual columns in a table using symmetric keys. It provides a higher level of granularity compared to TDE. Column-level encryption uses the EncryptByKey and DecryptByKey functions.

To encrypt a column:

1. Create a database master key and a symmetric key
2. Open the symmetric key
3. Encrypt the column data using EncryptByKey
4. Close the symmetric key

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'strong_password';
CREATE SYMMETRIC KEY SymKey_test WITH ALGORITHM = AES_256 ENCRYPTION BY MASTER KEY;

OPEN SYMMETRIC KEY SymKey_test DECRYPTION BY MASTER KEY;

UPDATE CustomerData.dbo.CustomerInfo
SET BankACCNumber_encrypt = EncryptByKey(Key_GUID('SymKey_test'), BankACCNumber);

CLOSE SYMMETRIC KEY SymKey_test;

In summary, SQL Server offers several encryption options to protect sensitive data at different levels - from encrypting the entire database to masking individual columns. The choice depends on the specific security requirements and performance considerations.

Reference URL :

https://github.com/MicrosoftDocs/sql-docs/blob/live/docs/relational-databases/security/encryption/sql-server-encryption.md https://techdocs.broadcom.com/us/en/ca-enterprise-software/business-management/ca-service-management/17-3/administering/administering-ca-service-management/transparent-data-encryption-for-ca-service-management/enabling-transparent-data-encryption-for-microsoft-sql-server-database.html