sql website, encrypt each row with different key.

taz8020 used Ask the Experts™
Hi I have been asked to build a simple site. Problem is the want to hold patient data on that site. Each user will be able to add details about thier patient.

The database is encrypted and has always encrypted on colums. To be extra safe I would like to create a key for each user then use this to encrypt their rows with that key. So if ever there was an attach on the site each users info is encrypted different.

Please advise best way to do this.
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Just use a hash of the patient's name or ID as part of your encryption key. Just make sure it's something that won't change much or at all. Otherwise if that changes then you need to decrypt with the old key and re-encrypt with the new key.


So you would do it in code on the web server? the only thing that worries me with this is then if they want to search on a cloumn you would have to decrypt every row into a temp datatable then search. Is there a way to do it sql side?

That's correct. It's painful, but that's what you were asking for, so...

If you do it all within SQL, then if the DB is compromised then the keys/mechanisms are all compromised, too.

That said, I would usually not encrypt the individual rows. I would just employ encryption at rest on the storage level, and if needed, additionally encrypt anything that didn't need to be searchable.
Acronis in Gartner 2019 MQ for datacenter backup

It is an honor to be featured in Gartner 2019 Magic Quadrant for Datacenter Backup and Recovery Solutions. Gartner’s MQ sets a high standard and earning a place on their grid is a great affirmation that Acronis is delivering on our mission to protect all data, apps, and systems.

You can encrypt the data with pgp and keep the searchable columns in plain text.

That is only actually useful if you do not have the keys. And still breakable given enough time.

Truthfully, i believe you are overthinking this. Even always encrypt is most likely plain useless. As long as the web server does not know the data is encrypted, it makes no difference to a remote attacker.

Adding encryption all over the place is not how you achieve better security. That is rather how you loose focus of the actual issues. Start with stating your requirements.


Hi Both, thanks. That's what worries me the sql server is on the webserver so if someone gets in no matter what I do, the data is compromised.
As users will be entering their patient data I want it to be as secure as possible. Even if I encrypt users rows using userId as key its still all going to be seen if someone hacks the server.
What way would you recommend me make it as secure as possible knowing its all on the same server?

You can't ultimately protect against everything. There is no such thing as perfect security. Security is the art of deterrence. If you are collecting data that is worth breaking through 5 walls for, then you put at LEAST 6 walls up.

For example, if you are worried about someone breaking in and stealing the physical server, then encryption at rest can help mitigate that attack. But if your application itself is vulnerable to SQL injection, for example, then it doesn't matter what encryption you use because the injection will allow a hacker to access all the data by abusing a legitimate channel.

So first and foremost, your application needs to be secure. Perform penetration tests to make sure everything is patched. That is your front line and if it's not secure, then it doesn't matter what else you do.
The problem would be exactly the same should the data be located on a different server. As long as the web server can access the unencrypted data, if the server is compromised, the data will be trivialy available without the encryption even going in his way. Actually, he would not even notice you encrypted anything.

Such encryption would only protect you if someone breaks into premices, and steals the server hard drive.

There is no way past the fact that you cannot expect to be able to both decrypt on the fly and expect a compromised server will not leak. Any one pretending the contrary is eather incompetent or a con artist.

What you can actually do is give the end users the decryption key ( in real life, let them generate the key themselves ), that you would not ever know. If the user looses his key, the data would be lost. and even that would not prevent brute forcing the key. It just might require a few centuries on commodity hardware, assuming the encryption algorythm is not breached.

no encryption level will ever let you past the above.

I am very sorry but your requirements simply do not add up. But i am pretty sure many vendors sell such "solutions". And that is simply a scam.
A real world security would require making your server as impenetrable as possible. Which means use a decent firewall, properly configured reverse proxies, and forget about m$.

A military grade security would have the client encrypt the data themselves using a key that none of your servers would ever know, send parts of the data through different channels ( at lest ond of them not being network based ), on different datacenters, which would run different oses, soft, and hardware, and also different stuff on the reverse proxies and actual servers, add a few IDS, combine well known and custom encryption algorythms, ...

In your case, i believe a single reasonably well configured datacenter, and end to end encryption would be quite decent. If your server is expected to have access to unencrypted data, forget enf to end and focus on securing your premises.


hi thanks again to both of you. the server is held in a data centre where both sql and iis are and they have assured me they have the best barracuda firewalls.
it’s just going to be a fairly simple website but as it stores patients data just want to make sure it’s secure as possible. the website only allows https with a godaddy ssl.

so do you think using always encrypted is enough? I will run some sql injection penetration test too once completed.
Honestly, barracuda firewalls in front of a iis server hosting the db is a joke. W/o encryption. That said, it is very unlikely your data is actually that valuable.

My point is not trashing barracuda. Just stating most attacks will go through any firewall. The firewall brand hardly ever matters except when you got hacked and are facing legal issues yoh had rather reflect on a third party.
I agree - firewalls usually do not make a difference aside from blocking direct access to sensitive internal services. But they usually will not protect the application itself, since they are usually configured to always allow uninterrupted traffic to public ports like HTTP and HTTPS. Even the firewalls that do packet inspection are looking for things that are so defined that they probably will never apply to you.

Firewalls are still useful, though. Again, they do block and manage access to internal services, and they alleviate load from generic attacks, but that's about it.

You have to decide how much security you truly need. For example if it's medical data, then you'll probably need to be HIPAA compliant. The compliance requirements for that will probably exceed what you actually need.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial