top of page
Learn T-SQL

SQL Encryption

SQL Server Encryption is a feature in Microsoft SQL Server that enables you to encrypt data to protect it from unauthorized access. SQL Server offers several encryption options, including cell-level encryption, Transparent Data Encryption (TDE), and backup encryption. Cell-level encryption allows you to encrypt individual columns of data within a table, and it offers a higher level of security than database-level encryption. TDE encrypts the entire database and its backups, making it more suitable for larger databases that require protection at rest. Backup encryption is used to encrypt SQL Server backups, protecting them from unauthorized access. SQL Server Encryption uses symmetric encryption algorithms, such as Advanced Encryption Standard (AES) and Triple Data Encryption Standard (3DES), to encrypt the data. The encryption key is then protected by a certificate or an asymmetric key, which is stored in the SQL Server master database. The certificate or asymmetric key is then encrypted by the database master key, which is generated automatically when the SQL Server instance is installed. SQL Server Encryption is an essential component of any data security strategy, as it helps to ensure that sensitive data is protected from unauthorized access. However, implementing SQL Server Encryption requires careful planning and consideration of performance implications, as encrypting and decrypting data can affect the performance of database operations.


SQL Server Encryption is available in several editions of Microsoft SQL Server, but the availability of specific encryption features may vary by edition. Here is a summary of SQL Server Encryption features available in each edition:

  • Enterprise Edition: This edition includes all of the encryption features available in SQL Server, including Transparent Data Encryption (TDE), Cell-level encryption, backup encryption, Extensible Key Management (EKM), and Always Encrypted.

  • Standard Edition: This edition includes Transparent Data Encryption (TDE), backup encryption, and Always Encrypted.

  • Web Edition: This edition includes Transparent Data Encryption (TDE) and backup encryption.

  • Developer and Express Editions: These editions include Transparent Data Encryption (TDE) and backup encryption.

Note that the availability of SQL Server Encryption features may also depend on the version and service pack level of SQL Server. It's important to review the documentation for your specific version and edition of SQL Server to determine which encryption features are available and how to implement them.


There are differences in encryption features and capabilities among versions of SQL Server. Here are some of the key differences:

  • Transparent Data Encryption (TDE): TDE was introduced in SQL Server 2008 Enterprise Edition and is available in all editions of SQL Server since SQL Server 2016 SP1. Prior to that, it was only available in Enterprise Edition. TDE encrypts the entire database, including data, log files, and backups.

  • Cell-level encryption: Cell-level encryption was introduced in SQL Server 2005 Enterprise Edition and is available in all editions of SQL Server. It allows you to encrypt individual columns of data within a table.

  • Backup encryption: Backup encryption was introduced in SQL Server 2014 and is available in all editions of SQL Server. It allows you to encrypt database backups.

  • Always Encrypted: Always Encrypted was introduced in SQL Server 2016 and is available in Enterprise, Standard, and Developer Editions. It allows you to encrypt sensitive data such as credit card numbers and personally identifiable information (PII) at rest and in transit.

  • Extensible Key Management (EKM): EKM was introduced in SQL Server 2008 Enterprise Edition and is available in all editions of SQL Server. It allows you to store and manage encryption keys in an external hardware security module (HSM).

  • Transport Layer Security (TLS): SQL Server supports different versions of TLS for encrypting network traffic between client applications and the SQL Server instance. TLS 1.2 is the default version starting from SQL Server 2016, while older versions of SQL Server support older versions of TLS.

It's important to note that some encryption features are only available in specific editions of SQL Server. Therefore, it's important to review the documentation for your specific version and edition of SQL Server to determine which encryption features are available and how to implement them.


