Sharing SQL Server session state on Web applications


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?


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?


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?


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.


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.

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.