AES-256 Encryption

Abhishek Rao 1

Hi,

I Need to know is there a common AES-256 algorithm which support Encryption and decryption from Both C# and SQL server ?

1) Master Key
2) Certificate
3) Symmetric Key
4) EncryptbyKey

Above are already in place in the database for specific columns like password

How do I make the application compatible with Encryption handling data from such columns on the front end.
Eg: Password for a user (in terms of login authentication, change password such operations done through SP calls from the app)

The main question is, can I inherit or derive the symmetric key created in the above method to be used in the app for encrypt/decrypt compatible between the app and db?

I needed to encrypt and decrypt data from both end (SQL and Application)

Can anybody help me with this ?

Thanks,
Abhishek

Scott White 1 Reputation point

2020-12-03T12:58:55.227+00:00

Encryption/Decryption is an expensive operation. If your database will be under any type of load, I would not use the database to perform encryption/decryption operation and would rely on application code to handle those operations for you.

1 answer

Cheong00 3,471 Reputation points

2020-12-03T09:25:40.947+00:00

Most of your questions are answered here. Also check this page for considerations. (Say, you cannot use update statement accross encrypted and unencrypted fields directly)

And then when you need to use it, simple enforce enforce encryption so the SQL Connection is encrypted, and automatically decrypted on your C# code.

No code change other than connection string change is required on application side.
Please sign in to rate this answer.
Abhishek Rao 1 Reputation point

2020-12-03T09:50:53.02+00:00

Thanks for your Answer.

Is it possible to decrypt the value from DB if we encrypt using "Always Encrypted" Method ?

Actually I was looking for Encryption From Application and Sending Encrypted value to Database and also I should be able to decrypt that encrypted data from Application in DB.

Please suggest your feedback

Thanks,
Abhishek

Cheong00 3,471 Reputation points

2020-12-03T10:10:48.897+00:00

Check Always Encrypted with secure enclaves out.

However note that this feature only exists on SQL2019 or later.

Tom Phillips 17,716 Reputation points

2020-12-03T16:00:06.31+00:00

That is not really how you do that. The normal way to accomplish your goal is to use an "encrypted session". Then the communication is encrypted between the client and server. The server encrypts and decrypts the data at rest stored in the database separately from the communication between the client.

https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/enable-encrypted-connections-to-the-database-engine?view=sql-server-ver15

Cheong00 3,471 Reputation points

2020-12-04T01:45:42.767+00:00

True, but then again this feature didn't exist before SQL2016.

Some tangentially related side story: My first boss (the company owner) had asked me if he can do it by the time of SQL2000 because he feared the IT staffs resigned and jumped to a competitor may steal the customer database and bring it to the competitor, and I told him while I can do encryption, we'll not be able to do partial search on it, so thing like "search by address" will no longer function. I imagine he will be big fan for this now.
Sign in to comment