使用透明数据加密（TDE）来保护数据库

这篇文章说明了如何使用透明数据加密（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