SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by David Poole / SQL and IIS on the same Box? / Latest Posts

RE: SQL and IIS on the same Box?

Steve Jones - SSC Editor — Tue, 16 Jun 2009 12:51:01 GMT

Ok, moved back. Doh! Didn't realize this was a continuation of another thread.One note, if you are not asking a question on the article, you want to start your own thread. This isn't consulting, and it's a little inappropriate to ask a specific person for advice on your own issue.As far as the config, it's hard to know what to recommend. The same size boxes for SQL will obviously give you more resources. you ought to be able to calculate disk space needs (mdf/ldf + 2 days backups at least and pad), for memory, get at least as much, it's cheap now.CPU, go with something modern. You'd have to examine your system and determine how CPU bound it is now. If it's not, then you should be fine with the current CPU

RE: SQL and IIS on the same Box?

Steve Jones - SSC Editor — Tue, 16 Jun 2009 12:34:05 GMT

Moving to SQL 2005 - strategies. Please don't post in author's forums.

RE: SQL and IIS on the same Box?

sid18in — Tue, 16 Jun 2009 12:02:53 GMT

Hi David,It seems I am a entry level developer, and not as experienced *** you to comment on your article. But would surely like to say this was the best article on the topic I am researching on IIS and sql on same box... I loved your article the way you explained all the issues...I would like to get an advice from some one like you... I want to put a proposal to management for separating the iis and sql server...now after reading your article I am clear on that...but i would like to get a knowledge on how much hardware config is required for 2 separate boxes...Let me give you some info on my websites running..i am using a Asp.net based CMS and we have many of our international websites like 15 websites running on the same server...which also has sql server in it....I just got to know that we have around 8000 hits everyday combining all the websites ( which are running on the same one server) Currently our config of the server is ...Intel Xeon E5430 @2.66Ghz..4 gb Ram..70 gb harddisk....We are running out of hard disk space and I have to download the backups daily and its very irritating.... I just joined this firm and feel with their growth they need the upgrade..On our website we have lots of images.. 2 questions... 1-> do you feel current config is enough? 2->if we separate the box what should be the minimum config for both iis and sql server...I would appreciate your help...Thanks,Sid

RE: SQL and IIS on the same Box?

A.J. Bredeveldt — Fri, 04 Jun 2004 05:39:00 GMT

Hi,, I like your article, great and many thanks, I like your writing style,
And then there was this.
.......OK I know that hackers are developers in much the same way that terrorists of which we approve are called freedom fighters!.......
Coming from South Africa and having grown up in an extremely oppressive regime I know that it will take a lot more explaining about what freedom fighters are than you are attempting here.
Keep up the good work!
Antonio

RE: SQL and IIS on the same Box?

currym — Tue, 15 Jul 2003 01:34:00 GMT

Good article. In my environment, security is constantly under review. IIS + SQL (same server) = BAD. Enjoyed seeing the other reasons.

RE: SQL and IIS on the same Box?

K. Brian Kelley — Mon, 23 Jun 2003 06:06:00 GMT

quote:
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!

I cover the basics here, but I do need to update because it doesn't go into specifics and doesn't cover file/registry permissions.K. Brian Kelleyhttp://www.truthsolutions.com/Author: Start to Finish Guide to SQL Server Performance Monitoring http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1

RE: SQL and IIS on the same Box?

Andy Warren — Mon, 23 Jun 2003 04:31:00 GMT

I agree that security is getting tougher (wait, it was always tough!). I'm hoping that the tool vendors will fill in the gaps. Locking down the enviroment is fairly mechanical, it's just there are so many different places.Andyhttp://www.sqlservercentral.com/columnists/awarren/

RE: SQL and IIS on the same Box?

Jonr — Mon, 23 Jun 2003 03:16:00 GMT

