Securing a SQL Server 2008R2 database

Question

We will be developing an ASP.NET application. It will store data in an SQL Server 2008 R2 installation. Most of the data is sensitive, so security is a primary concern.
We will be hosting this in a shared environment, and it is a design goal that the data should be unreadable in the case of theft.

I am thinking of the following set up: Encrypt the whole database using TDE. Users are created in the SQL Server users table, and we authenticate against that when users log in through the web interface.
The intention is that if someone gets to the database, they will not be able to use the data. And no connection string with user credentials will need to be stored in the web.config file.

Do you see any disadvantages to this approach? And how easy will it be to authenticate against the SQL Server as described?

Answer 1

I do not think it is a good idea to use TDE in a shared environment.

The whole idea of it is to make the encryption/decryption TRANSPARENT to external users using encryption master key (or private key of certificate) on your INTERNAL, belonging and guarded by you, server and preventing to restore the encrypted database on another server without that key. But shared hosting server is not your server, you cannot control access to it or even, more than frequently, even make changes to or write system folders/files or databases. You should consult about it with your hoster.
But if one shared hosting client will be given an exception then there will be another, respectively with access to common system restricted functionalities and areas.

Anyway, anybody from hoster sysadmins or roundabout workers will have access to your database altogether with your master key to restore and read it on another server.

Then, TDE is the feature of Enterprise Edition of SQL Server and sharing hostings are usually provide Express Editions. There is no much sense in sharing Enterprise Ed. server

Update: @Martin Wiboe,
I am not sure that I follow what you are heading at and what you are asking about.

The database encryption keys are encrypted with service master key (and the latter is protected by Windows DPAPI) but there is no point in this on shred environment since the data in memory and on wire are unencrypted and master service key is the one for instance (server).
So, it should be shareable between all users on shared hosting.
You cannot "lock"/"unlock" keys because it is TRANSPARENT data encryption!
Note that if one of database are TDE-ed then tempdb system database is encrypted. This does not make much sense for shared hosting.

1)
Software generated keys are crackable, it is just a matter of persistence/desire vs. time threshold. The real security can be assured only by hardware generated and hardware stored keys. So, shared hosting is out of consideration
2)
You may want to consider to abandon DBMS encryption and encrypt data on client side.
Though this approach has disadvantages that you cannot use SQL Server for searching, optimizing transmission, processing, etc. on server side. Then, what is the point in such DBMS?

Eventually, this all boils down to the fact that there is no much sense in using shared hosting server

Answer 2

Here is an obvious concern. Your application needs to be able to read and understand the data. Therefore, if anyone is able to mess with your application (sniff its communication, decompile it and so on), he can access the database using the same way.
Even if you use some external storage for key data (like Windows Data Protection API), you still need to authenticate to this storage somehow. Therefore, if anyone is able to control the authentication mechanism (for example, Windows domain), can gain the same access.

Basically, if an adversary has complete control of the environment (and they do if you use a shared hosting and try to protect your data from their staff), you cannot stop them from doing whatever they want.

Here are some good overviews of encryption in SQL Server: Understanding Transparent Data Encryption (TDE), Encryption Hierarchy. In any case, you still need a way to store an "ultimate start key" inaccessible to your adversary, but stil be able to use this key in your app.