Sharing SQL Server Session State across Web Applications

Question

I'm setting up a very basic demo of SQL Server Session State, but am having some trouble getting it working. I'm trying to test this out locally running Windows 7 with IIS 7.5 and SQL Server 2008 R2.

Ultimately, I need a way to track the number of users logged into a system that is load-balanced between a few different web servers. So I need to update a session variable (stored in SQL) every time a user logs in or out. So the session ID could always be different. Is this possible?

Here's what I've done so far:

1. Created two new sites in IIS (Site1 and Site2)
2. Created two new Web Application projects in VS 2010 (Site1 and Site2)
3. In both sites, on the Default.aspx page, I created a button that when clicked will save either "Site1" or "Site2" (depending on the site) into a session variable called "LastSiteUsed". There's another button, that when clicked, will read the value from the "LastSiteUsed" session variable and display it in a label.
4. In SQL, I created a new Database called dbSessionTest. Then I followed the instructions on MSDN to install the Session State Database using the aspnet_regsql tool.
5. In both of my Web Application's Web.config file, I've added the following under <System.Web>:

<sessionState mode="SQLServer" sqlConnectionString="server=.\sqlexpress;database=dbSessionTest;uid=myUsername;pwd=myPassword" cookieless="false" timeout="20" allowCustomSqlDatabase="true"/>

Both sites function normally, but they don't seem to be sharing the session. For example, when I click my button to save my session variable from Site1, I'd expect to be able to read that value from Site2, but it doesn't work. Site2 will only return "Site2" and Site1 will only return "Site1".

Does anyone have any ideas on what I could be doing wrong? Am I wrong in thinking that I should be able to read a value set by Site1 from Site2?

UPDATE:

I can see that session data is getting stored in the ASPStateTempSessions table in SQL Management Studio, but each site can still only see the value which it wrote. Both sites are setting the session variable like this:

Session("LastSiteUsed") = "Site2"

And both sites are retrieving the value like:

lblValue.Text = "Value from Session: " & Session("LastSiteUsed").ToString

Do I need to do this differently for accessing session variables stored in SQL Server?

UPDATE 2:

I've tried using the default database, ASPState, which is created by running this command:

aspnet_regsql.exe -S MyServerName -E -ssadd -sstype p

Then simplified each of my Web.config files like:

<sessionState mode="SQLServer" sqlConnectionString="Data Source=.\sqlexpress;User ID=myUsername;Password=myPassword" cookieless="false" timeout="20"/>

But again, no luck. I want to be able to set a session variable from Site1 and then read the value from Site2, but it's not working. Again, I'm able to see entries showing up in the ASPStateTempSessions table so I know they're getting entered correctly. One thing I noticed is they have different session IDs?

Is there something I need to be doing differently to ensure the same session ID is used between my sites when setting/reading the same session variable?

UPDATE 3:

I've followed the instructions from this article which modifies an SP and adds a column for grouping to the ASPStateTempApplications table. This kind of worked, but only if both of my sites were open in the same browser (using tabs). I need to be able to open Site1 in IE, save a value to my session variable, close the browser, open Site2 in Chrome, and read the value. From everything I've read with SQL Server Session State... this should be possible.

UPDATE 4 - SOLUTION:

