Summary of Sqlserver's use of TDE transparent data encryption
Official document https://docs.microsoft.com/zh-cn/sql/t-sql/statements/create-certificate-transact-sql?view=sql-server-2017
TDE:Transparent Data Encryption transparent data encryption
See master-security-symmetric key or sys.symmetric_keys in the master key XX:SSMS graphical interface tool
See master-security-certificates or sys.certificates in the CERTIFICATE YY:SSMS graphical interface tool
Enable TDE for the database:
Rough steps
Create a master key in the master database.
Create / use certificates protected by the master key.
Encrypt the key to a certificate-protected database.
Enable TDE for a database.
1. Drop master key the master key first
Drop master key
If an error is reported, it means that certificate is using it. You need to delete certificate and then delete master key.
Cannot drop master key because certificate 'Cellular database XX' is encrypted by it.
2. Create the master key master key
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'XX'
Example create master key encryption by password = 'TD_123456'
3. Create a certificate certificate with a general name of certdbname
Create certificate certtificatename with subject = 'XX'
Example create certificate certSSRSTEST with subject = 'SSRSTEST database certificate data encription'
4. Back up step 3 above to create a certificate certificate
BACKUP CERTIFICATE certtificatename TO FILE = 'XX'
WITH PRIVATE KEY (FILE = 'XXkey'
ENCRYPTION BY PASSWORD = 'XX')
Example
BACKUP CERTIFICATE certSSRSTEST TO FILE ='\\ testdb1\ mirror\ certSSRSTEST'
WITH PRIVATE KEY (FILE ='\\ testdb1\ mirror\ certSSRSTESTkey'
ENCRYPTION BY PASSWORD = '654321DT')
5. Encrypt a database using the certificate in step 3 above, and enable this encryption
Create database encryption key with algorithm = XX encryption by server certificate certtificatename
Alter database databasename set encryption on
Example
Use SSRSTEST
Go
Create database encryption key with algorithm = AES_128 encryption by server certificate certSSRSTEST
Go
Alter database SSRSTEST set encryption on
Go
Off-machine recovery of a database backed up by TDE
1. Back up the TDE database
Backup database SSRSTEST to disk ='\\ testdb1\ mirror\ SSRSTEST.bak'
2. Restore this database on a different machine
2.1.Create master key on a different machine. This password can be used freely.
Create master key encryption by password = '999TD999'
2.2. Create a CERTIFICATE certificate on a different machine. This password must be the same as that of the source backup CERTIFICATE (that is, step 4 above), otherwise an error will be reported.
CREATE CERTIFICATE certClientData
FROM FILE='\\ testdb1\ mirror\ certSSRSTEST'
WITH PRIVATE KEY (
FILE='\\ testdb1\ mirror\ certSSRSTESTkey'
DECRYPTION BY PASSWORD='654321_DT')
2.3 、
Restore database SSRSTEST from disk ='\\ testdb1\ mirror\ SSRSTEST.bak'
If an error is reported when restoring this database directly, it indicates that a certificate certificate needs to be created on the different machine.
Restore database SSRSTEST from disk ='\\ testdb1\ mirror\ SSRSTEST.bak'
Error Cannot find server certificate with thumbprint '0x1640C78B8E4C6DFA2DB4D2E97E3B206F2672FAB.
Create a certificate certificate on a different machine. An error indicates that DECRYPTION BY PASSWORD must be equal to the ENCRYPTION BY PASSWORD in step 4 above.
Use master
Go
CREATE CERTIFICATE certClientData
FROM FILE='\\ testdb1\ mirror\ certSSRSTEST'
WITH PRIVATE KEY (
FILE='\\ testdb1\ mirror\ certSSRSTESTkey'
DECRYPTION BY PASSWORD='TD_123456')
Go
Error The private key password is invalid
Create a certificate certificate on a different machine, with a correct password and an error, indicating that you need to create a master key on the other machine first.
Use master
Go
CREATE CERTIFICATE certClientData
FROM FILE='\\ testdb1\ mirror\ certSSRSTEST'
WITH PRIVATE KEY (
FILE='\\ testdb1\ mirror\ certSSRSTESTkey'
DECRYPTION BY PASSWORD='654321_DT')
Go
Report an error Please create a master key in the database or open the master key in the session before performing this operation.
Create master key and randomly set password password = '999passTD999password, create certificate, enter correct password PASSWORD='654321_DT', everything is fine.
Use master
Create master key encryption by password = '999TD999'
CREATE CERTIFICATE certClientData
FROM FILE='\\ testdb1\ mirror\ certSSRSTEST'
WITH PRIVATE KEY (
FILE='\\ testdb1\ mirror\ certSSRSTESTkey'
DECRYPTION BY PASSWORD='654321_DT')
