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
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
DECRYPTBYKEY as you need to compare values etc.
Also note that both encryption functions can also have validation data passed like a salt.
DECRYPTBYKEY - https://msdn.microsoft.com/en-us/library/ms181860.aspx