StefanL19 StefanL19 - 14 days ago 6
ASP.NET (C#) Question

How to get decrypted data from a database?

I want to use encryption in order to secure sensitive data in my database. I have already encrypted the data in the SQL database by using Symmetric Key and and Certificate with AES_256 algorithm. Now the problem is that I do not know how to handle the encryption/decryption part in my ASP.NET app. I can receive a string from the user and I should be able to decrypt the string and check whether it is equal to some encrypted value in the database. I get the key by

SELECT KEY_GUID('KeyName')


My question is how to handle the decryption part in my asp app by using this key. I have tried to use this but it does not work.

Answer
DECLARE @KeyName SYSNAME = 'keyName'

IF NOT EXISTS (SELECT * FROM sys.openkeys WHERE key_name = @KeyName)
BEGIN
    OPEN SYMMETRIC KEY keyName DECRYPTION BY CERTIFICATE certificateName;
END

DECLARE @WhatToEncrypt VARCHAR(400) = 'Something To Encrypt can be binary or character'

DECLARE @EncryptedBinary VARBINARY(MAX)
SET @EncryptedBinary = ENCRYPTBYKEY(KEY_GUID(@KeyName),@WhatToEncrypt)

DECLARE @DecryptedBinary VARBINARY(MAX)
SET @DecryptedBinary = DECRYPTBYKEY(@EncryptedBinary)

SELECT @WhatToEncrypt as Original, CAST(@DecryptedBinary AS VARCHAR(400)) as EncryptedThenDecrypted

--may want to add some logic to see if it was open and leave it open
CLOSE SYMMETRIC KEY keyName

The encryption and decryption needs to be done SQL side NOT ASP.Net side when you are using DB Encryption technique as you described. So to implement in C# you would have to basically pass the applicable SQL statements just like you would execute a stored procedure or something.

I would recommend having stored procedures to open and close the keys and then simply use the functions ENCRYPTBYKEY and DECRYPTBYKEY as you need to compare values etc.

Also note that both encryption functions can also have validation data passed like a salt.

ENCRYPTBYKEY- https://msdn.microsoft.com/en-us/library/ms174361.aspx

DECRYPTBYKEY - https://msdn.microsoft.com/en-us/library/ms181860.aspx

Comments