HOW TO: Configure SQL Server to Store ASP.NET Session State

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

Requirements

The following list outlines the recommended hardware, software, network infrastructure, and service packs that you need:

• Microsoft Windows 2000 Professional, Microsoft Windows 2000 Server, Microsoft Windows 2000 Advanced Server, or Microsoft Windows XP
• Microsoft .NET Framework
• Microsoft Internet Information Services (IIS)
• Microsoft SQL Server

Configure SQL Server for ASP.NET SQL Server Session State

The following steps describe how to run the InstallSqlState.sql and the UninstallSqlState.sql script files to configure SQL Server mode session state management.

1. In SQL Query Analyzer, on the File menu, click Open.
2. In the Open Query File dialog box, browse to the InstallSqlState.sql script file, and then click Open. By default, InstallSqlState.sql is located in one of the following folders:
   system drive\WINNT\Microsoft.NET\Framework\version\
   
   system drive\Windows\Microsoft.NET\Framework\version\
3. After InstallSqlState.sql opens in SQL Query Analyzer, click Execute on the Query menu to run the script.
4. 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:
   1. On the Windows Start menu, click Run, type cmd, and then click OK to open a command prompt.
   2. At the command prompt, type net stop w3svc. You receive confirmation that the w3svc process is stopped.
5. In SQL Query Analyzer, on the File menu, click Open.
6. 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:
   system drive\WINNT\Microsoft.NET\Framework\version\
   
   system drive\Windows\Microsoft.NET\Framework\version\
7. After UninstallSqlState.sql opens in SQL Query Analyzer, click Execute on 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 w3svc at a command prompt.

Modify the Web.config File of Your Application

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:

1. 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 id=<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:

   <sessionState 
               mode="SQLServer"
               sqlConnectionString="data source=127.0.0.1;user id=<username>;password=<strongpassword>"
               cookieless="false" 
               timeout="20" 
       />
   						

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

Troubleshooting

• If you do not stop the w3svc process before you run the UninstallSqlState.sql script file, you receive the following error message:
  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.For additional information about how to run alternative scripts to configure persistent SQL Server session state management so that the session data is not lost when you restart the server, click the article number below to view the article in the Microsoft Knowledge Base:
  311209  (http://support.microsoft.com/kb/311209/EN-US/ ) HOW TO: Configure ASP.NET for Persistent SQL Server Session State Management

For additional information, click the article numbers below to view the articles in the Microsoft Knowledge Base: For more information about ASP.NET session state management, refer to the following Microsoft Web sites: