I enjoyed your article and am in agreement with most of your points. However I also think there are cases when it is OK to run IIS and SQL Server on the same box -- obviously it should be a reasoned decision -- and I have been dismayed to hear professionals repeat mantras such as "it is bad practice to run IIS and SQL Server on the same box" without any consideration of the context.
I appreciate all your points about the benefits in terms of performance and scaling that can be gained by separating the boxes. I fully share your concerns about the constant stream of new and potentially unstable IIS patches. Also, I think you could make more of the "single point of failure" argument. Even if a webserver is unable to serve the correct content because the sql server machine has crashed, it is probably better that it can still serve static content, or even just a "We've got a problem, come back soon" page.
However, these benefits have to be considered with respect to the additional costs of buying and *maintaining* another machine. Hardware costs may generally be a small fraction of the total cost of an IT system, but there's no need to spend more of your (client's) money than is necessary to get the job done.
I think the security arguments are largely disingenous. If every part of the system is properly locked down and constantly maintained (applying patches, reading security newsgroups, monitoring logs etc.) then you have a fighting chance. Don't do this and you are vulnerable - regardless of how many servers you have.
Firstly, what if the server(s) are hosted in some remote location - a common enough setup for a webserver. How do you remotely administrate the SQL server? pcAnywhere, VNC, EM over TCP/IP? All great potential attack surfaces if not correctly configured -- a bit like running IIS on the machine in fact
Secondly, the comfort of having two physical boxes ("oh great, so if someone hacks IIS they can't get to my SQL box") is not exactly as it seems. They *are* logically connected, and someone with access to a compromised webserver may be able to elevate that webserver's SQL server priveleges - perhaps enough to run xp_cmdshell, or a webtask. Even without elevation they should be able to do anything the webserver can legitimately do -- which means they may be able to access confidential data.
>>A database server tends to be kept secure behind a fire wall with very limited access
>>to its functionality and what functionality is exposed requires password access.
Some webservers sit behind firewalls too
And presumably the webserver is going to have the SQL server password or security token on it somewhere -- if the webserver is compromised, so is this password.
>>If you put SQL Server on the same box as IIS then what happens if a hacker discovers a
>>way to elevate their privileges and gain control over SQL Server?
Is that any worse than if you put yout SQL Server on a different box from IIS and the hacker discovers a way to elevate their priveleges and gain control over SQL Server?
>>SQL Server runs as with administrative privileges of the machine
If you choose to configure it that way ...
>>and has a number of powerful features to read and write to the registry, call operating
>> system commands, talk to other servers etc.
Which can, and should, be removed or disabled.
>>What damage might be caused by someone gaining access to this sort of functionality?
Unlimited damage. That's why I'ld argue that properly securing the SQL Server application is far *more* important than separating the boxes.
Sorry if this appears nit-picky - I enjoyed your article, and I agree that if you can afford two boxes, do it. However I genuinely want to understand the security issues better - and writing this helps me to do that (I think
). Please put me straight if I've got confused over any of this.