Encrypting a backup of a SQL Server database

Original: Encrypting a backup of a SQL Server database

Well, recently in the study of database backup related things, considering that should be a database backup to add a secret, ready to search from the Internet to see what a good way, did not think it was quite chaotic ...

First of all, I found from the Internet, the database backup encryption methods, there are three main types:

1. when using the BACKUP statement, add the PASSWORD parameter "This method applies to previous versions of SQL Server 2012 (2012 not included)"

However, in fact, the encryption of this PASSWORD parameter is not a complete encryption of the data we imagined.

As described in MSDN: https://msdn.microsoft.com/zh-cn/library/ms186865 (v=sql.100). aspx

It seems that this password is only to attach a password to the backup, and do not encrypt the backup data, if the password is directly modified to replace the data in the backup can still be read correctly, so, in fact, the meaning of encryption is not very large.

2. Enable transparent Data encryption (TDE) for the database "This method applies to SQL Server 2008 and later versions (with 2008)"

Note: This feature is supported only in SQL Server Enterprise (Corporate Edition).

This tde bar, well, very good, because it is not only for backup encryption, it is the entire database is encrypted, and since it is "transparent", that is, it will not affect the operation of any database, normal database operations (such as the deletion of what), as well as backup recovery, etc. There is no need to specifically consider encryption issues. Only if you leave the current database server, you will find that nothing can be done. The original encryption certificate needs to be imported into the new server for normal use.

MSDN Related Documentation: https://msdn.microsoft.com/zh-cn/library/bb934049.aspx

To enable transparent encryption, you need the following steps:

1. In the master database, add the database master key:

MORE: Https://msdn.microsoft.com/zh-cn/library/ms174382.aspx

 Use Master; CREATE KEY  by = ' $ $test $$ ';

where Password = ' ' is setting the master key, please set the high strength password as needed.

To modify the master key, you can use:

MORE: Https://msdn.microsoft.com/zh-cn/library/ms186937.aspx

 Use Master; ALTER KEY  with  by = ' $$123123$$ ';

2. In the master database, add the certificate used to encrypt the database:

MORE: https://msdn.microsoft.com/zh-cn/library/ms187798 (v=sql.120). aspx

 Use Master; CREATE  with = ' Test Certificate ';

Among them, TestCert is the certificate name, can be named according to need, but remember ! Subject is the theme seems, casually write on it, the length of the best not to more than 128 bytes.

3. In the database to be encrypted, set up the certificate and the encryption algorithm:

MORE: Https://msdn.microsoft.com/zh-cn/library/bb677241.aspx

 Use TestDB CREATE DATABASE KEY  with =   by SERVER CERTIFICATE TestCert;

Where TestCert is the name of the certificate added in the previous step, algorithm is the encryption algorithm, with: aes_128 | aes_192 | aes_256 | Triple_des_3key, select the encryption algorithm that is suitable for the strength as needed.

4. Enable encryption for the database to be encrypted:

ALTER DATABASE SET on;

Well, after the above steps, the encryption of the database is complete.

To see which databases in the current database server are encrypted, execute the following statement:

SELECT db_name  as *  from Sys.dm_database_encryption_keys;

However, you should also consider the subsequent recovery of the database or transfer to another server.

5, first to backup the encryption certificate from the master database:

MORE: Https://msdn.microsoft.com/zh-cn/library/ms178578.aspx

 Use Master; BACKUP  to FILE = ' D:\TestCert.cer '   with KEY FILE = ' D:\TestCert.pkey '  by = ' $ $certpwd $$ ' );

Well, the certificate name, save the file name, save the private key, the file name of the private key, and the password to encrypt the private key (this password is used to protect the private key, which is used for recovery).

6, in other database server, still first establish the database master key, with the 1th step operation;

7. Then, start recovering the certificate from the file:

 Use Master; CREATE  from FILE = ' D:\TestCert.cer '  with KEY FILE = ' D:\TestCert.pkey '  by = ' $ $certpwd $$ ');

Yes, the recovery certificate is actually a certificate created from a file, a certificate name, a file path, a private key file path, and a password to decrypt the private key (set when backup is encrypted).

8, then you can attach the database, restore the database or something ~

Note that the key to database encryption is that certificate , the database master key is used to protect the database information, such as the storage of certificates, not directly related to the database encryption.

So, be sure to back up a good certificate!!! Otherwise, don't cry and decrypt the database.

This section of the reference article: http://blog.csdn.net/ws_hgo/article/details/6927152

Finally, to say that this method is not good, that is, this method is the entire database of data encryption, including logs and so on, may bring a certain burden on the CPU.

And in the back-up time because the database is already in the encryption state, so too much compression can not be done, the backup file may be large size.

In fact, I am more concerned that this transparent data encryption (TDE) only Enterprise (Corporate Edition) owned, the other version is wooden ~

3. Encrypt the backup directly "This method applies to SQL Server 2014 and later versions (should?). ）】

This method and the first comparison, directly in the backup parameters, only the backup encryption, the database is not encrypted, but also need to be the same as the second method, you need to create a certificate.

1, forget, or I will not repeat the writing, please see the second method of the 1th step ...

2. See step 2nd of the second method ...

3, NN, this time you can start backing up the database!

BACKUP DATABASE  to DISK = ' D:\TestDB.bak '  with  == testcert);

Where the first half of the sentence should be familiar, is to back up the database statement, Compression is the compression option

The second half of the sentence is encryption, algorithm is the encryption algorithm, TestCert is the certificate we added.

The backup was completed. Of course, it also involves recovery issues on other servers.

4, OK, I am lazy again, is actually the second method in the 5th, 6, 7 Steps, Backup certificate, recovery certificate ...

5, Ah, but also happy to restore the backup ~

This section of the reference article: http://www.cnblogs.com/CareySon/p/3853016.html

Oh, the front said so much, in fact, directly said this much good, is it, first in the version, more than TDE two version, but there is no I want ...

Second, compression can also be used, but backup compression and backup encryption support version is the same ...

What's more, it only supports 2014 (and later versions?). ), presumably there should be a lot of databases or 2008 or even 2005 ...

Summarize

So, in fact, these few methods are not too satisfied ... Everyone use it according to their own situation, as for me ... According to my needs, I am going to use zip compression encryption ...

Hey, wasted a morning research database backup encryption, and wasted a noon to write this article, is a morning not wasted it, maybe later use it ...

Encrypting a backup of a SQL Server database