这篇文章说明了如何使用透明数据加密（TDE）来保护数据库，包括备份的文件。要了解这个功能可以被用来提高数据库应用程序的安全性，请查看这篇文章。
Step1:
USE master ;
GO
IF EXISTS( SELECT
*
FROM
sys.databases
WHERE
name = 'TdeDemo' )
DROP DATABASE TdeDemo ;
GO
CREATE DATABASE TdeDemo ;
GO
Next, create the server-level certificate which will protect the database key used to encrypt the database's files. This certificate in turn will be protected by the master key which if it does not exist will need to be created:
创建主密钥
USE master ;
GO
IF NOT EXISTS( SELECT
*
FROM
sys.symmetric_keys
WHERE
name LIKE '%[_]DatabaseMasterKey%' )
BEGIN
CREATE MASTER KEY ENCRYPTION BY PASSWORD =
'997jkhUbhk$w4ez0876hKHJH5gh' ;
END
GO
创建或获取由主密钥保护的证书
CREATE CERTIFICATE MyTdeCert
WITH SUBJECT = 'My TDE Certificate' ;
GO
With the server-level components in place, the database can now be encrypted. This is done by first creating the database (symmetric) encryption key within the database and then enabling TDE:
USE TdeDemo ;
GO
CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_128 ENCRYPTION BY SERVER
CERTIFICATE MyTdeCert ;
GO
ALTER DATABASE TdeDemo
SET ENCRYPTION ON ;
GO
Database encryption may take a while to complete. While in progress, the sys.dm_database_encryption_keys data management view will show the database in an encryption_state of 2:
SELECT
DB_NAME(database_id) AS DB ,
encryption_state
FROM
sys.dm_database_encryption_keys
WHERE
database_id = DB_ID() ;
GO
Once TDE encryption has been fully applied, the encryption_state will become 3:
SELECT
DB_NAME(database_id) AS DB ,
encryption_state
FROM
sys.dm_database_encryption_keys
WHERE
database_id = DB_ID() ;
GO
Now to demonstrate the protection of database backup files through TDE, backup the database and its certificate. Please note that these are being backed up locally to the same location. This is not a secure practice but is expediant for this demo:
USE master ;
GO
BACKUP CERTIFICATE MyTdeCert
TO FILE = 'c:\temp\MyTdeCert'
WITH PRIVATE KEY (
FILE = 'c:\temp\MyTdeCertPrivateKey',
ENCRYPTION BY PASSWORD = '997jkhUbhk$w4ez0876hKHJH5gh'
) ;
GO
BACKUP DATABASE TdeDemo
TO DISK = 'c:\temp\TdeDemo.bak'
WITH INIT ;
GO
By dropping the database and the server-level certificate, we can simulate a restore to a different server:
DROP DATABASE TdeDemo ;
GO
DROP CERTIFICATE MyTdeCert ;
GO
With the certificate missing, the restore operation will fail:
RESTORE DATABASE TdeDemo
FROM DISK = 'C:\temp\TdeDemo.bak' ;
GO
Msg 33111, Level 16, State 3, Line 1
Cannot find server certificate with thumbprint '0x686A8264E4A17572FBAE6A1D091A47D600847FB6'.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
It's not until the certificate is recovered to the server that the backup file can be restored:
CREATE CERTIFICATE MyTdeCert
FROM FILE = 'c:\temp\MyTdeCert'
WITH PRIVATE KEY (
FILE = 'c:\temp\MyTdeCertPrivateKey',
DECRYPTION BY PASSWORD = '997jkhUbhk$w4ez0876hKHJH5gh'
) ;
GO
RESTORE DATABASE TdeDemo
FROM DISK = 'C:\temp\TdeDemo.bak' ;
GO
Processed 168 pages for database 'TdeDemo', file 'TdeDemo' on file 1.
Processed 2 pages for database 'TdeDemo', file 'TdeDemo_log' on file 1.
RESTORE DATABASE successfully processed 170 pages in 0.157 seconds (8.415 MB/sec).
To reset the environment:
USE master ;
GO
DROP DATABASE TdeDemo ;
GO
DROP CERTIFICATE MyTdeCert ;
GO