How can decrypt column in SQL SP

Question

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.

OPEN SYMMETRIC KEY CreditCards_Key11
DECRYPTION BY CERTIFICATE Sales09;
UPDATE Sales.CreditCard
SET CardNumber_Encrypted = EncryptByKey(Key_GUID('CreditCards_Key11')
, CardNumber, 1, HashBytes('SHA1', CONVERT( varbinary
, CreditCardID)));
GO

And then the result is coming in my stored procedure is like this:-

SELECT CustomerID
AS 'Encrypted ID Number',
CONVERT(nvarchar, DecryptByKey(CustomerID))
AS 'Decrypted ID Number'
FROM tblCustomer
GO
CLOSE SYMMETRIC KEY UniqueCUSTOMERID

and the output is :-

Bineeshcp Viswanath · Answer

I have done the below query and I can able to decrypt the data. OPEN SYMMETRIC KEY symKeyPassport DECRYPTION BY CERTIFICATE PassportNoCert; SELECT CONVERT (nvarchar, DecryptByKey(PassportNo_encrypt)) AS Passport FROM tblCustomer CLOSE SYMMETRIC KEY symKeyPassport;

Jignesh Kumar · Answer

Please find below link which will help you, https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/encrypt-a-column-of-data?view=sql-server-2017

Vivek Kumar · Answer

Hi, Please find below links for your reference. https://www.c-sharpcorner.com/blogs/password-encryption-and-decryption-in-sql-sp https://www.mssqltips.com/sqlservertip/2964/encrypting-and-decrypting-sql-server-stored-procedures-views-and-userdefined-functions/ https://www.dotnetheaven.com/article/encrypt-and-decrypt-text-in-sql-server-2008

Sanwar Ranwa · Answer

Download and install this you will get a tool Open its first connect to your database server Select the database >procedure and right click on it and click on Decrypt option

Bineeshcp Viswanath · Answer

hello Sanwar, I could not find the decryption part in the application after downloaded. kindly explain how I can decrypt the data

Sanwar Ranwa · Answer

Hello Please check these Links using this link you can decrypt sp https://www.devart.com/dbforge/sql/sqldecryptor/