question

ShashiBhosale-2805 avatar image
0 Votes"
ShashiBhosale-2805 asked CarrinWu-MSFT commented

connecting to SQL Database from asp application and setting CONTEXT_INFO

connecting to SQL Database from asp application and setting CONTEXT_INFO. <connectionStrings><add name="CLHN_AppConnectionString" connectionString="Data Source=server1;Initial Catalog=CLHN_App;User ID=sa;Password=dsfsdfsfsdfs0" providerName="System.Data.SqlClient" /></connectionStrings>I would like to know if there is any way to set CONTEXT_INFO from the connection string OR there is any way to set CONTEXT_INFO every time there is a SQL connection made from asp application .

sql-server-general
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @ShashiBhosale-2805, we have not get a reply from you. Did any answers could help you? If there have any answers helped you, please do "Accept Answer". By doing so, it will benefit for community members who have this similar issue. Your contribution is highly appreciated. Thank you!

0 Votes 0 ·
cooldadtx avatar image
0 Votes"
cooldadtx answered cooldadtx commented

The connection string just sets up the session data. I assume you're doing this for auditing purposes. In general your auditing should start with context data and fall back to session data if it isn't set. Something like this works for getting the current user from CONTEXT_INFO, if available or the session (connection string) otherwise.

COALESCE(REPLACE(CONVERT(VARCHAR(128), CONTEXT_INFO()), CHAR(0), ''), SUSER_NAME())


CONTEXT_INFO is contextual whereas the session data last for the entire session. To set context info you have to explicitly do it. Here's the simple SQL code we use although you can do it inline as well.

DECLARE @v_Context_Info BINARY(128)
SET @v_Context_Info = CONVERT(BINARY(128), @p_User)

SET Context_Info @v_Context_Info


However this is contextual so that means it is valid only for the context. In the case of ADO.NET that is the DbConnection. Therefore you have to open the connection to the DB, set the context info, do your SQL work and then close the connection. If you're using any sort of automated cleanup (which you should be) then this becomes harder. Specifically your code needs to look like this:

using (var conn = new SqlConnection(...))
{
    //Set user context

    //Do work
}


If the connection is closed then the context info is reset. This makes it harder to use things like EntityFramework but it is doable.

What we do is we have a custom data layer that wraps the DB connection (for other reasons). As part of the open call to the DB we auto-set the context info if a user name is specified (which is all configured using IoC). The downside to doing this every time is context info is only important when doing DB updates (for auditing at least) and more queries happen then updates. In our data layer we use heuristics to only set the context if we are doing an update to the DB otherwise we don't waste the time. But it is going to depend upon your DB layer.

A simple solution, if you don't have an access layer is add an extension method to the connection. Depending upon whether you know the context to set or not determines how you'll implement it but something like this:

public static class ConnectionExtensions
{
   public static void OpenForWriting ( this DbConnection connection, string userName )
   {
       connection.Open();

       //Set user context

   }
}

//Usage
using (var conn = new SqlConnection(...))
{
   conn.OpenForWriting("someUser");
}


You can get more fancy as you need it.

If you're using EntityFramework then create a derived DbContext class and override the saving changes method to open the connection and then set the context.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

I should point out that CONTEXT_INFO seems deprecated in newer SQL versions. I think SESSION_CONTEXT is preferred now but you can read up whether you can use it or not.

0 Votes 0 ·
ShashiBhosale-2805 avatar image
0 Votes"
ShashiBhosale-2805 answered cooldadtx commented

Thanks for all the suggestions.
Here is what happened. We use a ERP system and that uses sql database.
There use to be a table called co and data resided in that table. I used the table in my asp application (example select * from co).

With the new version of ERP , they changed the table to CO_MST and there is a view called co which is based on co_mst table/data. the view defination is - select * from co_mst where [site_ref] = CAST(CONTEXT_INFO() AS NVARCHAR(8)). The new version did the same thing for all 100's of tables. This is a big DB sturtcure change they made.

In order for my application to work i will need to set context_info (i have the value of the string) every time DB connection my asp application makes.

Thats why i was wondering if i can somehow set context_info in my connection string so that the session will have the context_info value set as per my needs.

I hope i have explained my situation here.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Yes you have explained it well but it doesn't change the fact that you cannot auto-set context info in a connection string. That isn't supported. You can google around and find everybody saying the same thing and providing a similar workaround.

Connection string sets session data only. To set the context info you have to do it explicitly. If you want to use connection string (assuming you're using Windows auth where it would matter) then you'll have to switch from CONTEXT_INFO to SUSER_NAME as I mentioned in the fallback approach. This value is what is the connection string will ultimately set.

0 Votes 0 ·
ShashiBhosale-2805 avatar image
0 Votes"
ShashiBhosale-2805 answered cooldadtx commented

Thanks for the feedback. Is there a way to set the CONTEXT_INFO after connection is established on the sql side?
I can always check the hostname of the SPID and set the context _info.

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

I'm not aware of a way on the SQL side to run custom logic when a connection is established. But even if it is possible bear in mind that ADO.NET doesn't work that way. Once ADO.NET establishes a DB connection to SQL it won't close it. This the connection pooling that you read about. As far as SQL is concerned there is a single connection to the DB that is persisted irrelevant of your open/close calls in .NET. This is for performance reasons. Yes you can disable that but your DB connection performance will plummet so that is not really an option. So even if you can implement a connection event in SQL it would get called once (per .NET connection). If this works for you then fine.

0 Votes 0 ·

I'm going to reiterate my earlier thought that if you cannot write code on the .NET side to handle connection logic then you are limited. The best you might be able to do is use User Id in the connection string as that is one of the few pieces of data sent to SQL. Connection strings are for the driver, not SQL so none of that data is sent to the DB (at least directly). A connection string is really a driver configuration.

I don't see how you're going to be able to get this to work without either moving away from CONTEXT_INFO or writing some client-side code.

0 Votes 0 ·
CarrinWu-MSFT avatar image
0 Votes"
CarrinWu-MSFT answered

Hi @ShashiBhosale-2805,

Welcome to Microsoft Q&A!

Sorry about that I am not familiar with ASP application, but I do some research maybe can help you:
Session Context Information
Phase out CONTEXT_INFO() in SQL Server 2016 with SESSION_CONTEXT()


Best regards,
Carrin


If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.