I just thought I would write a quick blog about something that I was tackling at work today. We are in the process of migrating some web applications from ASP.NET 1.0 to ASP.NET 2.0. One of these applications uses Session State stored on SQL Server. I did a little homework and discovered that there were some differences, especially with the stored procedures. So I got this idea to create another database in SQL Server for the ASP.NET 2.0 application so I don’t mess up the ASP.NET 1.0 application during our conversion. Make sense? Good!

Step 1: Create a database in SQL Server using aspnet_regsql.exe
1. Open up a command console.
2. Go to: C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727>
3. From the command line type:
aspnet_regsql.exe -S SERVERNAME -U sa -P password -ssadd -sstype c -d ASPState20

Basically, this will add a custom session state database called “ASPState20″ to SQL Server.

Step 2: Update your web.config sessionstate under system.web
1. Open up: web.config
2. Look for the sessionState section.
3. Update it with:
<sessionstate allowCustomSqlDatabase="true" mode="SQLServer" sqlConnectionString="data source=SERVERNAME.COMPANY.COM;initial catalog=AspState20;user id=sa;password=password" cookieless="false" timeout="720"/>
4. Save and compile!

Side Notes:
1. Notice how we use something called “allowCustomSqlDatabase”?
For some reason, if you don’t set it to true, you will get this error:

Parser Error Message: The sqlConnectionString attribute or the connection string it refers to cannot contain the connection options ‘Database’, ‘Initial Catalog’ or ‘AttachDbFileName’. In order to allow this, allowCustomSqlDatabase attribute must be set to true and the application needs to be granted unrestricted SqlClientPermission. Please check with your administrator if the application does not have this permission.

2. Notice how we use the “initial catalog”?
If you don’t specify an initial catalog, ASP.NET assumes you want the default “AspState” database. If you have an AspState database and it isn’t an ASP.NET 2.0 database, you will get this error:

Exception Details:
System.Web.HttpException: Unable to use SQL Server because ASP.NET version 2.0 Session State is not installed on the SQL server. Please install ASP.NET Session State SQL Server version 2.0 or above.

Stack Trace:
[HttpException (0x80004005): Unable to use SQL Server because ASP.NET version 2.0 Session State is not installed on the SQL server. Please install ASP.NET Session State SQL Server version 2.0 or above.]
System.Web.SessionState.SqlPartitionInfo.GetServerSupportOptions(SqlConnection sqlConnection) +252
System.Web.SessionState.SqlPartitionInfo.InitSqlInfo(SqlConnection sqlConnection) +91
System.Web.SessionState.SqlStateConnection..ctor(SqlPartitionInfo sqlPartitionInfo) +426
System.Web.SessionState.SqlSessionStateStore.GetConnection(String id, Boolean& usePooling) +282
System.Web.SessionState.SqlSessionStateStore. SetAndReleaseItemExclusive(HttpContext context, String id, SessionStateStoreData item, Object lockId, Boolean newItem) +178
System.Web.SessionState.SessionStateModule.OnReleaseState(Object source, EventArgs eventArgs) +355
System.Web.SyncEventExecutionStep.System.Web.HttpApplication. IExecutionStep.Execute() +92
System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously) +64