Change TCP port number for SQL Server 2005

SQL server 2005 onward uses dynamic port number which makes firewall configuration difficult.  To change to a fixed IP setting, we can change the registry directly as below. The below example changes the TCP port number to 3546.



Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.2\MSSQLServer\SuperSocketNetLib\Tcp\IP1]
"Enabled"=dword:00000000
"Active"=dword:00000001
"TcpPort"="3546"
"TcpDynamicPorts"="0"
"DisplayName"="Specific IP Address"
"IpAddress"="10.77.186.149"

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.2\MSSQLServer\SuperSocketNetLib\Tcp\IP2]
"Enabled"=dword:00000000
"Active"=dword:00000001
"TcpPort"="3546""TcpDynamicPorts"="0"
"DisplayName"="Specific IP Address"
"IpAddress"="127.0.0.1"

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.2\MSSQLServer\SuperSocketNetLib\Tcp\IPAll]
"TcpPort"=""
"TcpDynamicPorts"="3546"
"DisplayName"="Any IP Address"