In this article describe the concept of the SQLServer session state mode in Here we understand the SQLServer session state mode and learn from the example.

ASP.NET supports various Session State Modes depending on various storage options for session data. The following are the available Session State Modes in ASP.NET:

· InProc

· StateServer

· SQLServer

· Custom

Let’s we talk about SQLServer session or SQLServer mode session in mode stores session state in a SQL Server database. Using this mode ensures that session state is preserved if the Web application is restarted and also makes session state available to multiple Web servers in a Web farm.

Objects stored in session state must be serializable if the mode is SQL Server. For information on serializable objects, see the SerializableAttribute class.

To use SQLServer mode, you must first be sure the ASP.NET session state database is installed on SQL Server. You can install the ASP.NET session state database using the Aspnet_regsql.exe tool, as described later in this topic.

Here we give the example of SQLServer session state mode.

Create a web form in as User.aspx


    User Name: <asp:TextBox ID="txt_name" runat="server"></asp:TextBox>

    <br />

    <br />

        <asp:Button ID="btn_submit" runat="server" Text="Submit"OnClick="btn_submit_Click" />



Code on submit button in User.aspx.cs

protected void btn_submit_Click(object sender, EventArgs e)


        Session["Username"] = txt_name.Text;




Add another web form as UserDetail.aspx and code into UserDetail.aspx.cs


if (Session["Username"] != null && Session["Username"] != "")


            string name = Session["Username"].ToString();

            Response.Write(String.Format("User name is: {0}", name));




            Response.Write(String.Format("There are no user!"));



Here we code the web.config file to set session mode:



    <sessionState mode="SQLServer" timeout="30" sqlConnectionString="Integrated Security=SSPI; Data Source=MINDSTICK-PC\ MINDSTICK;" />




Run the application and run first User.aspxSQLServer session state in

And click on submit button then we get the error cannot open the database “ASPState” requested by the login. The login failed. Login failed for user ‘MINDSTICK-PC\ MINDSTICK’.

Here we have not installed InstallSqlState.sql.

Open visual studio command prompt and we will be running a utility called aspnet_regsql. It's a generic utility which is used for SQL related things in ASP. Net.

SQLServer session state in

The command type and the description as given below to understand:

 -S <server>: Species the IP address or the name of SQL server in which you want to store the session state

-U:  Specifies the user ID to be used when connecting to the SQL Server

-P:  Specifies the password to be used when connecting to the SQL Server

-E: Indicates that you want to use integrated security when connecting to the SQL Server

-ssadd: Adds support for the SQLServer mode session state

-ssremove: Removes support for the SQLServer mode session state

-sstype: Type of session state support. This option can be:

t: for temporary storage (Stores session data in the SQL Server tempdb database)

p: for persistent storage (Stores session data in the ASPState database)

c:  for custom storage (Stores session data in a custom database)

-d:  <database>

The name of the custom database to use if -sstype switch is "c"

Now session state will be added in your database.

SQLServer session state in

Here the ‘ASPState’ database added in the database. Remember is in the error the

database was ASPState.

There are no tables in database and here temprory as temp Table that stores the


Here are the system stored procedure that help to create temp table to store state.

Run again the application as User.aspx form:

SQLServer session state in

Click on submit button to go to UserDetail.aspx

SQLServer session state in

Stop the development server and run directly UserDetail.aspx from visual studio.

SQLServer session state in

There are no error. So successfully completed this example.

Advantages of SQLServer Session state
·         SQL Server is most reliable of all modes. Session data are intact if ASP.NET
restarts, but also if SQL Server restarts.
·         SQL Server is also most scalable option.
·         SQL Server is often available in shared hosting scenario.
Disadvantages of SQLServer Session state
·         It requires most configuration.
·         If you already don't use SQL Server on database driven website, it will require
additional cost to obtain license.
·         SQL Server is slowest of all options.
  Modified On Mar-24-2018 02:48:32 AM
  1. Hi, i am using the same sql server mode to store session. but i am getting issue to get the value of session variables, i have made the class serialized but the session variable values are always getting as null

Leave Comment