Transparent Data Encryption (TDE) for the Reviewer workspace in SQL Server

You can take several precautions to help secure the database, such as designing a secure system, encrypting confidential assets, and building a firewall around the database servers. However, in a scenario where the physical media (such as drives or backup tapes) are stolen, a malicious party can restore or attach the database and browse the data. One solution is to encrypt the sensitive data in the database and protect the keys used to encrypt the data with a certificate. This prevents anyone without the keys from using the data, but this kind of protection must be planned in advance.

Transparent Data Encryption (TDE) enables you to encrypt sensitive data, such as credit card numbers, stored in tables and FileGroups. Encrypted data is transparently decrypted for a database user or application that has access to data. TDE helps protect data stored on media in the event that the storage media or data file gets stolen. SQL Server uses authentication, authorization, and auditing mechanisms to secure data in the database but not in the operating system data files where data is stored. To protect these data files, SQL Server provides TDE. TDE encrypts sensitive data stored in data files. To prevent unauthorized decryption, TDE stores the encryption keys in a security module external to the database.

TDE performs real-time I/O encryption and decryption of the data and log files. The encryption uses a database encryption key (DEK), which is stored in the database boot record for availability during recovery. The DEK is a symmetric key secured by using a certificate stored in the master database of the server or an asymmetric key protected by an EKM module. TDE protects data at rest, meaning the data and log files. It provides the ability to comply with many laws, regulations, and guidelines established in various industries. This enables software developers to encrypt data by using AES and 3DES encryption algorithms without changing existing applications.

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.

Benefits of using TDE:

• As a security administrator, you can be sure that sensitive data is safe in case the storage media or data file is stolen.
• Implementing TDE helps you address security-related regulatory compliance issues.
• You do not need to create triggers or views to decrypt data for the authorized user or application. Data from tables is transparently decrypted for the database user and application.
• Database users and applications need not be aware that the data they are accessing is stored in encrypted form. Data is transparently decrypted for the database users and applications.
• Applications need not be modified to handle encrypted data. Data encryption and decryption are managed by the database.
• Key management operations are automated. The user or application does not need to manage encryption keys.

For more information, see Transparent Data Encryption (TDE) in the MSDN library.

To use TDE, follow these steps in SQL Server Management Studio.

手順:

1. Create a master key.
2. Create or obtain a certificate protected by the master key.
3. Create a database encryption key and protect it by the certificate.
4. Set the database to use encryption.

Example of TDE

You can use the SQL commands below to configure TDE. You can choose the password for the master key, and when backing up the master key, you can choose the folder and file name.

注意:

Copying and pasting the example may cause syntax errors.

USE master
GO
/* Verify master key */
SELECT * FROM sys.symmetric_keys WHERE name LIKE '%MS_DatabaseMasterKey%'
GO

/* if there are no records found, then it means there was no predefined Master Key. 
 To create a Master Key, you can execute the below mentioned TSQL code. */
 
/* Create master key */
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'rev$$@admin';
GO
/* Backup master key */
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'rev$$@admin';
GO
BACKUP MASTER KEY TO FILE = 'D:\mssqlbackup\master\masterkey.mk' 
    ENCRYPTION BY PASSWORD = 'rev$$@admin';
GO

/* Create Certificate */
CREATE CERTIFICATE rev_cert WITH SUBJECT = 'REV Server Certificate';
GO

/* Verify Certificate */
SELECT * FROM sys.certificates where [name] = 'rev_cert'
GO

/* Backup certificate */
BACKUP CERTIFICATE rev_cert TO FILE = 'D:\mssqlbackup\master\rev.cer'
   WITH PRIVATE KEY (
         FILE = 'D:\mssqlbackup\master\rev.pvk',
         ENCRYPTION BY PASSWORD = 'rev$$@admin');
GO

--use rev database
USE rev
GO
/* Create Encryption key */
CREATE DATABASE ENCRYPTION KEY
   WITH ALGORITHM = AES_256
   ENCRYPTION BY SERVER CERTIFICATE rev_cert;
GO

/* Encrypt database */
ALTER DATABASE revdb SET ENCRYPTION ON;
GO

/* Verify Encryption */
SELECT 
DB_NAME(database_id) AS DatabaseName
,Encryption_State AS EncryptionState
,key_algorithm AS Algorithm
,key_length AS KeyLength
FROM sys.dm_database_encryption_keys
GO
SELECT 
NAME AS DatabaseName
,IS_ENCRYPTED AS IsEncrypted 
FROM sys.databases where name ='revdb'
GO