6

Context

Let's assume you have a server which exposes a web server and one or more web services to store and manage sensible information about physical persons (assume, for this example, complete medical history but also phone numbers, e-mails and other private information).

Access is authenticated and you did in your code everything you need to reach a reasonable level of security.

Web server and web services are running on a Windows Server with IIS + ASP.NET and databases are in a single SQL Server instance. Assume system is always up-to-date, logs are carefully evaluated and system is properly configured and an attacker has no physical access to machine itself.

Current Architecture

Even if not directly related to DB also remember that:

Data are stored in N+3 different different databases (each one configured with exactly required permissions, no sa-like access, as described How to secure database table of users for an application?).

  • One database for logs (write-only for web server accounts).
  • One database to store login information (readonly for web server accounts, a different Intranet web application will run with a different user).
  • One database to map login with a physical database (and other internal stuff to manage accounts), again read-only for external accessible web application.
  • One separate database for each user of the system (read and write for everyone).

Question

To use three different SQL Server instances (one for logs, one for accounts and mapping and one for all user databases) will increase security or just complexity?

Will this also affect performance? (If you can't answer this without more context, you may simply ignore performance issues unless they're obviously much worse)

Moreover is there any drawback to merge together mapping information and accounts? (separate databases with same permissions will increase security in any way?)

Considerations

I know that in security often "more is better" (at least it's a common motto) but drawbacks may be greater than any benefit (if any):

  • Increased cost for hardware and software.
  • Increased complexity (both for setup and maintenance), this is IMO a big drawback because a (possibly) safer system with a non-optimal configuration may be much less secure than a simpler one.

My perplexity is because if an attacker will be able to run arbitrary code (because of a bug in my application or because of an exploit) then it doesn't matter where things are: it has all resources to do what he wants, I assume we won't detect attack quickly enough to stop service then time he'll need to understand there is another machine to connect to is small compared to total time he has to perform his actions.


I don't know if this question is strictly on-topic here, it seems to span across multiple SE sites and I'm not sure which one is right one.

  • 1
    I don't think multiple instances would increase security. It could decrease vulnerability though - because if one server is hacked, the damage is limited to that server. But then: if there is a security hole, it's most likely that the other servers will be hacked as well. – a_horse_with_no_name Jul 13 '15 at 14:52
  • @a_horse_with_no_name it's what I thought (at first): if he injects malicious SQL code (somehow) then to have separate DB, instances and/or servers is better. Then I realized...well if he can inject something then I'll just slow down him for 10 minutes, is it enough to do something? Cost I'll pay for this is repaid? I'm not even (so) sure a separate server for SQL is SO required. – Adriano Repetti Jul 13 '15 at 15:38
2

In general multiple instances do not increase security, only complexity. There are reasons to use multiple instances but I don't think they fit your situation.

  • You have different groups of users that need administrative (sysadmin, securityadmin etc) access.
  • One of your databases needs to be on a different side of the firewall than the rest. I'm honestly not sure why this might be necessary but I've seen it done.
  • DR/HA
  • Wildly different use cases. For example
    • Reporting data vs OLTP (basically splitting a load)
    • One database is high transaction and you want optimize for ad hoc turned off while the rest you want it turned on. (requires different server level settings for best performance)

You will note that only one of these has anything to do with security and it's going to be pretty unusual. Generally those types of system privileges go to one team. Sometimes however you need to isolate an instance that is for a vendor package and you have no choice but to grant the application sysadmin.

As far as performance for multiple instances goes, well, there is one fairly obvious consideration. Each instance has it's own overhead. The amount of memory/system resources required for multiple instances will always be higher than for a single instance. If you are willing to pay the price for the additional hardware then that shouldn't be a big issue. The other performance issue comes into play when you have data on multiple instances that needs to work together. For example writing a query that ties your log information to your login information. If the data is on two different databases then you have no unusual performance issues. If on the other hand you are using two separate instances you have to either use a linked server (performance + security issues) or load all of the data into your application and use the application to sort it out (can work for small amounts of data but anything past that is going to give you a massive headache).

  • My upv but I have one more question: I'm in DR scenario, will multiple instances help on this (if hosted on same machine)? – Adriano Repetti Jul 13 '15 at 14:52
  • No, for a DR scenario you typically want multiple boxes not one box with multiple instances. It's all about what you are protecting from. Clusters for example protect you if the box goes down but not if the drive goes bad. I was only including it for completeness. – Kenneth Fisher Jul 13 '15 at 15:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.