ASP.NET error “Unable to use SQL Server because ASP.NET version 2.0 Session State is not installed on the SQL server”

Using SQL based state management for ASP.NET 2.0 aplications you may encounter following error:

6/12/2010 7:58:15 AM Frontend.Web.Main.Global Error 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.
at System.Web.SessionState.SqlSessionStateStore.SqlPartitionInfo.GetServerSupportOptions(SqlConnection sqlConnection)
…

What to do?

1. Ensure the ASP.NET state management database is created on the desired database server.
   Keep in mind the command to create such a database:
   
   aspnet_regsql -S <servername> -E -ssadd -sstype p
   
   The database should appear in the management studio like:
   
   
   
2. Ensure the database server and (optionally) the database – if the name differs from the default ASPState – are referenced properly in the web.config in you application:
   
   <system.web>
   <sessionState mode="SQLServer" sqlConnectionString="Integrated Security=SSPI;data source=<servername>;" timeout="30" cookieless="false">
   </sessionState>
   …
   
3. Check the account of the application pool your application is running in:
   
   
   
4. This account should be granted following permissions in the ASPState database:
   - data read
   - data write
   -execute stored procedures
   
5. Usually there’s no dedicated role for stored procedure execution, so create one using “Roles –> Database Roles” editor in management studio:
   - create a role like db_spexecute
   - in “Securables” section add all the dbo. stored procedures related to ASPState management
   - include your application pool account in the newly create role
   
   
6. If you performed all operations correctly, you don’t even need to restart IIS or the WebApp – it just works. Enjoy!