Bineeshcp Viswanath

Bineeshcp Viswanath

  • 2k
  • 140
  • 26.7k

How can decrypt column in SQL SP

Mar 4 2019 3:55 AM
Dear All,
 
I have encrypted a table column and I want to display data decrypt data.
 
I have used below query to encrypt data. I need your help to how I can display data as it is in real.
  1. OPEN SYMMETRIC KEY CreditCards_Key11  
  2. DECRYPTION BY CERTIFICATE Sales09;  
  3. UPDATE Sales.CreditCard  
  4. SET CardNumber_Encrypted = EncryptByKey(Key_GUID('CreditCards_Key11')  
  5. , CardNumber, 1, HashBytes('SHA1'CONVERT( varbinary  
  6. , CreditCardID)));  
  7. GO 
And then the result is coming in my stored procedure is like this:-
  1. SELECT CustomerID  
  2. AS 'Encrypted ID Number',  
  3. CONVERT(nvarchar, DecryptByKey(CustomerID))  
  4. AS 'Decrypted ID Number'  
  5. FROM tblCustomer  
  6. GO  
  7. CLOSE SYMMETRIC KEY UniqueCUSTOMERID 
and the output is :-
 
 

Answers (6)