Transparent data encryption (TDE) for the 生产化制图 (Production Mapping) workspace in SQL Server (Production Mapping)
可采取多种预防措施来协助保护数据库安全，例如设计安全系统、加密机密资产以及在数据库服务器周围建立防火墙。但如果物理介质（如驱动器或备份磁带）被盗，则恶意方可以恢复或附加数据库并浏览数据。一种解决方案是对数据库中的敏感数据进行加密，并通过一个证书来保护用于加密数据的密钥。这样便可防止任何没有密钥的用户使用该数据，但此种保护必须提前计划。
Transparent Data Encryption (TDE) enables you to encrypt sensitive data, such as credit card numbers, stored in tables and tablespaces. 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 is stolen. Oracle 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, Oracle 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 执行数据和日志文件的实时 I/O 加密与解密。此加密使用数据库加密密钥 (DEK)，该密钥存储在数据库启动记录中以供恢复期间使用。DEK 是通过使用存储在服务器主数据库中的证书进行保护的对称密钥，或者是通过 EKM 模块进行保护的不对称密钥。TDE 可以保护静止的数据，即数据和日志文件。它可用于遵守各行业中建立的多种法律、法规和准则。从而使软件开发者能够使用 AES 和 3DES 加密算法对数据进行加密，而无需更改现有应用程序。
数据库文件的加密是在页面级别执行的。已加密数据库中的页面将在写入磁盘之前进行加密，并在读取到内存时进行解密。TDE 不会增加已加密数据库的大小。
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.
有关详细信息，请参阅 MSDN 库中的透明数据加密 (TDE)。
要使用 TDE，请在 SQL Server Management Studio 中执行以下步骤。
- 创建主密钥。
- 创建或获取受主密钥保护的证书。
- 创建数据库加密密钥并通过证书对其进行保护。
- 将数据库设置为使用加密。
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.
注:对示例进行复制和粘贴可能会导致语法错误。
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 = 'pm$$@admin';
GO
/* Backup master key */
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'pm$$@admin';
GO
BACKUP MASTER KEY TO FILE = 'D:\mssqlbackup\master\masterkey.mk'
ENCRYPTION BY PASSWORD = 'pm$$@admin';
GO
/* Create Certificate */
CREATE CERTIFICATE pm_cert WITH SUBJECT = 'PM Server Certificate';
GO
/* Verify Certificate */
SELECT * FROM sys.certificates where [name] = 'pm_cert'
GO
/* Backup certificate */
BACKUP CERTIFICATE pm_cert TO FILE = 'D:\mssqlbackup\master\pm.cer'
WITH PRIVATE KEY (
FILE = 'D:\mssqlbackup\master\pm.pvk',
ENCRYPTION BY PASSWORD = 'pm$$@admin');
GO
--use pm database
USE pmdb
GO
/* Create Encryption key */
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE pm_cert;
GO
/* Encrypt database */
ALTER DATABASE pmdb 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 ='pmdb'
GO