Solved

Lock down SQL Server?

Posted on 2004-04-26
14
342 Views
Last Modified: 2012-05-04
I have two separate issues for locking down SQL Server.  First, is it possible to make SQL Server only "talk" to the computer that it's installed on?  I have a small setup where the SQL Server is on the same box as my IIS.  Since it's an intranet application the only access to that SQL Server is from the IIS.  Is it possible to have SQL Server not respond to any requests from any other computer on the network, but still respond to requests made from that box?

Second, my application is scalable, so it will be used to handle thousands of requests in the near future.  If my client institutes a server farm for IIS, then the SQL Server would probably reside on another box.  In that case I would only want SQL Server to respond to requests from the computers running IIS.  They could probably have static IP's if that would help.
0
Comment
Question by:rstone
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 3
14 Comments
 
LVL 13

Expert Comment

by:danblake
ID: 10919098
Have a look at the server network utility -- you can choose what connection string you want to connect from the database...

If you use TCP/IP set to localhost 127.0.0.1 (as the ip address),
you can specify multiple listening TCP/IP inputs for the SQL Server server when moved to your server farm in a similar mechanism.

Why not just place a router/firewall in front- of the SQL Server from the IIS Servers (esp in your web-farm) ?

This will then ensure traffic is only going to the SQL-Server at the highest level of security currently possible.
(Due to the way port traffic is established between sql-server and IIS)
0
 

Author Comment

by:rstone
ID: 10919917
I don't see how to set those in the server network utility.  The only thing that I can set in the properties button of TCP/IP is the port.

In the server farm, a router/firewall is probably the best way to go.  I'll look into that.
0
 
LVL 34

Expert Comment

by:arbert
ID: 10920405
Agree with danblake--get IIS on another box to start with and put a router firewall in place....
0
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 

Author Comment

by:rstone
ID: 10920633
For the server farm separating IIS and SQL Server is fine.  However, in some cases my clients will need to put both on the same box.  If I can just stop SQL Server from listening to network requests, I'll have all of the security I need in this instance.  The reason that I'm wondering about this is that I know some Oracle developers that have done this same thing, so I was curious as to whether SQL Server could do it.

I just don't see how to do danblake's first suggestion.
0
 
LVL 34

Expert Comment

by:arbert
ID: 10920795
" I'll have all of the security I need in this instance"

You can't do this.  SQL Server, by default, will listen on any NIC card that's in the box....Now, if you're not actually connected to an internal network the point is moot.

HOWEVER, it's still a bad idea to have IIS and SQL on the same box since IIS is much less secure than SQL Server.  If IIS gets hacked, you're data is easily accessible.....
0
 

Author Comment

by:rstone
ID: 10920843
Can I use a software firewall, then?

Using IIS and SQL Server on the same box is unavoidable.  I'm sorry if I did not make that clear.
0
 

Author Comment

by:rstone
ID: 10920875
I have a requirement of Windows 2000 or Windows XP Pro, so if something's built into both of those, then I could use that as well.
0
 
LVL 13

Expert Comment

by:danblake
ID: 10920961
IIS & SQL-Server should never really ever ever ever be sitting on the same box.  (Was there enough evers in there arbert ?)

If you MUST place this on the same box, remove all other network protocals from Sql-server in the server client network utility tool, change the port to a non-default port (good idea anyway)..


To ensure it binds to only 127.0.0.1, why not prevent inbound querys/outbound queries on your network card adapter under windows 2000 -- open the network card properties ,

Click the properties of the TCP/IP interface, Go to options, click on TCP/IP filtering...
Click Properties, and Disable all ports in/out TCP/UDP/IP that sql-server uses and only allow the ports in/out that your web-browser is using : 80 (non-secure - http), 443 (secure-ports -https)

http://www.iana.org/assignments/port-numbers
0
 
LVL 13

Accepted Solution

by:
danblake earned 250 total points
ID: 10920998
I have never tested this particular config... (so please by all means give a go...)

this is exactly the same as a software firewall where you are saying that only connections from 127.0.0.1 (local -- loopback address) will be accepted to your sql-server ports, preventing all external ip address from taking part in the talking on the external address.

You can probably use a number of software firewall products that will give you this particular configuration.

But please note just by putting IIS/SQL Server on the same box, due to the poor security of IIS (due to the nature of what it is doing for you) -- you will never get good security in this config, as the permissions will probably be too high.
0
 

Author Comment

by:rstone
ID: 10921567
I'll give blocking out the IP ports a try.

Also, not to be rude, but I did say that it is a requirement that SQL Server and IIS be on the same box, so I don't need to multiple warnings about how it's a bad idea.
0
 
LVL 34

Assisted Solution

by:arbert
arbert earned 250 total points
ID: 10921650
"Also, not to be rude, but I did say that it is a requirement that SQL Server and IIS be on the same box, so I don't need to multiple warnings about how it's a bad idea."

Sorry, but some times it's a good idea to reiterate how bad of an idea it is.....Sometimes requirements are handed to you by clients that haven't been "informed" about how bad of an idea it is--can you put a price on your productional data store?   We wouldn't be doing much of a service to you if we gave you an answer that didn't also list the caveats.....
0
 
LVL 34

Expert Comment

by:arbert
ID: 10921655
You could always use IP filtering within windows as well......
0

Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

740 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question