Documentation Center

Configuring Transparent Data Encryption in SQL Server

Transparent Data Encryption (TDE) is a technology employed to encrypt database content. TDE offers encryption at a column, table, and table space level. TDE solves the problem of protecting data at rest, encrypting databases both on the hard drive and consequently on backup media.

SQL Server Encryption

Encryption of the database file is performed at the page level. The pages in an encrypted database are encrypted before they are written to disk and decrypted when read into memory. TDE does not increase the size of the encrypted database. In TDE, all files and file groups in the database are encrypted. SQL Server allows administrators and developers to choose from among several algorithms, we recommend and tested Content Manager with 256-bit AES.

Configuring encryption on a new database

Use the Transact-SQL command examples to:

  1. Create a Database Master Key and a Certificate protected by the Master Key.
    USE master
    GO
    CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'LetUsConsiderThisAVeryStrong(1+2=3)PasswordForEncryptingAnSDLTrisoftDatabaseHostedOnSQLServer'
    GO
    CREATE CERTIFICATE MyServerCertificate WITH SUBJECT = 'MyDataEncryptionKeyCertificate'
    GO
  2. Back up the new Master Key and Certificate. Do not use the same password that is used to encrypt the Master Key in the database.
    USE master
    BACKUP SERVICE MASTER KEY TO FILE = 'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL<version>\MSSQL\Backup\TDEInfoShare40Test.MSSQLServer<version>-ServiceMasterKey.smk' ENCRYPTION BY PASSWORD = 'ServiceMasterKey20100219'
    GO
    BACKUP MASTER KEY TO FILE = 'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL<version>\MSSQL\Backup\TDEInfoShare40Test.MSSQLServer<version>-DatabaseMasterKey.dmk' ENCRYPTION BY PASSWORD = 'DatabaseMasterKey20100219'
    GO
    BACKUP CERTIFICATE MyServerCertificate TO FILE = 'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL<version>\MSSQL\Backup\TDEInfoShare40Test.MSSQLServer<version>-MyServerCertificate.cert' WITH PRIVATE KEY ( FILE = 'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL<version>\MSSQL\Backup\TDEInfoShare40Test.MSSQLServer<version>-CertificatePrivateKey.pk' , ENCRYPTION BY PASSWORD = 'CertificatePrivateKey20100219' )
    GO
  3. Create a Database Encryption Key on your new database and protect it by the Certificate.
    USE MyDatabaseName
    GO
    CREATE DATABASE ENCRYPTION KEY
    WITH ALGORITHM = AES_256
    ENCRYPTION BY SERVER CERTIFICATE MyServerCertificate 
    GO
  4. Set the database to use encryption
    USE MyDatabaseName
    GO
    ALTER DATABASE MyDatabaseName
    SET ENCRYPTION ON
    GO

Where <version> is the version of the MSSQL database you are using. Refer to the hardware and software requirements to ensure that you have an approved version of the database installed.

Configuring encryption on an existing database

Restore a database dump in an empty encrypted database.

Disabling the encryption

Use the Transact-SQL command examples to:

  1. Remove the database encryption
    USE MyDatabaseName
    GO
    ALTER DATABASE MyDatabaseName
    SET ENCRYPTION OFF
    GO

    Wait for decryption operation to complete, execute the Transact-SQL command below, and look for a value of 1 = Unencrypted

    SELECT d.name, dek.* FROM sys.dm_database_encryption_keys dek, sys.databases d WHERE d.database_id=dek.database_id
    GO
  2. Drop the database encryption key
    USE MyDatabaseName
    GO
    DROP DATABASE ENCRYPTION KEY;
    GO