SQL Server中加密是層級的，每一個上層為下提供保護。如圖：

實例：
/**
SMK(Service Master Key)在SQL Server安裝時生成,由Windows DPAPI(Data Protection API)提供保護
**/

/**創(chuàng)建數(shù)據(jù)庫級別DMK(Database Master Key),受SMK保護**/
create master key encryption by password=N'Passw0rd'
go

/**數(shù)據(jù)庫內(nèi)的加密對象受DMK保護
支持的對稱加密算法:DES | TRIPLE_DES | TRIPLE_DES_3KEY | RC2 | RC4 | RC4_128| DESX | AES_128 | AES_192 | AES_256
非對稱加密算法：RSA_512 | RSA_1024 | RSA_2048
注意避免使用RC,DESX類算法,2014之后會刪除此功能
**/

--1.創(chuàng)建非對稱密鑰.

create asymmetric key asyc_key_enc with algorithm=RSA_1024 encryption by password=N'Pass@word' go

--2.創(chuàng)建對稱密鑰.

create symmetric key symc_key_enc with algorithm=Triple_DES encryption by password=N'Pass@word' go

--3.創(chuàng)建證書.證書也可被其它方式保護

create certificate cert_ENC with subject='certificate for ENC',expiry_date='20990101' go

--4.對稱密鑰可由以上三種方式提供加密保護

--4.1 由非對稱密鑰加密

create symmetric key symc_key_enc_byAsyc with algorithm=AES_128 encryption by asymmetric key asyc_key_enc go

--4.2 由對稱密鑰加密

open symmetric key symc_key_enc decryption by password=N'Pass@word'; create symmetric key symc_key_enc_bySymc with algorithm = DES encryption by symmetric key symc_key_enc go

--4.3 由證書加密

create symmetric key symc_key_enc_byCert with algorithm =AES_128 encryption by certificate cert_ENC go

/**列級數(shù)據(jù)加密和解密.MSSQL提供以下4對加密/解密函數(shù)對列數(shù)據(jù)加密
EncryptByCert() 和DecryptByCert()—利用證書對數(shù)據(jù)進行加密和解密
EncryptByAsymKey() and DecryptByAsymKey()—利用非對稱密鑰對數(shù)據(jù)進行加密和解密
EncryptByKey() and DecryptByKey()—利用對稱密鑰對數(shù)據(jù)進行加密和解密
EncryptByPassphrase() and DecryptByPassphrase()—利用密碼字段產(chǎn)生對稱密鑰對數(shù)據(jù)進行加密和解密
注意:被加密和解密的數(shù)據(jù),必需是varbinary類型
**/

--以ENCRYPTBYKEY為例，其它的大同小異

--對***號IDN進行加密和解密

create table tb(IDN int,Name varchar(20)); insert into tb values (123456789,'BigBrother'),(090807001,'SpiderMan'),(336655789,'SuperMan') go

--新增列Ency_IDN存儲加密數(shù)據(jù),使用之前由非對稱密鑰加密的對稱密鑰symc_key_enc_byAsyc來加密數(shù)據(jù)

alter table tb add Ency_IDN varbinary(128); go open symmetric key symc_key_enc_byAsyc decryption by asymmetric key asyc_key_enc with password=N'Pass@word'; update tb set Ency_IDN=ENCRYPTBYKEY(KEY_GUID('symc_key_enc_byAsyc'),CONVERT(Varbinary,IDN));--加密前要轉(zhuǎn)成varbinary close symmetric key symc_key_enc_byAsyc --顯式關(guān)閉對稱密鑰 go

--解密被加密的列數(shù)據(jù)

open symmetric key symc_key_enc_byAsyc decryption by asymmetric key asyc_key_enc with password=N'Pass@word'; select IDN,Ency_IDN,convert(int,DECRYPTBYKEY(Ency_IDN))as Decr_IDN from tb; close symmetric key symc_key_enc_byAsyc --顯式關(guān)閉對稱密鑰 go1<br>

以上就是本文的全部內(nèi)容，希望對大家的學(xué)習(xí)有所幫助。