Completely agree with the points made about security David, it's usually what you don't know about that the latest hack will take advantage of. Recognising that, whilst at the same time testing and applying the latest patches as they become available keeps you from becoming complacent and alert to the risk.Security issues aside, I've experience running SQL Server 7 & 2000 against IIS and have found that the two most definitely DO compete for memory even on relatively lightly loaded systems. I'm sure it's possible to get a good compromise between the two on a system with plenty of RAM, although personally I don't like limiting the memory that SQL Server can allocate to itself, especially if it is being used in a changing environment and hosting more than just one database, as memory requirements can grow and cause problems you didn't expect when first tuning for optimal memory needs. In a sentence, IIS and SQL Server don't make good bedmates. You can spend more time sorting out the issues than buying a new server.The other practical problem I've found is that third party vendors like to release patches to their (web based) software on a regular basis, and some of these require server re-boots to initialise IIS components. Obviously this is hardly ideal if the database server is hosted on the same box, serving up data to more than a single set of users using different apps, as the outage will take down all of the apps., not just the one that needs patching.However, for hosting internal development sites or a single non-profit making site, with a single database, I would imagine the cost implications will almost always outweight the resiliance arguments. Just hosting SQL Server is expensive, without putting it on a separate box.Edited by - jonreade on 06/23/2003 03:20:56 AM

RE: SQL and IIS on the same Box?

David.Poole — Mon, 23 Jun 2003 02:09:00 GMT

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.

RE: SQL and IIS on the same Box?

Frank Kalis — Mon, 23 Jun 2003 01:45:00 GMT

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

RE: SQL and IIS on the same Box?

ckempste — Sun, 22 Jun 2003 02:44:00 GMT

HiI 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 :)CheersCk

RE: SQL and IIS on the same Box?

jamyer — Fri, 20 Jun 2003 19:21:00 GMT

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!

RE: SQL and IIS on the same Box?

Steve Jones - SSC Editor — Fri, 20 Jun 2003 16:48:00 GMT

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 Jonessjones@sqlservercentral.comhttp://www.sqlservercentral.com/columnists/sjoneswww.dkranch.net

RE: SQL and IIS on the same Box?

geomon — Fri, 20 Jun 2003 09:48:00 GMT

The site is a bit slow today. Must be running both IIS & SQL Server on the same box! LOL

RE: SQL and IIS on the same Box?

K. Brian Kelley — Fri, 20 Jun 2003 06:45:00 GMT

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 Kelleyhttp://www.truthsolutions.com/Author: Start to Finish Guide to SQL Server Performance Monitoring http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1

RE: SQL and IIS on the same Box?

K. Brian Kelley — Fri, 20 Jun 2003 06:35:00 GMT

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 Kelleyhttp://www.truthsolutions.com/Author: Start to Finish Guide to SQL Server Performance Monitoring http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1

RE: SQL and IIS on the same Box?

planet115 — Fri, 20 Jun 2003 05:56:00 GMT

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.aspAnyone else have a different experience / problems when not run as Administrator?

RE: SQL and IIS on the same Box?

David.Poole — Fri, 20 Jun 2003 05:21:00 GMT

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.

RE: SQL and IIS on the same Box?

Frank Kalis — Fri, 20 Jun 2003 05:07:00 GMT

Just have been on my link.There's a superb slogan"There is no 'patch' for stupidity."That says it all!!!Cheers,Frank

RE: SQL and IIS on the same Box?

Frank Kalis — Fri, 20 Jun 2003 05:06:00 GMT

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 http://www.sqlsecurity.com At least it's a good starting point for further investigations.BTW, good article! Something that the End user should understand.Cheers,Frank

RE: SQL and IIS on the same Box?

David.Poole — Fri, 20 Jun 2003 04:44:00 GMT

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.

RE: SQL and IIS on the same Box?

planet115 — Fri, 20 Jun 2003 04:06:00 GMT

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.

SQL and IIS on the same Box?

David.Poole — Fri, 13 Jun 2003 00:00:00 GMT

Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/dpoole/sqlandiisonthesamebox.asp