Problems with SQL based ASP.NET state management

If you plan to use SQL based ASP.NET 2.0 state management with Windows Server 2008 and SQL Server 2008 R2, you may encounter following problem: the ASP.NET application won’t start. The error message in Event Log sound like:

The SELECT permission was denied on the object 'ASPStateTempApplications', database 'tempdb', schema 'dbo'.

The INSERT permission was denied on the object 'ASPStateTempApplications', database 'tempdb', schema 'dbo'.

   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)

   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)

   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)

   at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)

   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)

   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)

   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)

   at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)

   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()

   at System.Web.SessionState.SqlSessionStateStore.SqlPartitionInfo.InitSqlInfo(SqlConnection sqlConnection)

   at System.Web.SessionState.SqlSessionStateStore.SqlStateConnection..ctor(SqlPartitionInfo sqlPartitionInfo)

   at System.Web.SessionState.SqlSessionStateStore.GetConnection(String id, Boolean& usePooling)

   at System.Web.SessionState.SqlSessionStateStore.DoGet(HttpContext context, String id, Boolean getExclusive, Boolean& locked, TimeSpan& lockAge, Object& lockId, SessionStateActions& actionFlags)

   at System.Web.SessionState.SqlSessionStateStore.GetItemExclusive(HttpContext context, String id, Boolean& locked, TimeSpan& lockAge, Object& lockId, SessionStateActions& actionFlags)

   at System.Web.SessionState.SessionStateModule.GetSessionStateItem()

   at System.Web.SessionState.SessionStateModule.BeginAcquireState(Object source, EventArgs e, AsyncCallback cb, Object extraData)

   at System.Web.HttpApplication.AsyncEventExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute()

   at System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously)

The reason is:

- you configured usage of temporary database for ASP.NET state data management (for example, specifying 
–sstype t
or omitting this option while launching aspnet_regsql command for ASP.NET SQL state management)

-  the user account configured for Application Pool of your ASP.NET Web Application has no permission on ASPState database and (!) no SELECT/INSERT/DELETE/UPDATE permissions on 'ASPStateTempApplications' and 'ASPStateTempSessions' tables of tempdb database.

The very first attempt is to provide your Application Pool user with required permissions – and it works! It really works – until you restart the machine. At this moment the temporary database tempdb is re-created and all the permissions you configured for the Application Pool user are gone.

This behavior is known and described in technet article

http://technet.microsoft.com/en-us/library/dd392259(WS.10).aspx

Unfortunately, none of offered in this article solutions did work for me.

My solution looks like follows:

• remove ASP.NET SQL state management: for example, use command
  aspnet_regsql –ssremove –E  -S .
  confirm command with “y” if asked
  
  Note:
  -E is essential for integrated security connection usage
  -S informs what SQL server and SQL instance to use, “.” (dot) specifies default local instance of SQL Server
  
• add ASP.NET SQL state management: for example, use command
  aspnet_regsql –ssadd –sstype p –E  -S .
  confirm command with “y” if asked
  
• open SSMS and ensure the ASPState database is created
  
  
• add Application Pool user to User logins of ASPState database, associate him with db_datareader and db_datawriter roles
• open “Properties” dialog for ASPState database, select “Permissions” Tab and provide Application Pool user with SELECT/INSERT/DELETE/UPDATE permissions for the ASPState database.
  

Start your Web Application: if all the application state management settings are configured properly (check the connection string), everything should work immediately. Even after machine gets restarted.

Enjoy!