SQL Server provides various encryption options to secure data and protect it from unauthorized access. Here are the different types of encryption available in SQL Server and their pros and cons:

  • Transparent Data Encryption (TDE): TDE encrypts the entire database and is transparent to applications and end-users. Pros include the ability to protect data at rest and secure backups. However, TDE can have performance overhead and may not protect against all attack vectors.

  • Cell-level encryption: Cell-level encryption encrypts individual columns or cells within a table. Pros include fine-grained control over data access and the ability to encrypt only sensitive data. However, cell-level encryption can have performance overhead, and it may not protect against all attack vectors.

  • Backup encryption: Backup encryption encrypts database backups. Pros include the ability to secure backups and protect against unauthorized access. However, backup encryption can have a performance overhead and may require additional storage space.

  • Always Encrypted: Always Encrypted encrypts data at rest and in transit between the client and server. Pros include the ability to secure sensitive data even when it's in use and the ability to delegate key management to the client. However, Always Encrypted may have a performance overhead and may not be suitable for all types of data.

  • Transport Layer Security (TLS): TLS encrypts data in transit between the client and server. Pros include the ability to secure data in transit and protect against eavesdropping and man-in-the-middle attacks. However, TLS may have a performance overhead and may require additional configuration.

  • Extensible Key Management (EKM): EKM allows you to store and manage encryption keys in an external hardware security module (HSM). Pros include the ability to meet compliance requirements and the ability to protect keys from being compromised. However, EKM may require additional hardware and configuration, and it may not be suitable for all environments.

Overall, the pros and cons of each encryption type will depend on your specific use case, performance requirements, security needs, and compliance requirements. It's important to review the documentation for your specific version and edition of SQL Server and consult with security experts to determine which encryption options are suitable for your environment.


To implement Transparent Data Encryption (TDE) in SQL Server, you can follow these general steps:

  • Create or obtain a database master key (DMK) and a certificate or asymmetric key to protect the TDE encryption key.

  • Create a database encryption key (DEK) for the database that you want to encrypt.

  • Enable TDE for the database by setting the encryption on the database to ON.

Here are more detailed steps:


Create or obtain a database master key (DMK) and a certificate or asymmetric key to protect the TDE encryption key:


USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password'; -- password for the DMK
GO
CREATE CERTIFICATE TDE_Cert WITH SUBJECT = 'TDE certificate'; -- certificate or asymmetric key to protect the TDE encryption key
GO

Create a database encryption key (DEK) for the database that you want to encrypt:

USE [database_name];
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256 -- encryption algorithm
ENCRYPTION BY SERVER CERTIFICATE TDE_Cert; -- certificate or asymmetric key to protect the DEK
GO

Enable TDE for the database by setting the encryption on the database to ON:

USE [database_name];
ALTER DATABASE [database_name] SET ENCRYPTION ON;
GO

After TDE is enabled, all database files, including the data file, log file, and any other filegroups, will be encrypted. If you create a new filegroup after enabling TDE, you'll need to manually encrypt it using the same steps as above.


Note that enabling TDE can have a performance overhead, so it's important to test and monitor the performance of your database after enabling TDE. Also, TDE only protects data at rest, not data in transit or data that is being processed by the application. Therefore, it's important to use other encryption options, such as Always Encrypted or SSL/TLS, to protect data in transit or in use.


To implement Cell-level encryption in SQL Server, you can follow these general steps:

  • Create or obtain a certificate or asymmetric key to protect the column encryption key.

  • Create a column master key (CMK) and a column encryption key (CEK) for each column that you want to encrypt.

  • Alter the table to add the encrypted column and specify the encryption type for the column.

Here are more detailed steps:


Create or obtain a certificate or asymmetric key to protect the column encryption key:

USE master;
CREATE CERTIFICATE Cell_Encryption_Cert WITH SUBJECT = 'Cell-level encryption certificate'; -- certificate or asymmetric key to protect the CEK
GO

Create a column master key (CMK) and a column encryption key (CEK) for each column that you want to encrypt:


USE [database_name];
CREATE COLUMN MASTER KEY Cell_CMK 
WITH (KEY_STORE_PROVIDER_NAME = 'MSSQL_CERTIFICATE_STORE', KEY_PATH = 'Current User/Personal/Cell_Encryption_Cert');
GO

