ASP.NET Store Session In MS SQL Server Database

When websites and applications execute in a clustered environment or require heavy state management functionality, you should use database to store session state information.

A single session variable is used to uniquely identify and associate the web visitor with the state full information held in the database.

This article describes How to configure SQL Server to store ASP.NET session state information.

Configure SQL Server for ASP.NET Session State Management

• 1. Open & Execute the InstallSqlState.sql script file in the SQL Query Analyzer
  

  Open-Execute the InstallSqlState in SQL Query Analyzer

  Note: normally, the InstallSqlState.sql script file is located in system_drive\Windows\Microsoft.NET\Framework_version\version\. Ex: C:\Windows\Microsoft.NET\Framework64\v4.0.30319

• 2. In the application’s Web.config file, set mode=SQLServer and set the sqlConnectionString attribute in the sessionState element.

  + Example of the web.config file:

  <?xml version="1.0"?>   <configuration>   <system.web> <compilation debug="true" targetFramework="4.0" /> <customErrors mode="Off"/>   <sessionState mode="SQLServer" sqlConnectionString="data source=127.0.0.1;user id=sa;password=123456" cookieless="false" timeout="20" /> </system.web>   </configuration>

  <?xml version="1.0"?> <configuration> <system.web> <compilation debug="true" targetFramework="4.0" /> <customErrors mode="Off"/> <sessionState mode="SQLServer" sqlConnectionString="data source=127.0.0.1;user id=sa;password=123456" cookieless="false" timeout="20" /> </system.web> </configuration>

  Note: please ensure the database user name has permissions to perform this operation on the database.

• 3. Create a simple asp.net (C#) page for testing:

  using System; using System.Collections.Generic; using System.Linq; using System.Web; using System.Web.UI; using System.Web.UI.WebControls;   public partial class test_session_stores_in_mssql_database : System.Web.UI.Page { protected void Page_Load(object sender, EventArgs e) { Session["session_variable"] = "123456";   Response.Write("session_variable: " + Session["session_variable"].ToString()); } }

  using System; using System.Collections.Generic; using System.Linq; using System.Web; using System.Web.UI; using System.Web.UI.WebControls; public partial class test_session_stores_in_mssql_database : System.Web.UI.Page { protected void Page_Load(object sender, EventArgs e) { Session["session_variable"] = "123456"; Response.Write("session_variable: " + Session["session_variable"].ToString()); } }

• 4. View it on a browser
• 5. Verify if the ASP.NET session state is stored somewhere in SQL Server database. Try to use SQL Server Profiler to check if any stored procedures or SQL statements are called when you read/write ASP.NET session or try to run these SQL statements:

  SELECT * FROM [tempdb].dbo.ASPStateTempApplications SELECT * FROM [tempdb].dbo.ASPStateTempSessions

  SELECT * FROM [tempdb].dbo.ASPStateTempApplications SELECT * FROM [tempdb].dbo.ASPStateTempSessions

  

  Query ASPStateTempApplications and ASPStateTempSessions Tables