April 24, 2003 at 5:44 am
Hello.
Well, here's my problem. I have to install a single instance of SQL server 2000 on a machine with two NICs. Currently SQL server listens on both IPs (associated with each NIC) and also on loopback (127.0.0.1).
Now, I would like to bind the instance of SQL server to listen on only one IP/NIC. How can i do that?
Please, don't flame me if this was asked before - i've searched everywhere and came up with nothing.
Thanks in advance for ANY help!
April 28, 2003 at 12:00 pm
This was removed by the editor as SPAM
April 28, 2003 at 12:59 pm
Hmmm, that's an interesting one. I suspect the only way is to block 1433 at the NIC level using the Windows networking setup. Use the IP config for the nIC, advanced and block the port with TCPIP filtering.
SQL used to now listen when you changed IPs, so I was going to suggest booting with one IP and then switching after SQL starts, but that's kludgy.
Steve Jones
April 28, 2003 at 1:59 pm
Curious why you would want to do that?
Andy
April 28, 2003 at 3:06 pm
Another option is to set an IPSec policy and block TCP 1433 coming in to that one IP.
If you go with IP Filtering from the network properties, you end up blocking everything except the ports you specifically want to enable (the default deny). While this is the most secure and best practice way (which is why Steve suggested it), if you aren't sure and don't want to break anything, you may look at the IPSec avenue.
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
K. Brian Kelley
@kbriankelley
April 29, 2003 at 3:42 am
Thanks for all the answers. I thought there must be an easy way to bind SQL server to only one IP.
quote:
Curious why you would want to do that?
Well... I develop ISA 2000 server solutions and currently i'm working on an application that monitors (real time) ALL IP traffic through an ISA server between public, private and DMZ(perimeter) networks. ISA server logs every packet, protocol, etc into SQL database, then the application decides (based on collected data every few minutes) how to prioritize network traffic, bandwidth throttling, etc.. It also automaticaly locks out external sites when intrusion is detected and similar stuff.
There was no problem with deploying this solution on medium to large networks since ussually they all have dedicated SQL servers behind the firewall with giga connections to their proxy (ISA) arrays. The problem arises with branch offices: they don't have (yet) dedicated SQL servers on their LAN, so i installed MSDE on the same machine as ISA server. And, of course, every ISA server has at least two NICs.
Now, I know that ISA server + SQL server(MSDE) on the same machine isn't good nor recomended solution but currently this is the only option i have. I managed to block all external traffic on TCP 1433 with ISA packet filter and this works, however, MSDE now constanly whines with "cannot gain exclusive access on *.*.*.*:1433" (external NIC). I can ignore those errors, but it would all be much more simple, clean and easy if i could bind an instance of SQL server to only one IP, let's say in this case, to 127.0.0.1 for security reasons. Thus SQL server would be visible only to ISA server itself and would not try to bind itself and listen on neither - external or internal NICs.
I've never considered that binding to specific IP would be a problem with SQL server. But, after all, it's not such a critical problem and i can live with it. 😉
Thanx again for all the input.
April 29, 2003 at 5:30 am
Makes sense! Thanks - good to see why the problem exists, sometimes offers opportunities for other solutions. Seems like you could easily host MSDE on another machine somewhere behind ISA, that would duplicate what you'll have once you have dedicated sql box?
Andy
April 29, 2003 at 6:45 am
Since ISA and SQL Server (MSDE) are on the same box, you can probably configure SQL Server not to use a network library. In this case it'll go to shared memory only. That means you won't be able to connect to it from the network but if ISA is the only "user" this isn't a problem. If you navigate to where your MSDE binary files are, look for svrnetcn.exe. This is the Server Network Utility and it'll allow you to specify what network libraries you can listen on.
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
K. Brian Kelley
@kbriankelley
April 29, 2003 at 7:55 am
Heh heh. Hehe. You know what, BKelley? U rulez!
I've just tested this implementation on a clean lab box.
Thank You million times! This is EXACTLY what i wanted. MSDE now starts without any errors in the log and it does not bind to any NIC and does not interfere with ISA.
But that's not all. At first i thought that MSDE will not be able to communicate via network at all. Well, as a server - it can't, but as a client it *can* connect to another SQL server and exports the local ISA DB to data warehouse at corporate LAN. It's just great: ISA can log localy into MSDE, my application can use MSDE localy for on-line data processing and MSDE can also export the DB in the off-hours.
Yeah, life is again wonderfull for me :)))
Well, i own you a beer BKelley. THANX AGAIN!
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply