How to Use SQL Server Express to Store ASP.NET Session State

ASP.NET Session State SQL Server Mode provides a good way to have a persistent session, especially for server farm configuration. Here are the steps to quickly configure the environment to do so using SQL Server Express edition (because sometimes, you don’t need the full-blown version of SQL Server just to track sessions).

Steps

1. Download and install SQL Server Express.

2. Open the file, select install and follow the instructions.

3. On the Instance Configuration screen, you can change the Named instance if you want to, but for this example, it’s left with the default SQLExpress.

   

4. On the Server Configuration screen, make sure SQL Server Browser is set to Automatic so you can connect from a remote machine.

   

5. Keep moving along the screens and wait until the installation process completed. Make sure all components are successfully installed and click on Close button.

   

Create ASPState Database in SQL Server

In your DB server, open the Command Prompt (Admin) and type in the following command:

C:\Windows\Microsoft.NET\Framework\v2.0.50727\aspnet_regsql -ssadd -d [DATABASENAME] -S [SERVERNAME]\SqlExpress -sstype c -E

If you use a custom database name (instead of the default ASPState by using option -sstype p), then at the end of the process, you’ll see the following message:

To use this custom session state database in your web application, please specifiy it in the configuration file by using the 'allowCustomSqlDatabase' and 'sqlConnectionString' attributes in the <system.web>\<sessionState> section.

So you have to change your default connectionString from:

<sessionState mode="SQLServer" cookieless="false" timeout="480" sqlConnectionString="Data Source=[SERVERNAME]\SqlExpress;User Id=MySession;Password=MySessionPassword" />

To:

<sessionState mode="SQLServer" cookieless="false" timeout="480" sqlConnectionString="Data Source=[SERVERNAME]\SqlExpress;Initial Catalog=[DATABASENAME];User Id=MySession;Password=MySessionPassword" allowCustomSqlDatabase="true" />

If you can’t still see the difference, there are two additions on the sessionState element. The first one is the addition of Initial Catalog=[DATABASENAME] under sqlConnectionString and the second one is at the end of markup, allowCustomSqlDatabase=”true”.

Further Reading

Beginning ASP.NET 4.5 in C#: Chapter 6 – State Management (p. 263-266)
Professional Microsoft IIS 8: Advanced Administration (p. 501-545)
Session-State Modes
Creating the Application Services Database for SQL Server
ASP.NET SQL Server Registration Tool (Aspnet_regsql.exe)
ASP.NET Session State