Encrypting Data In Sql Server Azure Database With Separate Key For Each User's Data

I'm trying to create a service based on an Azure SQL Database backend. The service will be multi-tenant, and would contain highly sensitive information from multiple 'clients' (pot

Solution 1:

You're looking at protecting/isolating the tenants "by design" in a single table, why not check out Row Level Security. You could configure it to serve up only the applicable rows to a specific tenant.

This doesn't directly address your initial question about encrypting the data with a separate key for each tenant; If you have a separate table for each tenant, then you could do this via Always Encrypted, but this would seem to have some complexity in key management, if you're trying to handle 200k keys.

AFAIK, there isn't a native SQL Server functionality to encrypt each set of rows that belongs to a tenant with a distinct key- but there may be some elegant solutions that I haven't seen yet; Of course, you could do this on the app side and store it in SQL and there would be no issues; the trick would be the same as the AE based solution above- managing a large number of keys.