官方文檔https://docs.microsoft.com/zh-cn/sql/t-sql/statements/create-certificate-transact-sql?view=sql-server-2017

創(chuàng)新互聯(lián)公司是一家專業(yè)提供湘鄉(xiāng)企業(yè)網(wǎng)站建設(shè),專注與網(wǎng)站制作、做網(wǎng)站、H5網(wǎng)站設(shè)計、小程序制作等業(yè)務(wù)。10年已為湘鄉(xiāng)眾多企業(yè)、政府機(jī)構(gòu)等服務(wù)。創(chuàng)新互聯(lián)專業(yè)網(wǎng)絡(luò)公司優(yōu)惠進(jìn)行中。

TDE：Transparent Data Encryption透明數(shù)據(jù)加密

master key XX：SSMS圖形界面工具中見master-security-symmetric key或見sys.symmetric_keys

CERTIFICATE YY：SSMS圖形界面工具中見master-security-certificates或見sys.certificates

數(shù)據(jù)庫啟用TDE：

大致步驟

在master數(shù)據(jù)庫里創(chuàng)建主密匙。

創(chuàng)建/使用受主密匙保護(hù)的證書。

對某個受證書保護(hù)的數(shù)據(jù)庫加密密匙。

對某個數(shù)據(jù)庫啟用TDE。

1、先drop master key主秘鑰

drop master key

如果報錯，說明有certificate在使用它，需要先把certificate刪除再刪除master key

Cannot drop master key because certificate 'C_databaseXX' is encrypted by it.

2、創(chuàng)建master key主秘鑰

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'XX';

示例create master key encryption by password = 'TD_123456';

3、創(chuàng)建certificate證書，名稱一般為certdbname

create certificate certtificatename with subject ='XX';

示例create certificate certSSRSTEST with subject ='SSRSTEST database certificate data encription';

4、備份上面第3步創(chuàng)建certificate證書

BACKUP CERTIFICATE certtificatename TO FILE = 'XX'

WITH PRIVATE KEY ( FILE = 'XXkey' ,

ENCRYPTION BY PASSWORD = 'XX' );

示例

BACKUP CERTIFICATE certSSRSTEST TO FILE = '\\testdb1\mirror\certSSRSTEST'

WITH PRIVATE KEY ( FILE = '\\testdb1\mirror\certSSRSTESTkey' ,

ENCRYPTION BY PASSWORD = '654321_DT' );

5、對某個數(shù)據(jù)庫使用上面第3步的certificate進(jìn)行加密，并啟用這個加密

create database encryption key with algorithm = XX encryption by server certificate certtificatename

alter database databasename set encryption on

示例

use SSRSTEST;

go

create database encryption key with algorithm = AES_128 encryption by server certificate certSSRSTEST

go

alter database SSRSTEST set encryption on

go

異機(jī)恢復(fù)一個TDE備份的數(shù)據(jù)庫

1、備份TDE數(shù)據(jù)庫庫

backup database SSRSTEST to disk = '\\testdb1\mirror\SSRSTEST.bak'

2、異機(jī)恢復(fù)這個數(shù)據(jù)庫

2.1、異機(jī)創(chuàng)建master key，這個密碼可以隨便

create master key encryption by password = '999_TD999';

2.2、異機(jī)創(chuàng)建CERTIFICATE證書，這個 密碼必須和源端備份CERTIFICATE時的密碼一致(即上面第4步) ，否則會報錯

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'

異機(jī)恢復(fù)這個數(shù)據(jù)庫時如果直接恢復(fù)，有報錯，說明需要在異機(jī)創(chuàng)建certificate證書

restore database SSRSTEST from disk = '\\testdb1\mirror\SSRSTEST.bak'

報錯Cannot find server certificate with thumbprint '0x1640C78B8E4C6DCFA2DB4D2E97E3B206F2672FAB'.

異機(jī)創(chuàng)建certificate證書，有報錯說明DECRYPTION BY PASSWORD必須等于上面第4步的ENCRYPTION BY PASSWORD = '654321_DT'

use master;

go

CREATE CERTIFICATE certClientData

FROM FILE='\\testdb1\mirror\certSSRSTEST'

WITH PRIVATE KEY(

FILE='\\testdb1\mirror\certSSRSTESTkey',

DECRYPTION BY PASSWORD='TD_123456')

go

報錯The private key password is invalid

異機(jī)創(chuàng)建certificate證書，正確密碼還有報錯，說明需要先在異機(jī)建立master key

use master;

go

CREATE CERTIFICATE certClientData

FROM FILE='\\testdb1\mirror\certSSRSTEST'

WITH PRIVATE KEY(

FILE='\\testdb1\mirror\certSSRSTESTkey',

DECRYPTION BY PASSWORD='654321_DT')

go

報錯Please create a master key in the database or open the master key in the session before performing this operation.

創(chuàng)建master key隨便設(shè)置密碼password = '999_TD999'，創(chuàng)建證書輸入正確密碼PASSWORD='654321_DT'，一切正常

use master;

create master key encryption by password = '999_TD999';

CREATE CERTIFICATE certClientData

FROM FILE='\\testdb1\mirror\certSSRSTEST'

WITH PRIVATE KEY(

FILE='\\testdb1\mirror\certSSRSTESTkey',

DECRYPTION BY PASSWORD='654321_DT')

網(wǎng)站欄目：Sqlserver關(guān)于TDE透明數(shù)據(jù)加密的使用總結(jié)
文章分享：http://aaarwkj.com/article42/pjcghc.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供微信小程序、網(wǎng)站制作、網(wǎng)站策劃、定制網(wǎng)站、軟件開發(fā)、云服務(wù)器

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時需注明來源： 創(chuàng)新互聯(lián)