| title | description | author | ms.author | ms.date | ms.service | ms.subservice | ms.topic | f1_keywords | helpviewer_keywords | dev_langs | ||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
CONNECTIONPROPERTY (Transact-SQL) | CONNECTIONPROPERTY (Transact-SQL) | markingmyname | maghan | 07/24/2017 | sql | t-sql | reference |
|
|
|
[!INCLUDE 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.
:::image type="icon" source="../../includes/media/topic-link-icon.svg" border="false"::: Transact-SQL syntax conventions
CONNECTIONPROPERTY ( property ) 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 [!INCLUDEssNoVersion] authentication scheme. The authentication scheme is either Windows Authentication (NTLM, KERBEROS, DIGEST, BASIC, NEGOTIATE) or [!INCLUDEssNoVersion] 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. |
local_net_address and local_tcp_port return NULL in [!INCLUDEsssds].
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'; sys.dm_exec_sessions (Transact-SQL)
sys.dm_exec_requests (Transact-SQL)
