Encrypt sensitive information in SQL Server database

[henryvuong]

I am creating a program with C# .NET and SQL Server to make API calls to Amazon. To make these calls, I would need to provide ID and secret codes to Amazon. I store this info in a separate database table. I need to find a way to encrypt this info instead of storing it as plain text. I read about hashing and salting. But to my understanding, this method seems to be one-way. Once you salt and hash them, the codes can not be reversed to their original form. In my case, I need to retrieve the ID and secret codes in their original form from the database. It can either be done with SQL Server commands or C# codes. My program runs on desktops within a local network so the chance of getting an intruder is low. Any form of encryption should be sufficient.

Please advise.

 

[Skydiver]

My program runs on desktops within a local network so the chance of getting an intruder is low

If the machines are on a local network, how are the managing to contact Amazon which is in the cloud. If that local network allows internet access, then you assertion that there is a low chance of having an intruder needs to be re-examined thoroughly.

 

[Skydiver]

Anyway, if your objective is to encrypt the data from intruders, but don't care if the data is not encrypted from view of authorized users, then you could simply enable the many encryption options that SQL Server supports. I think the current approach that people like is TDE:

Transparent data encryption (TDE) - SQL Server

Learn about transparent data encryption, which encrypts SQL Server, Azure SQL Database, and Azure Synapse Analytics data, known as encrypting data at rest.

learn.microsoft.com

 

[Skydiver]

Now, if on the other hand, you want to encrypt just that column that contains the secrets, then any of the built in .NET encryption should suffice for your needs. For example, there are the various AES implementations represented by the abstract base class:

Aes Class (System.Security.Cryptography)

Represents the abstract base class from which all implementations of the Advanced Encryption Standard (AES) must inherit.

learn.microsoft.com

So now you can encrypt that column data, but now you have to keep the key(s) that you used to encrypt that column data a secret. You could encrypt that again using another key, but now you have to keep that key secret. And so it's turtles all the way down.

One way to get around that turtles all the way down problem is to strategically use DPAPI. (See How to: Use Data Protection - .NET ). You could have a common key used to protect that column, but that key is encrypted using DPAPI individually on all the client machines.

 

[cjard]

It doesn't really make sense, unless you're going to keep something in your head and enter it into the program regularly - someone breaks into your network and downloads your DB; oh no! That have your passwords! But fortunately you encrypted the column in the DB so all they got is garbage, and to use the column your app decrypts the data using the master key stored in the config file.. But oh no! The hackers downloaded that too..

There are ways to solve all these things but generally the way we solve it is to not give the keys to the kingdom out to every Tom, Dick and Harry; one server that you control as part of a secure facility should hold the keys that make Amazon do cool stuff, and then that server hosts a web api that the desktop computers contact and ask it to do cool stuff on Amazon. You can do some basic auth between desktop and server but the crux of it is, these precious keys that could wreak havoc on Amazon and do everything Amazon can offer are only ever stored in one place you control tightly, and then that place offers a huge user base the ability to do just one single thing in a very limited fashion so it is hard to abuse. The server never reveals the keys so the client can do its own thing on Amazon, the client asks the server to do work on its behalf, and the server has the opportunity to intervene and say no, if the client is being abusive