Tom Tom - 11 months ago 57
SQL Question

Getting current connection properties in SQL Server

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 Source

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.