Configuring ASP session state on SQL server

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:
   XML

   <sessionstate mode="SQLServer" timeout="20" allowcustomsqldatabase="true" 
   sqlconnectionstring="Data Source=Server;User ID=UserID;Password=Password;" 
   cookieless="false">

2. Using default windows security:
   XML

   <sessionstate mode="SQLServer" timeout="20" allowcustomsqldatabase="true" 
   sqlconnectionstring="Data Source=Server;Integrated-Security=SSPI;" 
   cookieless="false">

3. Custom database name:
   XML

   <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.

CodeProject