Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

SQL and IIS on the same Box? Expand / Collapse
Author
Message
Posted Friday, June 13, 2003 12:00 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 12:22 AM
Points: 2,923, Visits: 1,873
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/dpoole/sqlandiisonthesamebox.asp

LinkedIn Profile
Newbie on www.simple-talk.com
Post #13194
Posted Friday, June 20, 2003 4:06 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, March 8, 2004 10:31 AM
Points: 181, Visits: 1
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.




Post #66513
Posted Friday, June 20, 2003 4:44 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 12:22 AM
Points: 2,923, Visits: 1,873
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.



LinkedIn Profile
Newbie on www.simple-talk.com
Post #66514
Posted Friday, June 20, 2003 5:06 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, December 9, 2014 5:36 AM
Points: 5,956, Visits: 286
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


--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/
Post #66515
Posted Friday, June 20, 2003 5:07 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, December 9, 2014 5:36 AM
Points: 5,956, Visits: 286
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/
Post #66516
Posted Friday, June 20, 2003 5:21 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 12:22 AM
Points: 2,923, Visits: 1,873
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.



LinkedIn Profile
Newbie on www.simple-talk.com
Post #66517
Posted Friday, June 20, 2003 5:56 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, March 8, 2004 10:31 AM
Points: 181, Visits: 1
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?




Post #66518
Posted Friday, June 20, 2003 6:35 AM


Keeper of the Duck

Keeper of the Duck

Group: Moderators
Last Login: Friday, November 14, 2014 7:14 AM
Points: 6,625, Visits: 1,876
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, CISA, MCSE, Security+, MVP - SQL Server
Regular Columnist (Security), SQLServerCentral.com
Author of Introduction to SQL Server: Basic Skills for Any SQL Server User
| Professional Development blog | Technical Blog | LinkedIn | Twitter
Post #66519
Posted Friday, June 20, 2003 6:45 AM


Keeper of the Duck

Keeper of the Duck

Group: Moderators
Last Login: Friday, November 14, 2014 7:14 AM
Points: 6,625, Visits: 1,876
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, CISA, MCSE, Security+, MVP - SQL Server
Regular Columnist (Security), SQLServerCentral.com
Author of Introduction to SQL Server: Basic Skills for Any SQL Server User
| Professional Development blog | Technical Blog | LinkedIn | Twitter
Post #66520
Posted Friday, June 20, 2003 9:48 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, December 30, 2003 12:00 AM
Points: 92, Visits: 1
The site is a bit slow today. Must be running both IIS & SQL Server on the same box! LOL




Post #66521
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse