This post is intended as a handy reference for me…
The following TCP & UDP ports may be required for SQL Server. Note that these are the default ports; they can always be reconfigured, or set to dynamic (if you’re really unlucky or running named instances). And Named Pipes may not be required.
What’s missing from this list? Please leave suggestions in the comments below.
|RPC (used by SSIS & MSDTC)||135 TCP|
|Named Pipes||445 TCP|
|SQL Server||1433 TCP|
|SQL Server Browser||1434 UDP|
|SSAS browser||2382 TCP|
Note that these are the default ports. To find out which port is actually being used, look at the SQL Server Configuration Manager.
Select the instance in which you are interested (in this case, “DenaliCTP”). Right-click on TCP/IP and select “Properties”:
Here, we have a laptop computer running on a non-standard Port. The settings can be changed here.
And now let’s look at the results from the following T-SQL Query:
select distinct local_net_address, local_tcp_port from sys.dm_exec_connections where net_transport = 'TCP'
Just for kicks, here are the appropriate results from a normally-configured server running SQL 2005:
Oh, and the reason for those “BEGIN TRAN / ROLLBACK” bits? I use Mladen Prajdic’s SSMS Tools Pack, a most excellent freebie that provides all sorts of useful functionality, including the big red configurable line to indicate I’m running against a production server.