SQL and IIS on the same Box?
-
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/dpoole/sqlandiisonthesamebox.asp
-
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.
-
Thanks for your comments.
My concern with "properly" locking down a server, be it SQL, IIS or whatever is where do you learn to do it "properly"?
I've read a great deal on the web about security and have come away humbled and scared because I get the impression that I haven't even scratched the first ice crystal, never mind the tip of the iceberge. I've a horrible suspicion that feeling that I've got a fighting chance against hackers would be as delusional a thinking that I have a fighting chance against Mike Tyson.
I didn't emphasise the single point of failure argument simply because I've been working with database driven content management systems too long and actually don't have much dealings with static content any more. I would go out on a limb and say that most commercial sites whose databases go down would be crippled.
Your points about accessing SQL over TCP/IP is interesting but just for the sake of argument lets make the generalisation that the webserver is designed to be accessed by the public where as VPN access (as I understand it) is designed to be restricted and encrypted. Also consider managed hosting. We are allowed the equivalent of db_datareader and db_datawriter but all other changes have to be submitted to the hosting company for implementation.
My understanding is that the SQL Server service HAS to run with administrative priviledges of the machine on which it is installed?
Let us suppose that someone hacks admin priviledges of the IIS box. Yes they will be able to get at your web app password to SQL Server and can do whatever that login and password has priviledges to, but hopefully that login and password won't grant overall permissions to the SQL Server. In other words they have to beat two or more machines rather than the one.
Restricting xp_cmdshell etc is something I have asked in a recent forum post on removing permissions from the public role within the MASTER database. I didn't know this at the time of writing the article. I did the original SQL6.5 admin course and it was hammered into us that "THOU SHALT NOT TAMPER WITH THE MASTER DATABASE"
I wish that there was a specific book, course or single point reference for SQL Server security.
-
Hi David,
quote:
I wish that there was a specific book, course or single point reference for SQL Server security.tahdah, there is...
I don't know if you know this one
At least it's a good starting point for further investigations.
BTW, good article! Something that the End user should understand.
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Just have been on my link.
There's a superb slogan
"There is no 'patch' for stupidity."
That says it all!!!
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
I used to work on an HP3000 mini computer where the console was the ultimate controller of the mini, then there were administrators and finally users.
There used to be a utility that administrators could run to impersonate different categories of users.
- GOD - Impersonated the console.
- MORTAL - Dropped you back to admin.
- TIT - impersonated a user.
Says it all really.
-
Thanks for your reply.
Having thought about it some more, I guess that separating the boxes makes the SQL Server machine less susceptible to *automated* attacks (simply because there would be many different potential configurations to attack). This is undoubtedly a very good thing.
I think you won me over by generalising that as the webserver is open to the public - on purpose - it will be attacked constantly and there is no need to make it any easier for someone to get to your database if they are successful. However, if the *whole* system isn't secured, I reckon it's just an inconvenience for the determined hacker to leap between the two machines. So I humbly concede the security benefits of the 2 box architecture, with the reservation that the administrator needs to be aware that the physical separation alone does not guarantee the SQL Server box will be unaffected by IIS exploits - the SQL server box should still be locked down as tight as possible. (and the IIS too 🙂 )
>>My understanding is that the SQL Server service HAS to run with administrative
>> priviledges of the machine on which it is installed?
According to MS, this is true only under certain conditions (and then just for SQL Server Agent)- see point number 6 at:
http://www.microsoft.com/sql/techinfo/administration/2000/security/securingsqlserver.asp
Anyone else have a different experience / problems when not run as Administrator?
-
SQL Server can run perfectly fine if it's not an administrator on the system. There are limitations, of course. In reality,not being an administrator on the box is more limiting to SQL Server Agent (the docs say it requires this level of permissions to be able to restart SQL Server or itself should the services be stopped).
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
K. Brian Kelley
@kbriankelley -
quote:
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.This is very true. However, a Day Zero exploit against IIS that allows you to escalate your privs will not automatically mean SQL Server is compromised as well. This is a pretty significant point. In the case when they are on the same server, IIS compromise with escalated privs is more likely to lead to SQL Server compromise with escalated privs.
Also, IIS and SQL Server can be set up to use Windows authentication, even if they don't have any sort of trust. That means going after the SAM and while there are cracking programs out there that are very successful (John the Ripper, L0phtCrack, etc.), it's another barrier. Even with that said, you're still only getting the rights of the account and if the data access layer is designed properly, yes, this means you get access to whatever the web site touches, but not anything else (if your SQL Server is serving up more than just for the IIS box).
Also, consider the case of a two-tier SQL Server hierarchy on the back-end. If IIS passes data (such as Credit Card info) to one SQL Server which then execute some process that writes the data to the second SQL Server, you've got a relatively secure setup if it's done right. Even if the IIS box is compromised, and that means the ability to compromise the first SQL Server, the credit card data should be safe. You can't get directly to the second server from the outside, and this includes through the IIS box. That means going through the first one, and if that only has the ability to write to the second and nothing more, you get the idea.
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
K. Brian Kelley
@kbriankelley -
The site is a bit slow today. Must be running both IIS & SQL Server on the same box! LOL
-
I'm sure Mr. Poole might give Andy, Brian, and myself a small tap on the noggin. We indeed run IIS and SQL on one box, more a matter of economics than preference. Also the hosting costs tend to jump up like Daffy when Bugs sets the dynamite under him.
I agree with the article for the most part. SQL doesn't need to be admin, but it usually is. In either case, your web server and SQL server need to have strong passwords, be patched, and sit behind a firewall. I always recommend separate boxes, mostly because people blame SQL for issues when their application is causing them. Having SQL on it's own box usually limits these arguements.
With W2K3, however, the resilience of IIS has grown along with the ability to stop and start sections of it without affecting other processes. Not that I'd want to do it, but there are cases where you need to.
Steve Jones
-
Great article that addresses a classic question. We, for the most part, keep SQL and IIS apart. There are a few cases where it makes sense, especially for a small uncritical app to run it all on one box.
This will be an article I keep!
-
Hi
I tend to take this article as relating purely to production server envs only, although, it brings up some interesting chnge control and testing problems when you dont have a "similar" config in TEST/Pre-Prod. Anyhow, my experience has shown that the seperate web/app server and DBMS servers are mandatory for any serious app that requires stability and scalability. Many a time has IIS or COM+ gone wild, and perf tuning, problem resolution, and patching is much easiser to manage, measure and plan for in the future running seperated servers. Another key reason is security, remembering the servers inside and outside of the DMZ, private networks etc, all make hacking a damn site more difficult in the long term and can really "save your bacon".
Price is always an interesting arguement in the long term, i have had really cpu intensive apps that generally do bugger all work at the db end, and then people start asking questions about how we can utilise all the grunt we have in all the servers. This is a problem for those IT managers that want to cut some dollars here and there and want to host larger numbers of incompatible apps and other 3rd party SW onto a smaller and smaller number of servers and wonder why there are performance problems.
If you app has definable tiers, is a security/performance concern, differing patch/sw requirements at a variety of levels, always run seperate servers.
Ive never seen how large scale hosted envs work so I cant comment on any of that 🙂
Cheers
Ck
Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA" -
Hi David,
quote:
I wish that there was a specific book, course or single point reference for SQL Server security.http://www.securitytracker.com maintains a weekly security email update, which shows the latest flaws, bugs, security holes discovered.
Not only SQL Server related, but how good is a secure SQL Server when the environment is insecure?
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
quote:
SQL Server can run perfectly fine if it's not an administrator on the system.I think this is worth an article in its own right. When I did the MS courses for SQL6.5 it said that the logon under which SQL Server or SQL Agent runs should be an admin.
Now that this is no longer the case I should like to know how tightly I can lock down the user accounts for my MSSQLSERVER and SQLSERVERAGENT services. Ideally I want them tighter than two coats of paint!
quote:
I'm sure Mr. Poole might give Andy, Brian, and myself a small tap on the noggin.Not unless I was feeling particularly suicidal!
First off I should like to reitterate that the article is intended for a production or non-development environments.
Secondly, it is the stuff I don't know that worries me. I'm not stupid or lazy but there are gaps in my knowledge that could leave my servers exposed to outside attack. I am sure that you three have 99.99999999% of the bases covered with regard to security.
The world has moved on significantly since SQL6.5. You could be a DBA without having to know that much about general NT administration. Now I am convinced that any serious DBA should become Win 2Kx MCSE standard.
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply