Edit

CONNECTIONPROPERTY (Transact-SQL)

  • Article

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance

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.

Transact-SQL syntax conventions

Syntax

CONNECTIONPROPERTY ( property )

Note

To view Transact-SQL syntax for SQL Server 2014 (12.x) and earlier versions, see Previous versions documentation.

Arguments

property
The property of the connection. property can have one of these values:

Value Data type Description
net_transport nvarchar(40) Returns the physical transport protocol used by this connection. This value is not nullable. Possible return values:

HTTP
Named pipe
Session
Shared memory
SSL
TCP

and

VIA

Note: Always returns Session when a connection has both multiple active result sets (MARS) enabled, and connection pooling enabled.
protocol_type nvarchar(40) Returns the payload protocol type. It currently distinguishes between TDS (TSQL) and SOAP. Is nullable.
auth_scheme nvarchar(40) Returns the connection SQL Server authentication scheme. 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 specific connection targeted. Available only for connections that use 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 uses the TCP transport. Is nullable.
client_net_address varchar(48) Asks for the address of the client that tries to connect to this server. Is nullable.
physical_net_transport nvarchar(40) Returns the physical transport protocol used by this connection. Accurate when a connection has multiple active result sets (MARS) enabled.
<Any other string> Returns NULL for invalid input.

Remarks

local_net_address and local_tcp_port return NULL in SQL Database.

The returned values match 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

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