HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMinor

Restrict range of dynamic ports available to SQL Server

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
portsavailablesqlrangedynamicserverrestrict

Problem

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?

Solution

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

$instanceId = "";
$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.

Code Snippets

$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)"

Context

StackExchange Database Administrators Q#50830, answer score: 2

Revisions (0)

No revisions yet.