Shulou(Shulou.com)06/01 Report--

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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.