Getting current connection properties in SQL Serve

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

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 2

I think the answer is 'no'. Computer information is stored on the computer. Connection information is most likely stored in a configuraton file or in MS SQL Server.

But have a look at the MSSQL system tables and see what properties they have.

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.

Answer 4

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.