Chapter 1 Securing Your Server and Network (11): Encrypt __ Database using transparent Database

原文出处：http://blog.csdn.net/dba_huangzj/article/details/38398813，专题目录：http://blog.csdn.net/dba_huangzj/article/details/37906349

No person shall, without the consent of the author, publish in the form of "original" or be used for commercial purposes without any liability.

Last article: http://blog.csdn.net/dba_huangzj/article/details/38368737

Preface:

If you do not have permission controls on the database files (mdf/ldf, etc.), attackers can copy these files and attach them to their machine for analysis. The first layer of protection is the ability to control the NTFS file system where the SQL Server files reside. If you want to protect your database further, you can use transparent database encryption (transparent DB encryption,tde), which protects all files of the corresponding database, regardless of the number of files. Because files are encrypted, even if they are copied away, they cannot be used without the database master key. At the same time, this encryption does not affect the user's use of the database, and developers do not need to do additional work on this.

Note that only the development version, and general and Datacenter Editions, support TDE.

implementation:

1. Create the server encryption master key:

Use master; 
CREATE MASTER KEY Encryption by PASSWORD = ' strong password ';

2. Back up the master key immediately and put it in a safe place, and if you lose the master key, you will not be able to use it:

BACKUP MASTER KEY to FILE = ' \\path\SQL1_master.key ' encryption by 
PASSWORD = ' strong password ';

The password must compound Windows security Policy requirements and the SQL Server service account must have write access to the corresponding directory.

3. Create a server certificate in the Master library:

CREATE certificate Tdecert with SUBJECT = ' TDE certificate ';

4. Backup Certificate:

BACKUP Certificate Tdecert to File = ' \\path\SQL1_TDECert.cer ' with 
  PRIVATE KEY ( 
    FILE  = ' \\path\SQL1_ Tdecert.pvk ', 
    encryption by PASSWORD = ' Another strong password ' 
);

Original source: http://blog.csdn.net/dba_huangzj/article/details/38398813

5. Create the database encryption key for the corresponding database:

Use target database; 
Go 
CREATE DATABASE encryption KEY and 
algorithm = aes_128 
encryption by SERVER certificate Tdecert T is the title of the card

Original source: http://blog.csdn.net/dba_huangzj/article/details/38398813

6. Enable database encryption:

ALTER database target database SET encryption on;

principle:

TDE automatically encrypts data and log files on the disk without additional modifications to the database, and can encrypt all database or log backups. Implementation is also very easy. For cryptographic algorithms, you can usually use aes_128/192/256 or Triple_des_3key. The strength of triple-des is higher. However, performance may be affected.

For TDE performance analysis, you can access this article: http://www.databasejournal.com/features/mssql/article.php/3815501/ Performance-testing-sql-2008146s-transparent-data-encryption.htm (Performance Testing SQL 2008 ' s Transparent Data Encryption,sql Server 2008 tde/Transparent Database encryption performance test).

If you need to restore the encrypted database files to another server, you need to restore the certificate to the target server first:

Use master; 
CREATE Certificate Tdecert from file = ' \\path\SQL1_TDECert.cer ' with 
  PRIVATE KEY ( 
    FILE  = ' \\path\SQL1_ Tdecert.pvk ', 
    decryption by PASSWORD = ' password ' 
);

Original source: http://blog.csdn.net/dba_huangzj/article/details/38398813

You can then begin restoring the database or log files.

Next: http://blog.csdn.net/dba_huangzj/article/details/38438363