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
2  
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
add comment

2 Answers

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
add comment
up vote 1 down vote accepted

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
add comment

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.