Export (0) Print
Expand All

CONNECTIONPROPERTY (Transact-SQL)

Applies To: SQL Server 2014, SQL Server 2016 Preview

Topic Status: Some information in this topic is preview and subject to change in future releases. Preview information describes new features or changes to existing features in Microsoft SQL Server 2016 Community Technology Preview 2 (CTP2).

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

Applies to: SQL Server (SQL Server 2008 through current version), SQL Database V12.

Topic link icon Transact-SQL Syntax Conventions

CONNECTIONPROPERTY ( property )

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.

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

Note 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.

physical_net_transport

nvarchar(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:

SELECT 
ConnectionProperty('net_transport') AS 'Net transport', 
ConnectionProperty('protocol_type') AS 'Protocol type';
Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2015 Microsoft