19

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')

Hossein POURAKBAR
  • 1,073
  • 2
  • 15
  • 33

2 Answers2

32

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');
Dan Guzman
  • 43,250
  • 3
  • 46
  • 71
  • I need this to enable session per application instance, I think that only Method 1 will work for me because I am sending data on every connect and other methods will not have previous data on new connect, I presume. – Hrvoje Batrnek Jun 25 '18 at 22:59
  • @Hove, are you saying you need the same information from different connections from the same app instance? Perhaps a variant of method 4 with an application instance identifier instead of SessionID would work for you. – Dan Guzman Jun 26 '18 at 02:09
  • In fact yes, I was going to write some data into the table and bound it to Application Name (GUID for instance) in the connection string, so the combination of Method 1 and 4 and I would write to that table once on application start and update on every data change while this instance is running. I do this so that I don't have to send same data to every proc or query where it is needed. – Hrvoje Batrnek Jun 26 '18 at 11:06
  • @Hove, consider the impact on connection pooling with the connection string difference. This might not be a concern for a small application, though. – Dan Guzman Jun 26 '18 at 11:40
  • It shouldn't be a problem because of one GUID (Application Name) per application instance (start of exe file in Windows), for each instance of an app "Application Name" is fixed. e.g. in web applications, you have only one "instance", you can log in with only one login and you can't log in with another in another tab, with a desktop app I can allow even multiple instances and settings per the same login. It's good for multitasking, especially if you have long-running tasks in one and you have spare time to do something else on another with different settings. – Hrvoje Batrnek Jun 26 '18 at 12:15
  • @DanGuzman you said for method 1 - "since ADO.NET creates a separate connection pool for each distinct connection string, consider there will effectively be little or no database connection pooling." Didn't understand this... Won't all connections made from the machine use this same connection string that has this extra Application Name in it? In that case, why will there be little to no connection pooling? – Punit Vora Jul 25 '18 at 16:09
  • @PunitVora, what I mean this that one can use the `Application Name` connection string keyword for something other than the actual application name. For example, you could specify client name (or client id) for a multi-tenant application. Each client would have a separate connection pool due to the different connection string. – Dan Guzman Jul 25 '18 at 18:13
3

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.

Razvan Socol
  • 5,426
  • 2
  • 20
  • 32