Can SQL Server and IIS live happily ever after?

March 17, 2009 6:29 PM

Hosting SQL Server and IIS on the same co-located server: Good idea, bad idea, or terrible idea?

I'm looking to reduce the discrete points of failure on my co-lo'd servers. I've got one machine running SQL Server 2005 (with replication) and a separate machine IIS 6.0, both on Windows Server 2003. Our client software talks to both to SQL Server and IIS via ports that are open to the internet, but the two servers do not currently have any need to communicate with one another. We chose to go with this setup primarily as a security measure to prevent exploits for either of SQL Server or IIS from granting someone trivial access to the other, and to date this has worked pretty well.

If we were to host both SQL Server and IIS on the same box, what can we do to maximize our security given that both must be accessible from the internet? Are there any best practices associated with this, or is it just roundly rejected as a bad idea?

I'll do my best to fill in any details I've left out -- I'm not the IT guy, but I've hopefully enough working knowledge of the configuration to provide more context. Thank you!

posted by JohnFredra to computers & internet (8 comments total)

Have you thought about running them on separate virtual servers on the same physical box? This would be one way to maintain the same kind of security separation you have now on a single box, with all kinds of other advantages as well.

Really I'd be much more worried about having SQL ports open to the Internet. Some sort of VPN arrangement would seem to be in order there so that you don't have to leave the port open to just anybody.

It's not unusual to have IIS and SQL Server running on the same server as long as the workload is not too outrageous.
posted by FishBike at 6:38 PM on March 17 [1 favorite has favorites]

Probably not the best idea, but it can be done. SANS generally recommends that web content be segregated from system or data folders so provided you setup the logical (or physical drives, depending on the server) volumes to contain separate content. Try to keep permissions to each specific area.

Also, if the web server is the only box accessing the SQL server, you could restrict access solely to the local box (you could add a internal management workstation as well).

Will clients access the database directly? If so, how will you provide this access?
posted by purephase at 6:42 PM on March 17

It's not great, but you've only one box. The VM solution sounds pretty good.

Confessing up front that my experience is deeper with OSS databases (well, only MySQL), can you not configure SQL server to only listen on the loopback? This would keep the SQL ports off of any of other network interfaces and away from prying h@x0rs. (on preview, what purephase is talking about)

Failing that, a firewall of some sort could do the trick as well. Basically block access to the port(s) for everyone except yourself.
posted by jquinby at 6:43 PM on March 17

One thing you can do is run each service as a different user, and give each user the minimum required file-system privs to their respective directories & the misc OS rights they need (run as a service, etc).

From a performance standpoint, make sure that SQL server memory allocations & IIS resource allocations are limited (max worker threads, etc) to keep one from pushing the other out of real memory.
posted by Good Brain at 6:43 PM on March 17

Make sure that the Application Pool account(s) for your IIS web sites do not have permission to log into to SQL Server. That way your web app will not be able to access SQL server. Also make sure that the SQL service account does not have permission to access the IIS sites.

Other than that, employ standard methods to harden IIS and lockdown SQL server.

There's no reason why these two services can't run on the same machine.
posted by MCTDavid at 6:52 PM on March 17

Will clients access the database directly?

Yes, and we've gone to considerable lengths to make this secure. However, IIS will not need to talk to the database at all. Our clients are installed in pretty diverse network environments that we don't have much control over, so unfortunately a VPN solution is probably not possible. :(

These are all really excellent suggestions for things to consider. Thanks a lot!
posted by JohnFredra at 7:09 PM on March 17

Gonna go against the crowd and say this is not a great idea. You might eliminate a discrete point of failure and simplify your setup, but you still wind up with a big single point of failure. I would look at some clustering/failover or VM solution if you are looking to improve reliability and uptime.

Will clients access the database directly? Yes, and we've gone to considerable lengths to make this secure.

This is worrysome - you have direct access and open, non-encrypted SQL ports on the intenet, and presumably no certificates, how is it secure? You might want to re-think this setup depending on what type of data is in the database. If you had to be PCI compliant, for example, database ports open to the internet would be an instant stop (as would non-dedicated servers, btw). If you have any personal, confidential, non-public data on that database or network segment at all, make sure that your management chain is aware of the risks and present them with an alternative.

Also, here is a Microsoft doc on configuring SQL 2005 security. Includes "Do not expose a server that is running SQL Server to the public Internet."
posted by txvtchick at 9:22 PM on March 17

non-encrypted SQL ports on the internet, and presumably no certificates

I apologize for giving you that impression, but it's not the case. I was just trying to respond in a way that clarified my requirements while putting aside concerns about SQL Server access.

Thanks for the pointer to the MS whitepaper -- I've not seen it before, and it will certainly provide some good information.
posted by JohnFredra at 8:43 AM on March 18