THIS TOPIC APPLIES TO:yesSQL Server (starting with 2008)yesAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

Returns information about the connection properties for the unique connection that a request came in on.

Topic link icon Transact-SQL Syntax Conventions


Is the property of the connection. property can be one of the following values.

ValueData typeDescription
net_transportnvarchar(40)Returns the physical transport protocol that is used by this connection. Is not nullable.

Return values are: HTTP, Named pipe, Session, Shared memory, SSL, TCP, and VIA.

Note: Always returns Session when a connection has multiple active result sets (MARS) enabled, and connection pooling is enabled.
protocol_typenvarchar(40)Returns the protocol type of the payload. It currently distinguishes between TDS (TSQL) and SOAP. Is nullable.
auth_schemenvarchar(40)Returns the SQL Server Authentication scheme for a connection. The authentication scheme is either Windows Authentication (NTLM, KERBEROS, DIGEST, BASIC, NEGOTIATE) or SQL Server Authentication. Is not nullable.
local_net_addressvarchar(48)Returns the IP address on the server that this connection targeted. Available only for connections that are using the TCP transport provider. Is nullable.
local_tcp_portintReturns the server TCP port that this connection targeted if the connection were a connection that is using the TCP transport. Is nullable.
client_net_addressvarchar(48)Asks for the address of the client that is connecting to this server. Is nullable.
physical_net_transportnvarchar(40)Returns the physical transport protocol that is used by this connection. Accurate when a connection has multiple active result sets (MARS) enabled.
<Any other string>Returns NULL if the input is not valid.

local_net_address and local_tcp_port return NULL in SQL Database V12.

The values that are returned are the same as the options shown for the corresponding columns in the sys.dm_exec_connections dynamic management view. For example:

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

sys.dm_exec_sessions (Transact-SQL)
sys.dm_exec_requests (Transact-SQL)

Community Additions