1

I'm looking for a solution to encrypt a portion of a database at rest. The database contains data from different companies that should be encrypted with separate keys.

So far, I've considered that TDE (Transparent Data Encryption) could fulfill this requirement if I can find a way to distribute the data across multiple databases.

However, based on my understanding, SQL Server partitioning cannot be used to distribute data across different databases. One alternative approach could be using a view, but I'm unsure about how to handle inserts, updates, and deletes in that scenario.

Do you have any other suggestions or thoughts on this matter?

EDIT:

It's actually a single database, but there are two tables (with about 2M and 3M rows each) that contains data for multiple companies. Those companies are now asking to "encrypt at rest these data", each company with it's own key. They don't want the key to be shared, each company should have it's own key. This is why I've thougth to split data of those 2 tables across multiple DBs, and use TDE.

Users (that are working on those data as "Third Parties") access the data through a Web interface that query DB through nHibernate. From the DB perspective there is a single login that access the database. However should be noted that the user accessing the data is not a concern here, there are already Row Level Security rules that restrict the view of data to the final users.

I've also looked at "always encrypted" but didn't find a way to use different key for the same column, but different rows. Furthermore "always encrypted" will introduce query limitation that are not acceptable by end users.

I believe the concerns are mainly focused on scenario where a DB backup get lost or an attacker get network access to SQL server disks.

5
  • Your problem and question are a little unclear. Is it a single database or multiple databases you're dealing with? Is there a separate table for each Company's data or does all of the Companies data live together in the same tables? Can you provide an example schema of your database? How are users from these Companies currently connecting to the database and access their data? I don't believe encryption is going to secure the data between one Company to another, if their data is all in the same tables.
    – J.D.
    Commented Jun 19, 2023 at 12:26
  • 2
    You can do INSERT, UPDATE, and DELETE statements against a View, but from a security perspective, if the data of all Companies is shared in the same tables, you'll probably want to leverage a feature like Row-Level Security to help enforce security policies that prevent access from a Company to data that doesn't belong to them.
    – J.D.
    Commented Jun 19, 2023 at 12:27
  • Sorry for being unclear. going to edit the question.
    – mCasamento
    Commented Jun 19, 2023 at 12:59
  • No worries, any relevant information you can add is helpful.
    – J.D.
    Commented Jun 19, 2023 at 14:22
  • 1
    That requirement does not make sense; encryption at rest works on entire data files, you cannot encrypt parts of the same data file with different keys. The "scenario where a DB backup get lost or an attacker get network access to SQL server disks" does not become more secure even if you were able to encrypt parts of data files with different keys. Moreover, allowing users to manage TDE keys makes the situation less secure. You should simply use the TDE as implemented by SQL Server.
    – mustaccio
    Commented Jun 19, 2023 at 15:15

1 Answer 1

2

This is why I've thought to split data of those 2 tables across multiple DBs, and use TDE.

This is the only reasonable solution. You can replace the table in your main database with a partitioned view. See eg https://www.brentozar.com/archive/2016/09/partitioned-views-guide/

You can either try updating against the view, perhaps with an INSTEAD OF trigger, or simply load and manage the separate tables directly.

But you should really consider having the application connect directly to each customer's database, in a proper multi-tenant data architecture.

2
  • It works on SQL 2019 Std. I've also tried to do it with SQL Azure, connecting the table through "EXTERNAL TABLE", however I can't set a Primary key on the external table as it says "Primary keys are not supported with external tables for sharded data". Is there another way of doing it with Sql Azure ?
    – mCasamento
    Commented Jun 20, 2023 at 8:56
  • On Azure SQL database you should connect to each tenant’s database directly. Commented Jun 20, 2023 at 11:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.