CODEWITHSUNDEEP
c#encryption
Phakawan Wongperanan
Phakawan Wongperanan

Reputation: 1

I encrypt data by symmetric key in SQL server. How to decrypt by C#

Data stored in an MS SQL server is encrypted using AES128, which is a symmetric key algorithm.

How do I decrypt this data in C# if I know the password?

Upvotes: 0

Views: 900

Answers (1)

Dev D
Dev D

Reputation: 225

You can call a stored procedure which will decrypt the key and return your result. Let you have encrypted a column named "SSN" in your table named "YOUR_TABLE" with password "YOUR_PASSWORD". To return the decrypted SSN this stored procedure will work fine:

DECLARE @SymKeyPwd VARCHAR(50) = 'YOUR_PASSWORD'

DECLARE @open nvarchar(200)
SET @open = 'OPEN SYMMETRIC KEY Sym_ssn DECRYPTION BY CERTIFICATE Cert_Password WITH PASSWORD = ' + quotename(@SymKeyPwd,'''') + ';';

SELECT (CAST(DECRYPTBYKEY(SSN) as nvarchar(MAX) FROM YOUR_TABLE

IF EXISTS (
    SELECT *
    FROM sys.openkeys
    WHERE key_name = 'Sym_ssn'
    )
BEGIN
    CLOSE SYMMETRIC KEY Sym_ssn
END

Call the above SP from your c# code and get the desired result.

Upvotes: 1

Related Questions