Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Lock down SQL Server?

Posted on 2004-04-26
14
Medium Priority
?
357 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
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
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 

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 1000 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 1000 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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

926 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