Skip to main content

Configure SQL Server to store ASP.NET session state

Configure SQL Server to store ASP.NET session state

 

Summary

This step-by-step article demonstrates how to configure Microsoft SQL Server for ASP.NET SQL Server mode session state management.

Steps

    1. Open an elevated command prompt and navigate to the following path: C:\Windows\Microsoft.NET\Framework\<ASP.NET version>  (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

 

      1. Using default ASPState database and windows security

 

  • aspnet_regsql -S serverName -E -ssadd -sstype p

 

      1. Using custom database and SQL security

 

  • aspnet_regsql -d TableName -S serverName -U UserName -P Password -ssadd -sstype c

 

          1. 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.
          2. p - Stores session data in the ASPState database instead of in the tempdb database.
          3. 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.
    1. Modify the Web.config file of your application:
      1. To implement ASP.NET SQL Server mode session state management, you must modify the <sessionState> element of your application's Web.config file as follows:
        • Set the mode attribute of the <sessionState> element to SQLServer to indicate that session state is stored in SQL Server.
      2. Set the sqlConnectionString attribute to specify the connection string for SQL Server. For example: sqlConnectionString="data source=MySQLServer;user d=<username>;password=<strongpassword>"

 

  • Note: The user, <user name>, must have permissions to perform this operation on the database. The modified <sessionState> element should appear as follows:

 

 

 

  • Note: Ensure that you use the correct case when you specify the <sessionState> element and the associated attribute values. This code is case sensitive.

 

        • Using default SQL security:

 

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

 

        • Using default windows security:

 

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

 

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

 

Uninstall ASP.NET SQL Server Session State

  1. Before you run the UninstallSqlState.sql script file to uninstall SQL Server mode session state management configuration, you must stop the w3svc process. To do this, follow these steps:
  2. On the Windows Start menu, click Run, type cmd, and then clickOK to open a command prompt.
  3. At the command prompt, type net stop w3svc. You receive confirmation that the w3svc process is stopped.
  4. In SQL Query Analyzer, on the File menu, clickOpen.
  5. In the Open Query File dialog box, browse to the UninstallSqlState.sql script file, and then click Open. By default, UninstallSqlState.sql is located in one of the following folders:
  6. system drive\Windows\Microsoft.NET\Framework\<ASP.NET version>
  7. After UninstallSqlState.sql opens in SQL Query Analyzer, click Executeon the Query menu to run the script.
  8. After you uninstall SQL Server mode session state management configuration, you must restart the w3svc service. To restart the w3svc process, type net start w3svcat a command prompt.

 

Troubleshooting

  • If you do not stop the w3svc process before you run the UninstallSqlState.sql script file, you receive the following error message:

o   Cannot drop the database 'ASPState' because it is currently in use

  • If entries in the ASPStateTempSessions table are not removed after the related sessions expire, make sure that the SQL Server agent is running. You can implement this functionality through stored procedures that are scheduled through jobs in SQL Server. The SQL Server agent manages these jobs.
  • When you use the default InstallSqlState.sql and UninstallSqlState.sql script files to configure ASP.NET SQL Server mode session state management, note that these files add the ASPStateTempSessions and the ASPStateTempApplications tables to the tempdb database in SQL Server by default. Furthermore, if you restart SQL Server, you lose the session state data that was stored in the ASPStateTempSessions and the ASPStateTempApplications tables.
  • 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 de-serialize 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 programmatically.

 

Related articles