实例讲解SQL Server加密功能

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

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

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

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

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

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

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

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));--加密前要转成varbinary 
close symmetric key symc_key_enc_byAsyc --显式关闭对称密钥 
go
 

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 --显式关闭对称密钥 
go
1
<br>