Click here to Skip to main content
11,575,925 members (56,852 online)
Click here to Skip to main content

Configuring ASP session state on SQL server

, 24 Aug 2010 CPOL 54.7K 14
Rate this:
Please Sign up or sign in to vote.
This is a brief to the point note on how to setup ASP session state to use SQL server to store session information.

Introduction

This is a brief to the point note on how to setup ASP session state to use SQL server to store session information.

There are 2 steps for setting up the ASPState database and the configuration setting in your web.config.

For some reason, you cannot run the SQL file directly. I can't remember what the issue is, but you need to use ASP_RegSQl.exe from a command prompt. The script file and the aspregsql EXE are located at C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727.

Steps

  1. Open a command prompt and locate the following path: C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727 based on your OS version and .NET version
  2. Use the following statement:
    1. Using default ASPState database and SQL security
      aspnet_regsql -S serverName -U UserName -P Password -ssadd -sstype p
    2. Using default ASPState database and windows security
      aspnet_regsql -S serverName -E -ssadd -sstype p
    3. Using custom database and SQL security
      aspnet_regsql -d TableName -S serverName -U UserName -P Password 
      -ssadd -sstype c
      • t - Stores session data in the SQL Server tempdb database. This is the default. If you store session data in the tempdb database, the session data is lost if SQL Server is restarted.
      • p - Stores session data in the ASPState database instead of in the tempdb database.
      • c - Stores session data in a custom database. If you specify the c option, you must also include the name of the custom database using the -d option.
  3. In your configuration file:
    1. Using default SQL security:
      <sessionstate mode="SQLServer" timeout="20" allowcustomsqldatabase="true" 
      sqlconnectionstring="Data Source=Server;User ID=UserID;Password=Password;" 
      cookieless="false">
    2. Using default windows security:
      <sessionstate mode="SQLServer" timeout="20" allowcustomsqldatabase="true" 
      sqlconnectionstring="Data Source=Server;Integrated-Security=SSPI;" 
      cookieless="false">
    3. Custom database name:
      <sessionstate mode="SQLServer" timeout="20" allowcustomsqldatabase="true" 
      sqlconnectionstring="Data Source=Server;Initial Catalog=tablename;
      User ID=UserID;Password=Password;" cookieless="false">

Using SQL Server to Store ASP Session Information in a Web Farm

Things to watch for when using SQL to store the session information in a web farm scenario:

  1. The machine key between the servers' needs to be the same as AspState Session information is encrypted using the machine key.
  2. The application path to your websites on all machines needs to be consistent as well.

Other Issues

Since the session information needs to be converted from memory to a more persist-able form such as text. Out of memory session stores serialize and deserialize the session, you need to ensure that all complex/custom objects that you wish to store in a session are serializable. If you come across any such object that you cannot serialize by using the serialization attribute, you can always serialize it programatically.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Ritesh Ramesh
Architect Infosolvex Solutions Inc
Australia Australia
Ritesh is an IT consultant with over ten years of experience in the IT industry varying from consultation, architecture, design, development to technical management. He has a strong background in solutions and applications architecture with a focus on Microsoft’s .Net platform. His area of expertise spans design and implementation of client/server, database and web-based systems. He has worked with C#, ASP.NET 1.1 and 2.0, ADO.NET, Web Services and SQL technology on several enterprise class projects.




Freedom is not worth having if it does not include the freedom to make mistakes.
Mahatma Gandhi

You may also be interested in...

Comments and Discussions

 
QuestionRead SessionID value in sql mode Pin
CLEVERALMEIDA16-Apr-14 3:49
memberCLEVERALMEIDA16-Apr-14 3:49 
GeneralMy vote of 1 Pin
me_pollack28-Mar-14 11:20
memberme_pollack28-Mar-14 11:20 
Questionsessionstate attributes are case-sensitive Pin
cieszak28-Oct-13 23:05
membercieszak28-Oct-13 23:05 
GeneralMy vote of 1 Pin
msdevtech2-May-13 15:15
membermsdevtech2-May-13 15:15 
GeneralStoring-Session-State-in-a-SQL-Server-Database Pin
engharie3-Aug-12 4:10
memberengharie3-Aug-12 4:10 
QuestionHave a doubt on this Pin
vardhanmm@gmail.com14-Feb-12 20:22
membervardhanmm@gmail.com14-Feb-12 20:22 
GeneralConsider naming your blog post Pin
Christian Graus23-Aug-10 22:03
mvpChristian Graus23-Aug-10 22:03 
GeneralNot enough for an article. [modified] Pin
digital man23-Aug-10 21:24
memberdigital man23-Aug-10 21:24 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web04 | 2.8.150603.1 | Last Updated 24 Aug 2010
Article Copyright 2010 by Ritesh Ramesh
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid