0

Is there a way to have an always encrypted database?

I mean no system admin, no one to be able to read the data. Once encrypted the data to stay encrypted forever. This is a database for an application with many customers (authentication done via email/password).

Could this be achieved with a PIN that the customer has to enter besides their credentials? The data is encrypted with the PIN and that's it, only if they enter the PIN will customers be able to see their data un-encrypted.

If they lose the PIN that's it, the data is un-recoverable.

zen
  • 79
  • 1
  • 9

1 Answers1

1

The closest thing you can have is Row-level encryption but it can be used in these use cases:

  • A hospital can create a security policy that allows nurses to view data rows for their patients only.
  • A bank can create a policy to restrict access to financial data rows based on an employee's business division or role in the company.
  • A multi-tenant application can create a policy to enforce a logical separation of each tenant's data rows from every other tenant's rows. Efficiencies are achieved by the storage of data for many tenants in a single table. Each tenant can see only its data rows.

But this will not solve your problem because you should create a user per customer while the application will have always its own user.

The most secure thing that SQL Server can provide you is Always Encrypted : in this case the system admin will have no access to the data. But don't expect to have a different PIN per user.

Over all what you are looking for is called Homomorphic encryption where data never get decrypted. But I don't understand why you are trying to do so.

For instance, what is the problem that you are trying to solve here?

Because:

  • "no system admin, no one to be able to read the data" --> that is Always Encrypted
  • "a PIN that the customer has to enter besides their credentials" --> I never heard about such thing and probably is simply under research for now.
Francesco Mantovani
  • 10,216
  • 13
  • 73
  • 113
  • "no system admin, no one to be able to read the data" --> that is Always Encrypted I'd thought so but by using SQL Server Management Studio (SSMS) I can see the data un-encrypted (a matter of selecting a checkbox) Some users might not want their data to be readable even by admins, especially if the cloud application is from a not a large company, others (most in fact) might not care, but I'd like to offer this option regardless. – zen Dec 14 '22 at 15:47
  • Always Encrypted allows clients to encrypt sensitive data inside client applications and never reveal the encryption keys to the Database Engine. This provides a separation between those who own the data and can view it, and those who manage the data but should have no access - on-premises database administrators, cloud database operators, or other high-privileged unauthorized users. As a result, Always Encrypted enables customers to confidently store their sensitive data in the cloud, and to reduce the likelihood of data theft by malicious insiders. – Francesco Mantovani Dec 14 '22 at 15:55
  • You have to set the right permissions: https://learn.microsoft.com/en-us/sql/relational-databases/security/encryption/always-encrypted-tutorial-getting-started?view=sql-server-ver16&tabs=ssms – Francesco Mantovani Dec 14 '22 at 15:56
  • "As a result, Always Encrypted enables customers to confidently store their sensitive data in the cloud, and to reduce the likelihood of data theft by malicious insiders." this is the case for only one customer's data in a database, isn't it? could same approach be used for multiple customers' data in the same table? – zen Dec 14 '22 at 16:11
  • No, that is impossible. You have to create a different database per customers. That's why I asked you if you had customers or users. Costumers are companies with an office that pays you good money and you can afford to have a database per customer. Users just come and go and you cannot dedicate a database per customer. – Francesco Mantovani Dec 14 '22 at 21:08
  • all customers would use the same database, it's a cloud application, there are no separate tables, it would not make technical or financial sense for small and medium customers that usually do not have a lot of data – zen Dec 14 '22 at 23:29
  • Perfect, so Always Encrypted is the way to go. Find the right privileges to give to the System Administrator and you are good to go. There is no such thing as "PIN per customer" – Francesco Mantovani Dec 15 '22 at 07:39
  • Here another question like yours: https://stackoverflow.com/questions/34635135/can-an-admin-see-encrypted-data-with-sql-server-2016-always-encrypted-feature – Francesco Mantovani Dec 15 '22 at 07:41
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/250440/discussion-between-zen-and-francesco-mantovani). – zen Dec 15 '22 at 16:39