CREATE COLUMN ENCRYPTION KEY Cell_CEK 
WITH VALUES
( 
    COLUMN_MASTER_KEY = Cell_CMK, 
    ALGORITHM = 'RSA_OAEP', 
    ENCRYPTED_VALUE = *****
);
GO

Alter the table to add the encrypted column and specify the encryption type for the column:


USE [database_name];
ALTER TABLE [schema_name].[table_name] ADD [encrypted_column] varbinary(max) ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = Cell_CEK, ENCRYPTION_TYPE = RANDOMIZED, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256');
GO

After cell-level encryption is enabled, only the specified columns will be encrypted, and other columns in the table will remain unencrypted. It's important to note that cell-level encryption can have a performance overhead and may not protect against all attack vectors. It's recommended to use it only for sensitive data and to test and monitor the performance of your database after enabling cell-level encryption.


How to implement Backup encryption


To implement backup encryption in SQL Server, you can follow these general steps:

  • Create or obtain a certificate or asymmetric key to protect the backup encryption key.

  • Create a backup encryption certificate and a backup encryption key for the certificate.

  • Backup the database using the backup encryption certificate and key.

Here are more detailed steps:

Create or obtain a certificate or asymmetric key to protect the backup encryption key:

sql

USE master;
CREATE CERTIFICATE Backup_Encryption_Cert WITH SUBJECT = 'Backup encryption certificate'; -- certificate or asymmetric key to protect the backup encryption key
GO

Create a backup encryption certificate and a backup encryption key for the certificate:


USE master;
CREATE CERTIFICATE Backup_Cert WITH SUBJECT = 'Backup certificate'; -- backup encryption certificate
GO

CREATE SYMMETRIC KEY Backup_Key WITH ALGORITHM = AES_256 -- encryption algorithm
ENCRYPTION BY CERTIFICATE Backup_Cert; -- backup encryption key for the certificate
GO

Backup the database using the backup encryption certificate and key:

BACKUP DATABASE [database_name] TO DISK = 'backup_file_name'WITH INIT, FORMAT, ENCRYPTION (ALGORITHM = AES_256, SERVER CERTIFICATE = Backup_Encryption_Cert), 
COMPRESSION, STATS = 10; -- use the backup encryption certificate and key for encryption
GO

After backup encryption is enabled, the backup file will be encrypted and can only be restored by a user with the appropriate backup encryption certificate and key. It's important to note that backup encryption can have a performance overhead and may increase the size of the backup file. It's recommended to use it only for sensitive data and to test and monitor the performance of your backup process after enabling backup encryption.


Here are the general steps and some T-SQL commands for implementing SQL Server Always Encrypted:

Create a column master key: Use the following T-SQL command to create a column master key:


CREATE COLUMN MASTER KEY [CMK_Name] WITH
(
    KEY_STORE_PROVIDER_NAME = 'MSSQL_CERTIFICATE_STORE',
    KEY_PATH = 'CurrentUser/My/ColumnMasterKeyName'
);

Replace CMK_Name with the name of your column master key, and ColumnMasterKeyName with the name of the certificate you want to use.


Create a column encryption key: Use the following T-SQL command to create a column encryption key:


CREATE COLUMN ENCRYPTION KEY [CEK_Name] WITH VALUES
(
    COLUMN_MASTER_KEY = [CMK_Name],
    ALGORITHM = 'RSA_OAEP',
    ENCRYPTED_VALUE = <EncryptedValue>
);

Replace CEK_Name with the name of your column encryption key, CMK_Name with the name of your column master key, and <EncryptedValue> with the encrypted value of your column encryption key.


Define column encryption settings: Use the following T-SQL command to define column encryption settings:


ALTER TABLE [Table_Name] 
    ALTER COLUMN [Column_Name] [Data_Type] 
    ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK_Name], 
    ENCRYPTION_TYPE = [Encryption_Type]);

Replace Table_Name with the name of your table, Column_Name with the name of your column, Data_Type with the data type of your column, CEK_Name with the name of your column encryption key, and Encryption_Type with the encryption type you want to use (deterministic or randomized).


Modify your application: To modify your application, you need to use the Always Encrypted enabled .NET Framework Data Provider for SQL Server. You can download the provider from Microsoft.


You also need to change your connection string to include the following parameters:

  • Column Encryption Setting=Enabled

  • Certificate Thumbprint=Certificate_Thumbprint

  • Certificate Store Location=Current User

  • Certificate Store Name=My

Replace Certificate_Thumbprint with the thumbprint of the certificate you are using.


Test and deploy: Test your application thoroughly to ensure that it works correctly with Always Encrypted. Once you are satisfied that everything is working correctly, deploy your changes to your production environment.


These are the general steps and T-SQL commands for implementing SQL Server Always Encrypted. Keep in mind that this is a complex feature that requires careful planning and implementation. Consult the SQL Server documentation and seek expert advice before implementing it in a production environment.


Here are the general steps and some T-SQL commands for implementing Extensible Key Management (EKM) in SQL Server:


Install the EKM provider: Install the EKM provider software from your third-party vendor onto your SQL Server machine.


Register the EKM provider: Use the following T-SQL command to register the EKM provider:

USE [master]
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password';
GO
CREATE EXTERNAL MASTER KEY [EKM_Name]
WITH PROVIDER_NAME = 'EKM_Provider_Name',
    PROVIDER_TYPE = 'EKM_Provider_Type',
    PROVIDER_KEY_NAME = 'EKM_Key_Name',
    ALGORITHM = 'EKM_Algorithm'
    ENCRYPTION BY PASSWORD = 'password';
GO

Replace EKM_Name with the name you want to use for the EKM, EKM_Provider_Name with the name of your EKM provider, EKM_Provider_Type with the type of your EKM provider, EKM_Key_Name with the name of your EKM key, and EKM_Algorithm with the algorithm your EKM provider uses.


Create a database master key: Use the following T-SQL command to create a database master key:

USE [database_name]
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password';
GO

Create a certificate or asymmetric key: Use the following T-SQL command to create a certificate or asymmetric key:


USE [database_name]
GO
CREATE CERTIFICATE [Certificate_Name]
WITH SUBJECT = 'Certificate_Subject';

Replace Certificate_Name with the name you want to use for the certificate, and Certificate_Subject with a description of the certificate.


Create a symmetric key: Use the following T-SQL command to create a symmetric key:


USE [database_name]
GO
CREATE SYMMETRIC KEY [Symmetric_Key_Name]
WITH ALGORITHM = 'AES_256',
    IDENTITY_VALUE = 'identity_value',
    KEY_SOURCE = 'key_source'
    ENCRYPTION BY CERTIFICATE [Certificate_Name];
GO

Replace Symmetric_Key_Name with the name you want to use for the symmetric key, identity_value with a value that your EKM provider uses to generate a key, key_source with the source of the key, and Certificate_Name with the name of the certificate you created in step 4.


Use the symmetric key to encrypt data: Use the following T-SQL command to encrypt data using the symmetric key:


USE [database_name]
GO
OPEN SYMMETRIC KEY [Symmetric_Key_Name]
DECRYPTION BY CERTIFICATE [Certificate_Name];
GO
UPDATE [table_name]
SET [column_name] = ENCRYPTBYKEY(KEY_GUID('Symmetric_Key_Name'), [column_name]);
GO

Replace table_name with the name of the table containing the column you want to encrypt, column_name with the name of the column you want to encrypt, and Symmetric_Key_Name and Certificate_Name with the names of the symmetric key and certificate you created in steps 4 and 5.


These are the general steps and T-SQL commands for implementing Extensible Key Management (EKM) in SQL Server. Keep in mind that this is a complex feature that requires careful planning and implementation. Consult the SQL Server documentation and seek expert advice before implementing it in a production environment.

11 views0 comments
bottom of page