In distributed system architectures, managing user sessions across multiple servers or distinct applications is a primary challenge. While the default InProc (In-Process) mode is efficient, it limits session persistence to a single application pool on a single server. To achieve session sharing across different web applications or to support load-balanced environments, moving session data to a centralized store like SQL Server is a robust solution.

Mechanism of Session and Cookie Synchronization

ASP.NET tracks user sessions using a unique SessionID. By default, this ID is stored in a non-persistent cookie on the client side. When a browser makes a request, it includes this cookie in the HTTP header. The server receives the SessionID, retrieves the corresponding data from the session provider, and populates the Session object.

When migrating to a shared SQL Server model, the SessionID remains the key, but the data storage moves from the web server's memory to a specialized database (usually named ASPState). This allows multiple different applications (e.g., a legacy WebForms site and a modern MVC app) to access the same data, provided they share the same SessionID and database configuration.

Database Enviroment Setup

To use SQL Server as a session provider, the database schema must first be initialized. This is typically done using the aspnet_regsql.exe utility found in the .NET Framework directory.

1. Initializing the Session Database

Execute the following command in a developer command prompt to create the necessary tables and stored proceduers:

aspnet_regsql.exe -S <YourServerName> -E -ssadd -sstype p

• -S: Specifies the SQL Server instance.
• -E: Uses Windows Authentication.
• -ssadd: Adds support for SQL Server session state.
• -sstype p: Persists session data (persisted session state).

2. Modifying Logic for Cross-Application Access

By default, ASP.NET isolates sessions based on the application path. To allow different applications to share the same session, you must modify the TempGetAppID stored procedure within the ASPState database. By commenting out the filtering logic that checks for the AppName, you allow different application identities to map to the same session data.

Application Configuration

Both applications intended to share the session must be configured in their respective web.config files to point to the SQL Server instance.

<system.web>
 <sessionState 
   mode="SQLServer" 
   allowCustomSqlDatabase="true" 
   sqlConnectionString="Data Source=YourServerName;Initial Catalog=ASPState;Integrated Security=True;" 
   timeout="30" />
</system.web>

Code Implementation Example

In this scenario, we have a WebForms application acting as the session producer and an MVC application acting as the consumer.

Application A (WebForms) - Writing Session Data

This snippet demonstrates setting a session value during the page load event.

using System;
using System.Web;

namespace WebFormsProvider
{
   public partial class SessionSetter : System.Web.UI.Page
   {
       protected void Page_Load(object sender, EventArgs e)
       {
           if (!IsPostBack)
           {
               // Assigning data to the shared session
               Session["GlobalUserToken"] = "USR_998877";
               
               // Optional: Setting a tracking cookie
               HttpCookie authSync = new HttpCookie("SyncStatus", "Active");
               Response.Cookies.Add(authSync);
           }
       }
   }
}

Application B (ASP.NET MVC) - Reading Session Data

This controller action retrieves the value set by the WebForms application.

using System.Web.Mvc;

namespace MvcConsumer.Controllers
{
   public class DashboardController : Controller
   {
       public ActionResult Profile()
       {
           // Retrieving the value from the SQL-backed session
           if (Session["GlobalUserToken"] != null)
           {
               ViewBag.Identity = Session["GlobalUserToken"].ToString();
           }
           else
           {
               ViewBag.Identity = "Anonymous";
           }
           
           return View();
       }
   }
}

Key Considerations

Using SQLServer mode requires that all objects stored in Session must be serializable. Unlike InProc mode, where objects stay in memory, SQL-based sessions convert objects into a byte stream for database storage. Any custom class must be decorated with the [Serializable] attribute to prevent runtime errors during the persistence process.