sql - 解密 SQL Server 查询中的密码

Question

CREATE ASYMMETRIC KEY Asym_EPassword  
WITH ALGORITHM = RSA_512 
ENCRYPTION BY PASSWORD = testdemo123

我已使用上述加密非对称 key 对表的Password 列进行了加密。

现在我想解密 select 查询中的 password 列

有人可以建议如何做到这一点吗？

Answer 1

SELECT CONVERT(NVARCHAR(100), 
    DecryptByAsymKey(AsymKey_ID('Asym_EPassword'),
    YourColumn, 'testdemo123')) AS PlainText
FROM YourTable;

SQLFiddle 不允许用户创建非对称 key ，因此为了演示，请将其复制/粘贴到您自己的 SSMS:

CREATE ASYMMETRIC KEY Asym_EPassword
WITH ALGORITHM = RSA_512 
ENCRYPTION BY PASSWORD = 'testDemo123!'

DECLARE @PlainText NVARCHAR(100)
DECLARE @CipherText VARBINARY(MAX)

SET @PlainText = 'AAAAA'
SELECT @CipherText = EncryptByAsymKey(AsymKey_ID('Asym_EPassword'), @PlainText);

SELECT @CipherText;

SELECT CONVERT(NVARCHAR(100), DecryptByAsymKey(AsymKey_ID('Asym_EPassword'), @CipherText, N'testDemo123!'));