Chapter 1 Securing Your Server and Network (11): transparent database encryption, chaptersecuring

Chapter 1 Securing Your Server and Network (11): transparent database encryption, chaptersecuring
Source: Workshop

Without the consent of the author, no one shall be published in the form of "original" or used for commercial purposes. I am not responsible for any legal liability.

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

 

Preface:

 

Without permission control on database files (MDF/LDF), attackers can copy these files and attach them to their own machines for analysis. The first layer of protection is to control the permissions of the NTFS file system where the SQL Server File is located. If you want to further protect the Database, you can use Transparent Database Encryption (TDE). This function can protect all files in the corresponding Database, no matter how many files are there. Because the files are encrypted, even if these files are copied, 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.

Note that TDE is supported only for the Development edition and generally for the data center edition.

 

Implementation:

 

1. Create a server encryption master key:

USE master; create master key encryption by password = 'strong password ';

 

2. Back up the CMK immediately and put it in a safe place. If you lose the CMK, you will not be able to use it yourself:

Backup master key to file = '\ path \ SQL1_master.key' encryption by password = 'strong password ';

The password must be a combination of Windows security policy requirements, and the SQL Server service account must have the write permission on the corresponding directory.

 

3. Create a server certificate in the Master database:

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

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

5. Create a 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 as the title

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 the data and log files on the disk without any additional modifications to the database, and can encrypt all databases or log backups. The implementation method is also very easy. Generally, AES_128/192/256 or Triple_des_3key can be used for encryption algorithms. The TRIPLE-DES strength is higher. However, performance may be affected.

For TDE Performance analysis, visit 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 ).

To restore the encrypted database file to another server, you must first restore the certificate to the target server:

USE master; create certificate TDECert from file = '\ path \ SQL1_TDECert.cer' with private key (FILE = '\ path \ SQL1_TDECert.pvk', decryption by password = 'Password ');

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

Then you can restore the database or log files.