Configuring a SQL Server to store session state data

To configure your session state database Microsoft has created a command line tool which will create the database for you. You can find the tool in the .NET framework folder. You must use the tool which corresponds to the framework version you are using. In my case I am using .NET 4.0 64 bit.

1. Browse to this location:

C:\Windows\system32>cd  C:\Windows\Microsoft.NET\Framework64\v4.0.30319

2. Run these commands substituting ‘ServerName’ for the SQL server you wish to create the database on:

C:\Windows\Microsoft.NET\Framework64\v4.0.30319>aspnet_regsql.exe -S ServerName -E -ssadd -sstype p
Microsoft (R) ASP.NET SQL Registration Tool version 4.0.30319.33440
Administrative utility to install and uninstall ASP.NET features on a SQL server
Copyright (C) Microsoft Corporation. All rights reserved.

Start adding session state.



3. You Should now see a new database appear on the SQL Server


Once you have the database in place you can configure IIS to save the sessions in the database.

The Development team will need to update the web.config file to use the SQL server:

Using default security:
<sessionstate mode="SQLServer" timeout="20" allowcustomsqldatabase="true" 
sqlconnectionstring="Data Source=Server;User ID=UserID;Password=Password;" 

Using Windows authentication:
<sessionstate mode="SQLServer" timeout="20" allowcustomsqldatabase="true" 
sqlconnectionstring="Data Source=Server;Integrated-Security=SSPI;"