Firewalls and SQL Server

What is involved in setting up a SQL Server with IIS where SQL Server is behind the firewall. I'm interested in high level configuration not codingWhat is involved in setting up a SQL Server with IIS where SQL Server is behind the firewall. I'm interested in high level configuration not coding

It's pretty straightforward (unless I am totally missing the point of your question). Your web server sits in a DMZ (an isolated network segment with internet connectivity). All traffic to/from this DMZ is filtered/blocked by a firewall. You then add a rule to the firewall to permit your web server to talk to the database server (usually this means permitting traffic over port 1433 from the web server to the database server).

Most firewalls are smart enough to handle the port negotiation process (whereby SQL Server accepts the incoming port 1433 and "hands-off" the connection to a mutually negotiated port). However, in some cases you will have to define additional ports to allow for this negotiation.

You would also be advised to put your DMZ behind a separate firewall blocking incoming traffic from the internet (eg. allowing only port 80 connections to your web server).

Finally, you may need to define an additional rule on the firewall between your internal network and the DMZ which would allow management of the IIS server (eg. allow port 3389 - Terminal Services - from your internal network).

Regards,

hmscott|||your reply was clear and helpful. This leads me to another question.

If say the IIS server gets hacked and the attacker gains control of the IIS server, can you comment on possible ways (if any) to get into the SQL Server?

Assuming:
- IIS code will access via application role
- Application role only has access to intended Stored Procedures
- Stored Procedures used by Application to Access SQL Server

Again looking for high level information.

For example I hear the term sql injection used sometimes which granting access via stored procedures should aleviate.|||Sql Injection is most often due to inappropriate use of parameters in urls or javascript/form variables. Sometimes the application just appends and executes data.

ex. select * from table where x = <some valuefrom the client>

If some one replaces the expected value (like id='fred') with ''fred';drop table sysobjects; commit'

You might get issues :>). If you expect a value, make sure it fits.

SP's are a good start, but if don't validate the values coiming back from a client, you can still get in trouble.

Obviously, you shouldn't be connecting as someone who can do real damage from an application. Assuming you validate the SQL, keep you systems patched, you should be ok. Note that the process for securing an application is NOT this simple, and every situation can have a 'gotcha' depending on what exactly it does. The principle Network and Application architects need to know exactly what they are doing. A forum post doesn't cover it.|||Your question and your assumptions lead in different directions. One thread is if the server is taken over, what exposure does your database have to a hacker? and the second thread is how do I prevent SQL injection attacks (which don't require the hacker to gain control over the server)?

For the first, don't store unencrypted passwords on the web server. Consider using COM+ objects as a "middle layer" on a separate server to access the database. Instantiate the COM+ objects from the IIS server. It's a pain, but it works. Mind you, I think they're called something else in ASP.NET, but I have no real experience in ASP.NET.

For the second case, you have to follow the advice someone else posted in another thread:

1. Validate user input
2. Check user input
3. Validate user input
4. Check user input
5. Validate user input

Validate. Rinse. Repeat.

I might suggest picking a copy of Michael Howard's "Designing Secure Web Based Applications". It's geared towards ASP and not ASP.NET (if I recall correctly), but it's got some really good material.

Regards,

hmscott