Azure multi tenant security - Azure Federated SQL, certs etc

Question

I'm trying to get some advice on how to approach a security architecture on Azure.

Background:

We are looking at building a multi-tenant app on Azure that needs to be extremely secure (personally sensitive data). The app will be accessed by standard browsers and mobile devices.

Security access types:

We have three types of users / access types...

1 - plain old user/password over https is fine, accessing both general, non private SQL plus hosted files

2 - user/pass over https, but need authentication of users via certificates that will be installed on user machines/devices. This level of user will need access to sensitive data which should be encrypted at rest both in database, and also any uploaded files.

3 - same as (2) but with the addition of some two factor authentication (we have used YubiKey for other things - might look towards a phone OTP offering as well)

Most users will only have access to their own tenant databases, however we have "account manager" type users that need access to selected tenant data, therefore we expect that they will need either a copy of one certificate per tenant they serve, or we will have to use some kind of master certificate.

Database type:

From a multi-tenant point of view it seems Azure Federated SQL is a good way to go because (a) we simply write one app with "TenentID" key in each table, and after login, set a global filter that handles the isolate for us (b) we understand that Azure federated SQL actually in the background maintains separate SQL database instances per tenant.(Ref: http://msmvps.com/blogs/nunogodinho/archive/2012/08/11/tips-amp-tricks-to-build-multi-tenant-databases-with-sql-databases.aspx)

Can anyone point to any links or give advice in relation to the approach needed to setup and manage file shares, encryption of SQL and file data at rest, authentication of users etc. (automated management on new user signup pref).

Answer 1

I can't really help on the certificates, but you will indeed need some "master certificate". If you are planning on using Azure website, you can't use your own certificates currently.

Concerning the database setup. SAAS applications build on trust, so you NEVER (EVER) want to be showing or editing the data of using to other users. Therefore I strongly suggest that you don't use the TenantID for each table. This would leave still the possiblity of an attack by a malicious user or an error by some developer.

The only way to get around these risks are

1. extensive testing
2. physical different tables to store each tenant data.

Personally I believe that even with very extensive+automated testing you can't have 100% code coverage against malicious users. I guess I am not alone.

The only way out IMHO is physical different tables. Let's look at the options:

1. different server: valid, but pretty expensive in azure
2. different database: valid, less management overhead but same objection as the previous option - expensive if you have a lot of tenants
3. different schema's: the solution. Think about it...
   • you only have to manage users and there default schema's
   • you can back-up schema's using powershell
   • you can move schema's to other databases with some work
   • You can still digg into SQL federation if you need to.
   • the major drawback is that you will need to support database upgrades for each tenant.

Have you read on azure.com any articles about multi-tenancy? http://msdn.microsoft.com/en-us/library/windowsazure/hh689716.aspx