connecting to SQL Database from asp application and setting CONTEXT_INFO

Question

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 .

Answer 1

Hi @Shashi Bhosale ,

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.

Answer 2

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.

Answer 3

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.

Answer 4

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.