Some time back there were some connectivity related questions thrown at me:
Q1. We know about the Dedicated Admin Connection (DAC) and how it is useful for troubleshooting service hang issues. We also know that DAC should only be used when necessary. Can we find out if someone is misusing the DAC and staying connected all the time?
A. You can use the following query from a normal query window to check:
select * from sys.dm_exec_sessions
where endpoint_id IN
select endpoint_id from sys.tcp_endpoints where is_admin_endpoint = 1
Do note that this assumes that the service is working normally and that you can connect normally 🙂
Q2. “I have disabled the named pipes protocol on server but I still find the server ‘listening’ on named pipes. See errorlog snippet below for what I mean”:
2007-05-23 11:36:56.69 Server Server local connection provider is ready to accept connection on [ \\.\pipe\SQLLocal\INSTANCE1 ].
2007-05-23 11:36:56.69 Server Server local connection provider is ready to accept connection on [ \\.\pipe\MSSQL$INSTANCE1\sql\query ].
The “local connection provider” refers to the shared memory protocol. If named pipes is genuinely enabled, you will receive an entry similar to:
2006-05-09 07:37:12.57 Server Server named pipe provider is ready to accept connection on [ \\.\pipe\MSSQL$INSTANCE1\sql\query ].
So, do not worry about it – named pipes is not enabled 🙂
Q3. “I have configured a fixed TCP port for SQL to listen to but I still find entries in the errorlog which seem to indicate dynamic ports being used:”
2007-05-23 12:27:42.84 Server Server is listening on [ 127.0.0.1 <ipv4> 1115].
To disable dynamic ports, use a blank value (remove the 0) from the Dynamic Port entry. However, you would still receive such an entry for DAC like this:
2007-06-15 17:03:16.63 Server Server is listening on [ 127.0.0.1 <ipv4> 1509].
2007-06-15 17:03:16.63 Server Dedicated admin connection support was established for listening locally on port 1509.
Hope these answers are useful!