Most of the times…We, the DBA’s are concerned about the TCP port which SQL Sever is running under and UDP Port under which Browser service is running under. So…what about the rest of the SQL Services? What is the Def port for SSIS? How about SSAS?
Thought of keeping all together in a single place which might be helpful. (I can use my own post as a single point of reference myself ;p)
Default Ports for SQL Server Services:
SQL Server Default Instance – TCP 1433
SQL Server Browser Service – UDP 1434
SQL Server DAC (Dedicated Admin Connection) – TCP 1434
–You can look into SQL Server Error logs at Start up for this. Eg: sqlcmd –Stcp:<server>,<port> or sqlcmd –S127.0.0.1,1434(if your SQL Server is configured to use only Local DAC Connections).
SSIS/MSDTC/T-SQL Debugger/RPC – TCP 135
SQL Server Service Broker – TCP 4022
SSAS – TCP 2383
SSAS Browser Service – TCP 2382
Note: These are the default Ports which comes with the default Installation of your SQL Server. They can be always reconfigured as required. In fact, Default Port 1433 should be changed and should be included in your SQL build process, Since it is highly vulnerable to attacks.
Let’s see how can we verify our SQL Server Port…..
Verifying Port from T-SQL:
By using the DMV ‘sys.dm_exec_connections’. This will return the current connection details. I mean, If you are connecting to SQL Server remotely via TCP, it will return the TCP port, If you are connecting locally It won’t return anything since you might be most probably connecting using Shared Memory.(If you explicitly connect to your SQL Server using TCP/IP as your Network Protocol even locally, it will return the TCP port details.)
Please see the below Screen shots which i hope are pretty much self explanatory.
Using Configuration Manager: To avoid the confusion of what network protocol currently you are are on, You can always make use of our SQL Server Configuration manager to verify the port details as shown below.
Hope this Post is Useful!