Closing port 1433 on a machine in a LAN

I have SQL 2000 installed on one server on our LAN.  SQL is using port 1433.  I want to deny access to SQL for some workstations on our LAN.  I was told by closing port 1433 on the workstations it would stop them from connecting.  I cannot find any information on how to do that.  
I'm not experienced with ports, so simplicity would be appreciated.
levieux7Asked:
Who is Participating?
 
Rich RumbleSecurity SamuraiCommented:
It will work as long as the IP doesn't change on the client's if you apply the ipsec rule I created on the server. if the servers ip doesn't change, then it's probably best to make an ipsec rule that blocks the pc's from reaching 1433 to server ip x.x.x.x (fill in your server ip x.x.x.x) you'd create the rules on all the pc's you don't want to access the server. each pc would have to have a copy of the ipsec rule you create, no adjustment's would be necessary, as you can specify "my ip address" in the rules. I'll put the rule in the same dir and call it block-sql-client.ipsec give me 10 minutes.

-rich
0
 
levieux7Author Commented:
We have some workstations running 2K and others running XP.  The servers are all W2K.
0
 
tonyteriCommented:
ok on one of the worksations that you want to block, go to Contreol panel, Network Connections, and select the properties of the Ethernet connection.  Higlight the TCP/IP and select properties, then ADVANCED, then OPTIONS, then Highlight TCP/IP properties.  Click to enable ip security and only type in the ports you want to allow.

These might be
80-www
53-dns
110
25     email
445
135-139 netbios
443 ssl
3389 Remote Desktop

/TT
0
Simple Misconfiguration =Network Vulnerability

In this technical webinar, AlgoSec will present several examples of common misconfigurations; including a basic device change, business application connectivity changes, and data center migrations. Learn best practices to protect your business from attack.

 
levieux7Author Commented:
OK.  I'm a novice.  I must not have done it right.  I entered the ports you listed under the TCP Ports heading and rebooted.   I can still ping the other servers, but I now am unable to access the internet or shares on my file servers.    There are also headings for UDP Ports and IP Protocols in the TCP/IP properties.   Should I enter any ports or other information there?
0
 
simonenticottCommented:
Hi,

Why not use SQL security to keep people out of the server ? it has very good security control from single DB access right down to fields in tables if you want to go that deep.  We use Windows Authentication to control access to our SQL servers, this lets you setup groups in your Active Directory and those groups control access to various databases etc.  Its very effective and quite easy to setup.

If you want a little more detail on setting up windows authentcation & groups let me know and i'll do a quick step by step to give you the idea.

Simon.
0
 
levieux7Author Commented:
I could do it by user, but in this specific issue I'm focusing on devices, since the SQL CALs are per device, not per user.  I want to lock out devices so I don't have to buy a CAL for every workstation on the network since currently they all can access SQL through the network.
0
 
simonenticottCommented:
Are you using XP pro with SP2 ?

If so you may be able to use group policy to block port 1433 on a selection of PCs using windows firewall.

Simon,
0
 
levieux7Author Commented:
Servers are all W2k SP4.  Workstations are either W2K (SP3?) or WXP SP2
0
 
levieux7Author Commented:
I should have said WXP Pro SP2 for the XP machines.
0
 
simonenticottCommented:
try manually blocking port 1433 on a machine using windows firewall and see if that works for you, if it does you can then look at doing it via group policy and active directory containers.

Simon.
0
 
levieux7Author Commented:
I've gone into the Windows Firewall via control panel, but I sure don't see where to block a port.   The only options on the exceptions tab of the firewall are to "Add Program" or "Add Port".  I can't see any place to block a port.  I am a novice, so it may be very simple, but I just can't see it.

I'm trying to block it using the TCP/IP properties on W2K workstation, as tonyteri suggested earlier, but I'm not suceeding--yet.  I have restricted specific TCP Ports and allowed all UDP ports and IP Protocols, but I can still get to SQL.  If I try to restrict the UDP ports to 53, 69, 137, 161 and 520, I can't access my network shares, though I can ping the servers using an IP address.

