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?
5
-
I have found a few things about how to mess with the range of ephemeral ports but nothing locked down to SQL Server.– Cougar9000Commented Oct 1, 2013 at 19:17
-
4I 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 ShahCommented Oct 1, 2013 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 SeigelCommented Oct 2, 2013 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– Cougar9000Commented Oct 2, 2013 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 SeigelCommented Oct 2, 2013 at 16:39
Add a comment
|
3 Answers
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.
-
Thanks Matthias. I was fairly certain that would be the case. Commented Nov 12, 2014 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.
answered Oct 3, 2013 at 17:38
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.
answered Jan 23, 2014 at 16:54
