Decrypt password in SQL Server query

Question

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

I have encrypted a Password column of my table with the above encryption asymmetric key.

Now I want to decrypt the password column inside my select query

Can anyone suggest how to do this?

Answer 1

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

SQLFiddle don't let users create asymmetric keys, so for demo copy/paste this to your own 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!'));