Once I get that working, I'd be interested in how to set up the group policies and AD containers.  

I'm more of a programmer that inherited the Network Administration side of things.  So much to learn...  
0
 
simonenticottCommented:
hi,

try adding port 445 (SMB)  to the list of ports you are allowing and restrict all UDP and IP Protocols to see if that helps.

I just remembered that Windows firewall only blocks incoming and not outgoing so you wont be able to use that to block port 1433.  A more advanced firewall would be able to do it but it'll be a lot of work installing and configuring it for everyone.

I did a quick search for port redirectors and have just found this little tool that allows you redirect a port, you could install it on the PCs you dont want to communicate with the SQL server and redirect port 1433 to a differnet port / IP address.  It can also run in "hidden mode" which would be useful.

http://www.kmint21.com/free/

I've not tested it myself.
0
 
Rich RumbleSecurity SamuraiCommented:
What are you people doing... Let me see if I can get everyone up to speed.
Windows firewall on XP or 2003(sp1) is a "Stateful" firewall, it only blocks packets coming into it the PC.
http://www.microsoft.com/windowsxp/using/security/internet/sp2_wfintro.mspx
All connections going out of the PC are allowed, these are called established connections. If the author wishes to block certain workstations from hitting the SQl port, he has three options.
1) block the port on the server for certain ip's (your rule may be "Deny 1.2.3.0 - 1.2.3.50 destination port 1433 to ip address 1.2.3.99")
2)block the destination port 1433 on the pc's(your rule may be "Deny localhost destination port 1433 to ip address 1.2.3.99")
3)block the host(s) from reaching that port using an access list on the firewall or routers on the LAN

Now since you have win2k and XP workstations, you'll need an application that will block the dest port of 1433 to ip x.x.x.x for both, and since XP's firewall can't do this in the first place, you need to install software that can, or you can try to configure the IPSEC firewall built-in to win2k and later (xp 2003)

I do not recommend the IPSEC firewall rules as they are hard to work with, and can be quite confusing, but in this case, we have a simple enough task where we only need to block certain host's from reaching a certain port on one server. The best place to set up the ipsec rules... if you can trust your DNS/WINS servers from going down or making an error, I'd say configure the rule on the server itself.
In this directory http://xinn.org/images 
you'll find a file called block-spl.ipsec, download it, go to start>run and type (this should be DL'd to the server running SQL)
secpol.msc
highlight IP security policies local machine, right-click>all tasks>import polcies... browse to where you saved the policy, and review it. once your done reviewing the policy, you can right-click it to assign it, or right-click it to unassign as well. no restart is necessary the policies take effect the instant they are assigned or unassigned.

This policy should be modified, currently it's set to block the ip address of 10.10.10.10 -12, you'll need to adjust to your ip scheme for testing. Again with the windows firewall on XP or 2003 sp1, you cannot specify certain host's to deny or allow to a port. There is no grainular control for these firewalls, they are all or nothing.
-rich
0
 
kneHCommented:
Win2K has active directory.

You can set the access rules in there.

GroupA can access server X
Group B cannot.

done.
0
 
Rich RumbleSecurity SamuraiCommented:
block-spl.ipsec should read block-sql.ipsec

The problem with the ipsec rules is they convert DNS names to ip's and if you have DHCP and that machine's ip changes then the rule is null and void, rather it's probably blocking the wrong machine. A 3rd patry firewall is overall the best way to block, ZoneAlarm or CheckPoint firewall's, you can use your routers and firewalls on the lan, but typically they require an IP address as well. Reguardless of the solution, you should as suggested above, enable the username password for your sql server so that unauthorized users can't get access.

-rich
0
 
levieux7Author Commented:
To richrumble:  I don't understand the following:  
   "In this directory http://xinn.org/images you'll find a file called block-spl.ipsec, download it, go to start>run and type (this should be DL'd to the server running SQL) secpol.msc"

  What machine am I to run this on, the server or the workstation?
  What does "DL'd to the server" mean?
0
 
