SQLServer 資料加密解密:在多個伺服器執行個體中建立相同對稱金鑰(三),sqlserver加密解密
建立相同的對稱金鑰非常容易。使用相同的 KEY_SOURCE、ALGORITHM 和 IDENTITY_VALUE 密鑰選項建立的對稱金鑰將是相同的。
--建立測試use [Temp]go-- drop table EnryptTest create table EnryptTest ( id int not null primary key, EnryptData nvarchar(20), ) goinsert into EnryptTest values(1,N'888888'),(2,N'888888'),(3,N'123456'),(4,N'A'); goalter table EnryptTest add SymmetricCol varbinary(max) go select * from EnryptTest; --刪除測試資訊use [Temp]godrop symmetric key MySymmetric; godrop certificate Mycertificate; go drop master key goalter table EnryptTest drop column SymmetricCol; gouse [Temp]go--建立資料庫主要金鑰create master key encryption by password = N'Hello@MyMasterKey';go--建立以服務主要金鑰加密的認證create certificate Mycertificate with subject = N'EnryptData certificate'; go--建立對稱金鑰create symmetric key MySymmetricwithkey_source = 'Hello kk key_source',--指定從中衍生金鑰的複雜密碼identity_value = 'Hello kk identity_value', --該短語標記使用臨時祕密金鑰加密的資料的GUID algorithm = aes_128 encryption by certificate Mycertificate; go--開啟對稱金鑰open symmetric key MySymmetric decryption by certificate Mycertificate; go --加密資料update EnryptTest set SymmetricCol = encryptbykey(key_guid('MySymmetric'),convert(varchar(max),EnryptData)) go -- 解密資料(正常)select *,convert(varchar(max ) ,convert (varchar(max ),decryptbykey(SymmetricCol))) from EnryptTest go --關閉加密close symmetric key MySymmetric;go--備份主要金鑰backup master key to file = N'D:\Temp_MasterKey' encryption by password = N'Hello@MyMasterKey' go --備份認證(包括私密)backup certificate Mycertificate to file = N'D:\mycertificate.cer' with private key ( file = N'D:\mycertificate_saleskey.pvk' , encryption by password = N'Hello@Mycertificate' ); go--把主要金鑰和認證的備份拷貝到另一台伺服器--把表EnryptTest資料轉送到另一個伺服器執行個體的資料庫中(怎麼做都行)--注意不是使用備份還原遷移資料庫(備份還原資料庫時會存在原有的主要金鑰和認證)
-------------------現在在另一台伺服器執行個體中----------------------------use [temp2] --如資料在這個資料庫中go-- 還原資料庫主要金鑰 restore master key from file = N'C:\Software\Temp_MasterKey' decryption by password = N'Hello@MyMasterKey' encryption by password = N'Hello@MyMasterKey' --New Password go-- 開啟當前資料庫的資料庫主要金鑰 open master key decryption by password = N'Hello@MyMasterKey' go -- 還原認證 create certificate Mycertificate from file = N'C:\Software\mycertificate.cer' with private key( file = N'C:\Software\mycertificate_saleskey.pvk', decryption by password = N'Hello@Mycertificate'); go--建立對稱金鑰(兩個伺服器的對稱金鑰資訊必須相同)create symmetric key MySymmetricwithkey_source = 'Hello kk key_source',--指定從中衍生金鑰的複雜密碼identity_value = 'Hello kk identity_value', --該短語標記使用臨時祕密金鑰加密的資料的GUID algorithm = aes_128 encryption by certificate Mycertificate; go--開啟對稱金鑰open symmetric key MySymmetric decryption by certificate Mycertificate; go -- 解密資料(成功)select *,convert(varchar(max ) ,convert (varchar(max ),decryptbykey(SymmetricCol))) from EnryptTest go --關閉加密close symmetric key MySymmetric;go--刪除測試資訊use [Temp2]godrop symmetric key MySymmetric; godrop certificate Mycertificate; go drop master key;godrop table EnryptTest;go
解密成功:
更多參考:如何在兩個伺服器上建立相同的對稱金鑰