sqlserver对数据进行加密、解密

sqlserver对数据进行加密、解密 - qingjiawen - 博客园 (cnblogs.com)

创建测试表

CREATE TABLE TBLUser 
(    
    Name             nvarchar(30),     
    Password         varbinary(1000)
)

创建数据库主

 

CREATE master key  ENCRYPTION BY PASSWORD ='passWord'

 

创建证书

CREATE CERTIFICATE TestCert with SUBJECT = 'Test Certificate'

 

创建对称

CREATE SYMMETRIC KEY TestSymmetric WITH ALGORITHM = AES_256 ENCRYPTION BY CERTIFICATE TestCert 

 

使用对称秘钥加密数据

OPEN SYMMETRIC KEY TestSymmetric DECRYPTION BY CERTIFICATE TestCert;
INSERT INTO TBLUser values('张三', ENCRYPTBYKEY(Key_Guid(N'TestSymmetric'), '123456'));
CLOSE SYMMETRIC KEY TestSymmetric;
 

 

解密数据

OPEN SYMMETRIC KEY TestSymmetric DECRYPTION BY CERTIFICATE TestCert;
SELECT Name, CAST(DecryptByKey(password) as varchar(100)) Password FROM TBLUser;
CLOSE SYMMETRIC KEY TestSymmetric;
 

 

HashBytes()函数

select HASHBYTES('MD5','123')

 

加密后 0x202CB962AC59075B964B07152D234B70

 

数据库标量函数 加密

 

 

PassPhrase加密

加密并存储记录用：EncryptByPassPhrase

select   EncryptByPassPhrase('123456', '13888888888')

123456 密码   13888888888 要加密的

取值记录并解密用：DecryptByPassPhrase、cast

select cast(DecryptByPassPhrase('123456',0x010000004495965547C528F75A2B1AA8A572F7F2FDD6834417C6425681F869A0BF2B1EE3) as varchar(max))

 

可以参考https://www.cnblogs.com/atomy/p/14302326.html