Chapter 1 Securing Your Server and Network (11): Using Transparent database encryption

Original source: http://blog.csdn.net/dba_huangzj/article/details/38398813, featured catalogue:http://blog.csdn.net/dba_huangzj/article/details/37906349

No person shall, without the consent of the author, be published in the form of "original" or used for commercial purposes, and I am not responsible for any legal liability.

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

Objective:

If the database files (mdf/ldf, etc.) do not have permission control, the attacker can copy the files and attach them to their machine for analysis. The first layer of protection is to control permissions on the NTFS file system where the SQL Server file resides. If you want to further protect the database, you can use transparent database encryption (Transparent ENCRYPTION,TDE), which protects all files in the corresponding database, regardless of the number of files. Because the files are encrypted, even if the files 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 extra work on it.

It is important to note that TDE is supported only in the development version, and in general and Datacenter Editions.

Realize:

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 ';

Where the password must be combined with Windows security policy requirements, and the SQL Server service account must have write access to the corresponding directory.

3. Create the server certificate in the Master library:

CREATE CERTIFICATE tdecert with SUBJECT = ' TDE CERTIFICATE ';

4. Back up the 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 with algorithm = aes_128 encryption by SERVER CERTIFICATE Tdecert; --tdecert for the title of the certificate

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

6. Enable database encryption:

ALTER database target SET encryption on;

 

Principle:

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

For performance analysis of TDE, 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-Transparent Data Encryption,sql Server tde/Transparent database encryption performance test).

If you need to restore the encrypted database file 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 start restoring the database or log files.