CONNECTIONPROPERTY (Transact-SQL)

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

Transact-SQL Syntax Conventions

Syntax

CONNECTIONPROPERTY (property)

Arguments

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

  Value	Data type	Description
  net_transport	nvarchar(40)	Returns the physical transport protocol that is used by this connection. Is not nullable. Note Always returns Session when a connection has multiple active result sets (MARS) enabled, and connection pooling is enabled.
  protocol_type	nvarchar(40)	Returns the protocol type of the payload. It currently distinguishes between TDS (TSQL) and SOAP. Is nullable.
  auth_scheme	nvarchar(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_address	varchar(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_port	int	Returns the server TCP port that this connection targeted if the connection were a connection that is using the TCP transport. Is nullable.
  client_net_address	varchar(48)	Asks for the address of the client that is connecting to this server. Is nullable.
  <Any other string>		Returns NULL if the input is not valid.

Remarks

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:

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

See Also

Reference

sys.dm_exec_sessions (Transact-SQL)

sys.dm_exec_requests (Transact-SQL)

Change History

Updated content
Added the Note to the net_transport property about the MARS behavior.