levieux7Author Commented:
To kneH:

For clarification:

Am I putting the computers in Groups A and B?

(Forgive the simplicity of my questions, but I'm pretty inexperienced and want to make sure before I make changes to my servers.)
0
 
Rich RumbleSecurity SamuraiCommented:
Dowbloaded = dl'd (also the file is actually named block-sql.ipsec  )
once you've downloaded the file to the Server, open secpol.msc (start>run secpol.msc)
right-click IP Security policies on Local Machine, and go to all tasks>import policies.
You can edit the settings in there, mainly the ip address's to block, again in this example, the address's are 10.10.10.10 through 10.10.10.12

I don't recommend this as the ultimate solution if your using DHCP there, as the ip address is subject to change from time to time. A 3rd party solution such as a software firewall is recommened, but a username and password for SQL databases should keep the users off in general.
-rich
0
 
levieux7Author Commented:
To richrumble:

Am I to understand from your last comment that if I'm running DHCP (which I am) then the block-sql.ipsec solution won't work?
0
 
Rich RumbleSecurity SamuraiCommented:
Dowbloaded = dowNloaded... typo's abound for me... jeez

You could also create an ipsec rule on the local machines themselves, that blocks them from reaching the Destination port of 1433 to server x.x.x.x as long as the servers IP doesn't change. I still feel it's better to password protect the DB's and or get a 3rd party firewall.
-rich
0
 
levieux7Author Commented:
The server's IP won't change, but the workstations' will.  I've already password protected the DB's, but I'm specifically trying to block access from specific workstations to reduce the number of SQL CALs I have to buy.  I'm going to test the ipsec rule on one workstation and see if it works.  I'll try kneH's solution using Active Directory groups, too.  
0
 
Rich RumbleSecurity SamuraiCommented:
youwill have to modify the ip of the destination, i've used 1.2.3.4 in this example. youwill have to do this on each host you install the ipsec file on. after you've imported it, you can right-click it and select Assign, and test to see if it works. you can right-click it to un-assign as well if your not happy with it,
it's copied btw.

-rich
0
 
levieux7Author Commented:
I forgot to mention one other variable:  I've got two NIC's on the server, with two different IP addresses.  I modified your block-sql-client to the first IP and then tried to import another copy of it for the second IP but it just overwrites the first one.
0
 
levieux7Author Commented:
I think I figured out how to add another IP in your block-sql-client.
0
 
levieux7Author Commented:
Success!  I know it's working because I can access a different SQL server at another locationin another state, but I can no longer access the target SQL server.
0
 
Rich RumbleSecurity SamuraiCommented:
Glad to hear it, Ipsec isn't without it's flaws.... if you had a user who was savvy enough, and had admin rights on his pc, you can by-pass ipsec by binding to a src port such as 500 or port 88, then trying to connect to the sql server, they would have sucess read this article to see the fix, but again not many people know how to do this, and even fewer know it can be done.
http://support.microsoft.com/kb/810207/EN-US/
Again, this isn't the solution I'd go with ultimatly, but it is free and does a pretty good job for the most part. if you have nmap installed on a pc with the ipsec rules, try to scan the server with
nmap.exe -sT -P0 -T5 x.x.x.x (you shouldn't see 1433 listed)
but do this and you will
nmap.exe -sT -P0 -T5 -g 88 x.x.x.x  (or -g 500)
-rich
0
 
levieux7Author Commented:
Well, I spoke too soon.  I don't know why, but 15 minutes after I closed the question I retried connecting to the server and I could connect.  I retraced the procedures, even rebooting the machine, but now I cannot block access to the SQL server.
0
 
Rich RumbleSecurity SamuraiCommented:
you can delete the imported rules and try to Download them again and see... but I'm not sure why it'd stop working unless the ip of the server changed... the "my ip address" variable should work reguardless of the ip the clients get from dhcp. Turn off "mirror packets" if adding another rule.  Perhaps the ipsec rules became un-assigned? Again (ipsec) not the recommended ultimate resolution to this issue.
-rich
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.