Ports for SQL Server services

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.

Service Port
RPC (used by SSIS & MSDTC) 135 TCP
Named Pipes 445 TCP
SQL Server 1433 TCP
SQL Server Browser 1434 UDP
SSAS browser 2382 TCP
SSAS 2383 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'


As we can see, the results above correlate with what we see in the SQL Server Configuration Manager.

Just for kicks, here are the appropriate results from a normally-configured server running SQL 2005:

and again, the query correlation:

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.

References

(Collected from Shawn Melton’s (Blog|Twitter) answer on ASK SQL Server Central)

Advertisements
This entry was posted in SQLServerPedia Syndication and tagged . Bookmark the permalink.

One Response to Ports for SQL Server services

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s