SQL Server调用存储过程往数据库中插入明文加密及解密

1.首先需要在对应的库里，创建一个KEY

Use Finance
go

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'xxxwdwdjdhaw$lefjhes234fsfjs'

go
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY

 

go

 

 

2.加密的存储过程

CREATE PROCEDURE [dbo].[EncryptText_sp]
(
@InputText VARCHAR(50)
,@EncryptedText VARBINARY(128) OUTPUT
)
AS
BEGIN
OPEN SYMMETRIC KEY InternalSymmetricKey
DECRYPTION BY CERTIFICATE InternalCertificate

SELECT @EncryptedText = EncryptByKey(Key_GUID('InternalSymmetricKey'),@InputText)

CLOSE SYMMETRIC KEY InternalSymmetricKey
END
GO

 

 

3.解密的存储过程

CREATE PROCEDURE [dbo].[DecryptText_sp]
(
@InputText VARBINARY(128)
,@DecryptedText VARCHAR(50) OUTPUT
)
AS
BEGIN
OPEN SYMMETRIC KEY InternalSymmetricKey
DECRYPTION BY CERTIFICATE InternalCertificate;

SELECT @DecryptedText = DecryptByKey(@InputText)

CLOSE SYMMETRIC KEY InternalSymmetricKey
END
GO

 

 

也可以使用查询语句

SELECT CONVERT(VARCHAR,DecryptByKey(password)) as PassWord,

FROM User