Think of TDE (transparent Data encryption).
TDE MSDN Description:
Transparent data encryption (TDE) performs real-time I/O encryption and decryption of data and log files. This encryption uses the database encryption key (DEK), which is stored in the database boot record for use during recovery. DEK is a symmetric key that is protected with a certificate that is stored in the master database on the server, or an asymmetric key that is protected by the EKM module. TDE protects "dormant" data, that is, data and log files. It provides the ability to comply with many laws, regulations and standards established by various industries. Software developers use AES and 3DES encryption algorithms to encrypt data without having to change existing applications.
What really appeals to me is "no need to change the existing application" because I need the encrypted library to serve a very stable system, and do everything that the DBA can control.
TDE Encryption Architecture:
Test process:
Copy Code code as follows:
--Create Master keys (master key)
Use master
Go
--drop MASTER KEY
CREATE MASTER KEY encryption by Password=n ' 1QAZ@WSX ';
Go
--Backing up the master key
BACKUP MASTER KEY to File=n ' C:\master_key.cer '
Encryption by Password=n '! Qaz2wsx '
Go
-Create a certificate based on the master key. Used to protect the database encryption key (DB encryption key)
--drop Certificate Sdb_cert
CREATE Certificate Sdb_cert
With Subject=n ' Certificate for Secretdb '
Go
--Backing up the master Key's certificate using the private key encryption
BACKUP Certificate Sdb_cert
To File=n ' C:\SDB_Cert.cer '
With PRIVATE KEY
(
FILE =n ' C:\SDB_Cert.pvk ',
Encryption by password= '! Qaz2wsx '
)
Go
--Create a test library Secretdb
Use master
Go
CREATE DATABASE Secretdb
Go
Use Secretdb
Go
CREATE TABLE SDB_TB
(ID int,val NVARCHAR (20));
INSERT into SDB_TB
VALUES (1,n ' A '), (2,n ' B '), (3,n ' C ');
Go
Use Secretdb
Go
--Create a database encryption key
CREATE DATABASE Encryption KEY
With algorithm=aes_128
Encryption by SERVER certificate Sdb_cert;
Go
--Enable database encryption
Use Secretdb
Go
ALTER DATABASE secretdb SET encryption on
Go
--Backup SECRETDB for subsequent machine restore tests
Use master
Go
BACKUP DATABASE secretdb to Disk=n ' D:\SecretDB.bak '
Go
After this is done, the SECRETDB library is encrypted and has its encrypted backup file, which is then required to restore the backup on another.
I'll cuff the secretdb.bak,sdb_cert.cer,sdb_cert.pvk to another machine. If you restore directly, you will get an error. You need to create a certificate that was originally used for encryption to restore the database backup. My purpose is achieved!
--Restore Secretdb backups on a different machine
Use master
Go
CREATE DATABASE Secretdb
Go
RESTORE DATABASE Secretdb
From Disk=n ' D:\SecretDB.bak '
With REPLACE
Go
--Message 33111, Level 16, State 3, line 1th
--Unable to find a server certificate with fingerprint ' 0x0106000000000009010000009c529ffd5c7fd72fd0aae9edf46c5f69946ffed0 '.
--Message 3013, Level 16, State 1, line 1th
--restore DATABASE is terminating abnormally.
Create a certificate and restore it.
Use master
Go
CREATE Certificate Sdb_cert
From File=n ' C:\SDB_Cert.cer '
With PRIVATE KEY
(
File=n ' C:\SDB_Cert.pvk ',
Decryption by Password=n '! Qaz2wsx '
)
Go
RESTORE DATABASE Secretdb
From Disk=n ' D:\SecretDB.bak '
With REPLACE
Go
Summarize:
In fact, before doing TDE should carefully read BOL Note: Ms-help://ms. Sqlcc.v10/ms. Sqlsvr.v10.zh-chs/s10de_4deptrbl/html/c75d0d4b-4008-4e71-9a9d-cee2a566bd3b.htm
which says:
If you use TDE to encrypt your database, backup compression will not significantly compress the backup storage.
Replication does not automatically copy data from a TDE-enabled database in encrypted form. If you want to protect the distribution and subscriber databases, you must enable TDE separately.
Certain limitations and considerations can affect the deployment and use of TDE.
