Protecting & encrypting data in a shared database of a multitenant cloud applicaton

Question

What would be the least expensive and PaaS-agnostic ways to protect and separate sensitive data in a multi tenant application which is using a shared database?

Some background info and more specific questions:

We are a small startup company. We have successfully launched an intranet web application project for a customer, and now we feel ready to offer a cloud solution for similar kinds of customers.

As we are on Microsoft BizSpark program, we are investigating Azure App Services. We might migrate later to Cloud Services, but for now it seems that App Services will be enough. Still, we don't want to tie ourselves to Azure too much in case we would want to move to other SaaS provider later.

Our application will store some sensitive information which should be protected. Separate encrypted (Azure provides transparent encryption) databases for each tenant would offer the most security, but we have no budget for such solution and it would be hard to manage automatically.

Currently our plan is that we will offer our customers to register a subdomain under our wildcard domain and then internally we map the subdomain to tenant ID which will be used in each table.

This seems to be the most cost-effective solution for a startup company because there are no additional management for every additional tenant, and registration can be fully automatic. I understand that we'll have to be super careful to enforce the use of tenant ID in every SQL request (using SQL views and stored procs with built-in tenant ID query will help), but that's obviously is not enough. We need some mechanism to protect each tenant's sensitive data with some encryption key.

And then some questions come:

• should we use a single encryption key for all the sensitive data for all tenants? or should we have a separate key for each tenant?

• if we go for separate keys (generated randomly at the moment of registration, so the key won't be known even for us), then who and how should store and protect the tenant encryption key? Should we give the key to the tenant and then ask his employees to provide the key in addition to each employee's login name and password every time they log in through web browser?

• what approach would work best considering that we might later need sharding or "elastic" database scaling as some PaaS providers call it, and that we might move from Azure and Microsoft SQL Server to something else?

If someone has experience with multi-tenancy & database protection, I'd really appreciate some advice, some do's and dont's and possible caveats. I have read some articles about the topic but they often are too specific to PaaS platforms or do not explain possible consequences and difficulties, but that knowledge comes only from day-to-day experience and trials&errors.

Answer 1

Adding some answers for completeness sake (2yrs later):

should we use a single encryption key for all the sensitive data for all tenants? or should we have a separate key for each tenant?

You should have a separate key for each tenant. Not sure how Azure does it but AWS has KMS for this usecase

if we go for separate keys (generated randomly at the moment of registration, so the key won't be known even for us), then who and how should store and protect the tenant encryption key? Should we give the key to the tenant and then ask his employees to provide the key in addition to each employee's login name and password every time they log in through web browser?

Use KMS (or something similar on other clouds, homegrown solution like Square's keywhiz). You don't need to give the key to tenant. All you care about is - if the user has authenticated using their password (or SSO), based on their permissions, they have access to some resources.

what approach would work best considering that we might later need sharding or "elastic" database scaling as some PaaS providers call it, and that we might move from Azure and Microsoft SQL Server to something else?

You need a tenantId which will help with data isolation. Now the actual data linked to tenantId can be encrypted based on a key stored in KMS.