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:
- 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 - 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 - 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 - 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:
- Remove the database encryption
USE MyDatabaseName GO ALTER DATABASE MyDatabaseName SET ENCRYPTION OFF GOWait 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
- Drop the database encryption key
USE MyDatabaseName GO DROP DATABASE ENCRYPTION KEY; GO