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?
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.