adding specific port + default

Question

adding specific port + default

cmosentine

Old Hand

Points: 331
More actions
January 6, 2015 at 4:57 am

#293446

Hi all: I have been reading some docs over at MS and I am a bit confused. We have an MSSQL 2008 R2 server which a vender needs access. They have provided me with a small appliance (small form-factor PC) which will query the server and send data to/from their processing facility (medical billings of some sort).
I don't want to place their equipment inside our LAN but would rather have it in the DMZ. To do this I want to enable the SQL server to listen on a specific port used only by this PC. I would then open a hole in the firewall allowing ONLY traffic from that PC (read IP address) to the SQL server (IP address) on that port only. However, I ALSO want the server to use dynamic ports for all my clients on our LAN.
The MS docs I have read (limited number) seem to indicate that the SQL server cannot have both a specific port AND dynamic ports enabled at the same time. Is this so? Can you point me to some good docs that describe how to do this? Can I multi-home the SQL server and have the new IP address listen only on a specific port?
Thanks much, Chris.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

george sibbald SSC Guru Points: 104210 More actions · Answer 1

is this a named instance?

If so even though the port assignment is dynamic, the instance will always try and assign the same port it obtained on its very first start up. It is rare for it to be unable to get the same port.

I would change the port assignment to fixed using the same port number as the dynamic assignment for your current users and assign another fixed port for this one pc, they would then connect using this port number.

In SQL server configuration manager, sql server network configuration, protocols for mssqlserver, TCPIP, in IPAll set comma seperated port numbers SQL will listen on in TCP port.

A SQL restart will be required

---------------------------------------------------------------------

cmosentine Old Hand Points: 331 More actions · Answer 2

Thanks for the reply. I came up with another idea which I have partially tested.

While playing with a test server, I created an alias (TESTDMZ) pointing to the target instance and assigned it Port 1500. I then went to the target instance and added TCP Port: 1500 to IPALL AND left the the Dynamic TCP Ports to whatever number it had been assigned. Finally, on a test client I created a new System DSN using the new alias and port number (SERVER\TESTDMZ,1500). I was able to connect to the server using the original DSN (SERVER\INSTANCE) as well as the new DSN (SERVER\TESTDSN,1500).

Now once I move the client computer into the DMZ I will create an ODBC connection but replace the server name with the IP address of our firewall's private interface like this:

AAA.BBB.CCC.DDD\TESTDSN,1500

I can then write a firewall exception which passes all traffic from the client IP/PORT in the DMZ to the SQL server inside the LAN (and vise versa).

Does this sound workable??? Chris.

george sibbald SSC Guru Points: 104210 More actions · Answer 3

it does. - cons - more complex, pros - more obscurity and flexibility.

Check SQL is listening on both ports (it will be in the SQL errorlog on startup.)

---------------------------------------------------------------------

cmosentine Old Hand Points: 331 More actions · Answer 4

Thanks again: Complexity is not my main concern. I trust no one, especially bringing in an outside vender's equipment to the LAN.