How to add custom attributes to SQL connection string?

Question

I want to add some custom attributes in SqlServer connection string, something like this:

Integrated Security=SSPI;Extended Properties="SomeAttr=SomeValue";Persist Security Info=False;Initial Catalog=DB;Data Source=SERVER

And then get that attribute in sql. for example SELECT SOME_FUNCTION('SomeAttr')

Answer 1

There is no generalized method to pass custom connection string attributes via Client APIs and retrieve using T-SQL. You have a number of alternatives, though. Below are a few.

Method 1: Use the Application Name keyword in the connection string to pass up to 128 characters and retrieve with the APP_NAME() T-SQL function:

Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=DB;Data Source=SERVER;Application Name="SomeAttr=SomeValue"

SELECT APP_NAME();

Note that this is limited to 128 characters and you will need to parse the payload. Also, since ADO.NET creates a separate connection pool for each distinct connection string, consider there will effectively be little or no database connection pooling.

Method 2: Execute a SET CONTEXT_INFO after connect and assign up to 128 bytes that can be retreived with the CONTEXT_INFO) T-SQL function:

DECLARE @context_info varbinary(128) = CAST('SomeAttr=SomeValue' AS varbinary(128));
SET CONTEXT_INFO @context_info;

SELECT CAST(CONTEXT_INFO() AS varchar(128));

Note that this is limited to 128 bytes and you will need to parse the payload.

Method 3: Create a session-level temporary table after connect and insert name/value pairs that can be retrieved with a SELECT query:

CREATE TABLE #CustomSessionAttributes(
      AttributeName varchar(128) PRIMARY KEY
    , AttributeValue varchar(1000));
INSERT INTO #CustomSessionAttributes VALUES('SomeAttr', 'SomeValue');

SELECT AttributeValue 
FROM #CustomSessionAttributes 
WHERE AttributeName = 'SomeAttr';

Note that you can increase the attribute value size and type as needed, and no parsing is needed.

Method 4: Create a permanent table keyed by session id and attribute name, insert name/value pairs after connect that can be retrieved with a SELECT query:

CREATE TABLE dbo.CustomSessionAttributes(
      SessionID smallint
    , AttributeName varchar(128)
    , AttributeValue varchar(1000)
    , CONSTRAINT PK_CustomSessionAttributes PRIMARY KEY (SessionID, AttributeName)
    );
--clean up previous session
DELETE FROM dbo.CustomSessionAttributes WHERE SessionID = @@SPID; 
--insert values for this session
INSERT INTO dbo.CustomSessionAttributes VALUES(@@SPID, 'SomeAttr', 'SomeValue');

--retreive attribute value
SELECT AttributeValue 
FROM dbo.CustomSessionAttributes 
WHERE
    SessionID = @@SPID 
    AND AttributeName = 'SomeAttr';

Note that you can increase the attribute value size and type as needed, and no parsing is needed.

EDIT:

Method 5: Use stored procedure sp_set_session_context to store session-scoped name/value pairs and retrieve the values with the SESSION_CONTEXT() function. This feature was introduced in SQL Server 2016 and Azure SQL Database.

EXEC sp_set_session_context 'SomeAttr', 'SomeValue';
SELECT SESSION_CONTEXT(N'SomeAttr');

Answer 2

You can use the WSID and APP keywords in the connection string. You can read those values using the HOST_NAME() and APP_NAME() functions. See http://msdn.microsoft.com/en-us/library/ms130822.aspx for details.