Securing a SQL databse account for an ASP.Net site

Question

I am trying to secure a MSSQL database for our .Net website.

I wanted to separate the database into different schemas so that we had an schema like 'Account' which had personal information and a schemas like 'Public' that had generic public content data.

Each of these schemas would be accessed using different SQL user accounts and they would each need their own DBML since they would need separate connection strings(we are using LINQtoSQL).

My colleague is claiming that since we just have both of these accounts in our web.config anyways this design is no more secure than just using one SQL server account that has access to the whole database. There is no need to separate the schemas since we aren't using Windows based authentication.

So my question is who is correct? Would separating the database into schemas be any more secure? Or is it a waste of time since both SQL accounts would be located in the web.config (even if encrypted)?

Answer 1

Well if you encrypt the section the accounts are in, I don't see what would be the problem. The only problem could be on connection pooling, it will fragment the pool in 2 that's it.

Answer 2

Your colleague is wrong on one level and right at another.

Breaking the database into schemas as you propose will help with attackers who are able to find things like SQL injection flaws. If you are diligent in setting your permissions, that is...

However, splitting the schema like this offers no additional benefit if someone manages to get into the actual file system and can red the web.config.

You need to think about all the possible layers.

If you haven't already, check out this great resource:

http://msdn.microsoft.com/en-us/library/ms998372.aspx

Also, try googling storing your connection strings in the registry. There are some good references for that, which will help protect you if someone gets into the file system and can read your web.config.

Finally, don't forget to encrypt your web.config...

Answer 3

Separating the accounts will reduce the risk in case the attack vector is SQL Injection. The attacker will only be able to do whatever is allowed by the priviledges of the session is using to carry on the injection. Presumably only an authenticated attacker can use the Account session as the injection vector so he will be detered because it can be discovered from logs who did it. Wether this separation is an effective mittigation of a threat, it depends a lot on information you left out, most importantly how do you separate access to the two areas in your code.

Having both connection strings in the web.config means that if the ASP pool itself is compromised then an attacker can use both string.

I presume the strings are encrypted none the less, see Encrypting Configuration Information Using Protected Configuration. This way at least the connection info is not lost if the web.config is leaked.