Securing a SQL Server 2008R2 database

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开发者_StackOverflow 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?

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

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.

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.

Ok, I stopped laughing here. Basically you try to make a vegetarian cow steak.

EITHER security is your primary concern, then you should not use a shared hosting scenario but should run in a certified data center in a seaparate locked rack with no management access by the hoster. In this case you would use a higher version SQL Server and use disc encryption + a secure system (TPM chip) to make sure after a theft the data is unreadable on the discs. Point.

Or security is not your concern then you can use a shared hosting provider.

Anything else is hogwash. The level of "no, waterboarding is not torture because it does not cause physical damage" type of argumentation.

Your problem is: you can do NOTHING to stop access to the data. As your application must be able to read the data unencrypted and thus has access to the keys used tp encrypt... anyone breaking into your application has access to the keys (which is why TPM's are so nice - this is safe hardware, no way to get access to the keys).

You can play around this fact, with all kinds of funy arguments, but if the data is stolen the arguments will NOT hold in any legal discussion. And you will look bad on top of this.

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.

Ok, so - I steal the database. I also steal your authentication mechanism. What exactly do you achieve? Besides killing your search functionality? As I ahve the database iH ave the users table and I can somehow authenticate as user. If I also steal the application I get prettym uch all things, even the code.

UNLESS you have a special case in which you dont have the application keys (things like LastPass which dont know how to decrypt your data on the server side themselves). Shared hosting environments just dont have the right level of hardware for this.