Getting current connection properties in SQL Server

Question

In MS SQL Server, the Database Properties dialog has the "View Connection Properties" link over on the left. Clicking that brings the "Connection Properties" dialog with properties of the current connection, such as Authentication Method, Network Protocol, Computer Name, etc...

Is there a way to get that information programmatically by running a sql query? What would that query look like?

Answer 1

SQL 2005 and after you interrogate sys.dm_exec_connections. To retrieve your current connection properties you'd run:

select * from sys.dm_exec_connections
where session_id = @@SPID

The field values depend on the protocol used to connect (shared memory, named pipes or tcp) but all contain information about authentication method used, protocol and client net address.

Answer 2

From client tool perspective you could use CONNECTIONPROPERTY:

For a request that comes in to the server, this function returns information about the connection properties of the unique connection which supports that request.

 SELECT ConnectionProperty('net_transport') AS [Net transport],   
        ConnectionProperty('protocol_type') AS [Protocol type];  

DBFiddle Demo

Answer 3

Yes you can, but it depends on which property you are after as the ones displayed in the connection properties UI come from several places.

It uses several queries (such as xp_msver and select suser_sname()) to get hold of some properties, but it also uses the xp_instance_regread stored procedure to get hold of some values from the registry of the server.

Pretty much everything that is done is management studio when interacting with the SQL engine can be done using SQL. Starting a profiler session and doing the actions in the UI will uncover what (sometimes obscure/undocumented/unsupported) SQL is being run.