I followed the answer on this article provided by @SilverNinja. I re-created the ASPState database via the command prompt (to undo my SP changes in Update #3) and then modified the TempGetAppID SP as per the answer from the link. I've also updated both of my Web.config files to match the answer from that article as well:

<sessionState mode="SQLServer"
              sqlConnectionString="Data Source=.\sqlexpress;User ID=myUsername;Password=myPassword;Application Name=CacheTest"/>

I've also included identical Machine Keys in both Web.config files:

<machineKey validationKey="59C42C5AB0988049AB0555E3F2128061AE9B75E3A522F25B34A21A46B51F188A5C0C1C74F918DFB44C33406B6E874A54167DFA06AC3BE1C3FEE8506E710015DB" decryptionKey="3C98C7E33D6BC8A8C4B7D966F42F2818D33AAB84A81C594AF8F4A533ADB23B97" validation="SHA1" decryption="AES" />

Now I am able to (using IE) open up both of my sites, set a value from Site1, and read it from Site2 (and vice versa). When I check the table, only one SessionID exists (both sites are using the same session correctly). I was wrong in my thinking before that opening a new browser (for example, with Chrome), would use the same session - a new browser will start it's own session. On a load-balanced scenario though, this shouldn't cause any issues.

Answer 1

The problem you have is sharing session state across ASP.NET applications. This is not supported out-of-the-box with SQL Server session provider. See this SO post regarding what changes to make to SQL Server session provider to support cross-application sessions.

I would not use session for managing this shared state (i.e. LastSiteUsed). This information should be persisted with the user profile store (Membership, Profile provider, custom DB, etc.). Since session can expire - using a shared session across applications isn't a reliable mechanism to track persistent user-specific state. If persistence isn't critical than utilize the AppFabric Cache for an in-memory cross-application shared state.

Answer 2

For anyone who wants or needs to solve this problem without modifying the database stored procedures, consider this approach (not for the faint-hearted).

The basic idea is that there is a static instance of SqlSessionStateStore.SqlPartitionInfo stored in System.Web.SessionState.SqlSessionStateStore.s_singlePartitionInfo that I initialise by calling InitSqlInfo. Once this instance is initialised, I set the value of the internal _appSuffix field. The instance needs to be initialised before setting the _appSuffix field, otherwise my custom value will be overwritten during initialisation. I calculate the hash code from the application name provided using the same hash function as in the ASP.NET state database.

Note that this is about as nasty as it gets, and is completely dependent on internal implementation details that may change at any point in time. However, I don't know of any practical alternative. Use at your own discretion and risk.

Now that I have given my disclaimer, I would be surprised if this implementation in the .NET BCL did change since this approach to authentication is being replaced and I don't see any reason for Microsoft to be tinkering in there.

/*
 * This code is a workaround for the fact that session state in ASP.NET SqlServer mode is isolated by application. The AppDomain's appId is used to
 * segregate applications, and there is no official way exposed to modify this behaviour.
 * 
 * Some workarounds tackle the problem by modifying the ASP.NET state database. This workaround approaches the problem from the application code
 * and will be appropriate for those who do not want to alter the database. We are using it during a migration process from old to new technology stacks, 
 * where we want the transition between the two sites to be seamless.
 * 
 * As always, when relying on implementation details, the reflection based approach used here may break in future / past versions of the .NET framework.
 * Test thoroughly.
 * 
 * Usage: add this to your Global.asax:
 *       protected void Application_BeginRequest()
 *       {
 *           SessionStateCrossApplicationHacker.SetSessionStateApplicationName("an application");
 *       }
 */

using System;
using System.Data.SqlClient;
using System.Globalization;
using System.Reflection;
using System.Web.SessionState;

public static class SessionStateCrossApplicationHacker
{
    static string _appName;
    static readonly object _appNameLock = new object();

    public static void SetSessionStateApplicationName(string appName)
    {
        if (_appName != appName)
        {
            lock (_appNameLock)
            {
                if (_appName != appName)
                {
                    SetSessionStateApplicationNameOnceOnly(appName);

                    _appName = appName;
                }
            }
        }
    }

    static void SetSessionStateApplicationNameOnceOnly(string appName)
    {
        //get the static instance of SqlSessionStateStore.SqlPartitionInfo from System.Web.SessionState.SqlSessionStateStore.s_singlePartitionInfo
        var sqlSessionStateStoreType = typeof (SessionStateMode).Assembly.GetType("System.Web.SessionState.SqlSessionStateStore");
        var sqlSessionStatePartitionInfoInstance = GetStaticFieldValue(sqlSessionStateStoreType, "s_singlePartitionInfo");
        if (sqlSessionStatePartitionInfoInstance == null)
            throw new InvalidOperationException("You'll need to call this method later in the pipeline - the session state mechanism (SessionStateModule, which is an IHttpModule) has not been initialised yet. Try calling this method in Global.asax's Application_BeginRequest. Also make sure that you do not specify a partitionResolverType in your web.config.");

        //ensure that the session has not been used prior to this with an incorrect app ID
        var isStaticSqlPartitionInfoInitialised = GetFieldValue<bool>(sqlSessionStatePartitionInfoInstance, "_sqlInfoInited");
        if (isStaticSqlPartitionInfoInitialised)
            throw new InvalidOperationException("You'll need to call this method earlier in the pipeline - before any sessions have been loaded.");

        //force initialisation of the static SqlSessionStateStore.SqlPartitionInfo instance - otherwise this will happen later and overwrite our change
        var connectionString = GetFieldValue<string>(sqlSessionStatePartitionInfoInstance, "_sqlConnectionString");
        using (var connection = new SqlConnection(connectionString))
        {
            connection.Open();
            CallInstanceMethod(sqlSessionStatePartitionInfoInstance, "InitSqlInfo", connection);
        }

        //calculate and set the application hash code
        string applicationNameHashCode = GetHashCode(appName).ToString("x8", CultureInfo.InvariantCulture);
        GetField(sqlSessionStatePartitionInfoInstance, "_appSuffix").SetValue(sqlSessionStatePartitionInfoInstance, applicationNameHashCode);
    }

    static int GetHashCode(string appName)
    {
        string s = appName.ToLower();
        int hash = 5381;
        int len = s.Length;

        for (int i = 0; i < len; i++)
        {
            int c = Convert.ToInt32(s[i]);
            hash = ((hash << 5) + hash) ^ c;
        }

        return hash;
    }

    static void CallInstanceMethod(object instance, string methodName, params object[] parameters)
    {
        var methodInfo = instance.GetType().GetMethod(methodName, BindingFlags.NonPublic | BindingFlags.Instance);
        methodInfo.Invoke(instance, parameters);
    }

    static object GetStaticFieldValue(Type typeWithStaticField, string staticFieldName)
    {
        return typeWithStaticField.GetField(staticFieldName, BindingFlags.NonPublic | BindingFlags.Static).GetValue(null);
    }

    static FieldInfo GetField(object instance, string name)
    {
        return instance.GetType().GetField(name, BindingFlags.NonPublic | BindingFlags.Instance);
    }

    static T GetFieldValue<T>(object instance, string name)
    {
        return (T)GetField(instance, name).GetValue(instance);
    }
}