Solved

Lock down SQL Server?

Posted on 2004-04-26
14
338 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
  • 5
  • 4
  • 3
14 Comments
 
LVL 13

Expert Comment

by:danblake
Comment Utility
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
Comment Utility
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
Comment Utility
Agree with danblake--get IIS on another box to start with and put a router firewall in place....
0
 

Author Comment

by:rstone
Comment Utility
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
Comment Utility
" 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
Comment Utility
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
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 

Author Comment

by:rstone
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
"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
Comment Utility
You could always use IP filtering within windows as well......
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Introduced in Microsoft SQL Server 2005, the Copy Database Wizard (http://msdn.microsoft.com/en-us/library/ms188664.aspx) is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

771 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

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now