Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Is there a way to limit the range of dynamic ports available to SQL Server from the database side, or database server side? Our SOP is to use static ports across the network firewall and a vendor is having trouble locking down their ports. Theoretically, if we allowed a range of 1000 ports inside the dynamic range (49152–65535) across the firewall how would I limit SQL Server to only assign a dynamic port inside that range?

share|improve this question
    
I have found a few things about how to mess with the range of ephemeral ports but nothing locked down to SQL Server. –  Cougar9000 Oct 1 '13 at 19:17
3  
I dont think that you can do it from SQL Server side. Windows can be configured to do it, but I doubt SQL Server can a range of IP. I prefer to use non-default, static port for sql server to listen on. –  Kin Oct 1 '13 at 20:05
    
Do you have to use a firewall rule that's port-based? You could use a rule that's program-based instead. (That's a less restrictive policy, but might be sufficient.) –  Jon Seigel Oct 2 '13 at 15:13
    
@JonSeigel it has to be port based. Security won't allow it, and quite frankly neither will I. Vendor claimed they could use static ports during tech review, we signed off on the project, and voila! Now exploring limited options –  Cougar9000 Oct 2 '13 at 16:02
    
Fair enough. Hmmm... since the port number is stored in the registry, you could use PowerShell to read the value and use the Firewall command-line to create a rule to your specs. –  Jon Seigel Oct 2 '13 at 16:39

3 Answers 3

up vote 1 down vote accepted

As Cougar9000 found out himself the usage of ephemeral (dynamic) ports is not managed by the application but by the operating system. You can only change it for the whole server.

The answer to this question does provide you with the information and links you need.

In short you could do

netsh int ipv4 set dynamicport tcp start=50000 num=1000

Please note that making the range too small may result in TCP/IP Port Exhaustion.

share|improve this answer
    
Thanks Matthias. I was fairly certain that would be the case. –  Cougar9000 Nov 12 at 21:39

Here's the start of a PowerShell solution as I mentioned in the comments:

$instanceId = "<Instance ID>";
$registryKey = "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\$instanceId\MSSQLServer\SuperSocketNetLib\TCP\IPAll"
$portNumber = (Get-Item $registryKey).GetValue("TcpDynamicPorts");

netsh advfirewall firewall add rule action=allow localport=$portNumber protocol=TCP dir=in name="SQL Server Database Engine ($instanceId)"

I'm not sure if this is going to be appropriate for the exact situation, but it might help someone out there.

share|improve this answer
    
Thanks for sharing that Jon. –  Cougar9000 Oct 7 '13 at 13:46

I have not found a way to do this, and am going to assume you can't. So, official answer is no, you cannot restrict the range on the SQL side. If any body finds a way to do it or a work around on the server side that works please share.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.