ASP.Net – Using SQL Server to store SessionState - Error

You have done everything that needs to be done (installed the tables, stored procs, etc in to the Database and updated the web.config file) and yet you get a message that ASP.Net Session State is not installed:

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. "

Turns out the most likely cause could be that you do not have the correct permissions set on your database. You need to provide the user who is accessing the database containing the ASP.Net Session State objects execute permission, so that it can run the stored-procs to access and manipulate the state information stored in the tables.

The easiest way to do this, is to right click on your database object in Sql Server Management Studio (SSMS), go to permissions and grant execute to the user that will be used to connect to the database.

Note:

When creating the ASP.Net 2.0 session objects do not run “InstallSqlState.sql” directly. I found this to be unreliable and the file itself tells you not to do it that way. Also do not run the “aspnet_regsql.exe” via its GUI – I found that to not work always to. Instead run aspnet_regsql at the command prompt (executable is found in the %windir%\Microsoft .Net\Framework\version folder).

Here is the command I used:

aspnet_regsql.exe -ssadd -sstype c -S ServerName -d customDBName –E

-ssadd: create a database to support the storage of session information in SQL Server -sstype c: create a custom database to store the session state info. (Otherwise a ASPState database will be created/reused). I prefer this – as it allows me to keep my session state databases separate for different applications. -S: server name -d: database name – required as I had specified –sstype c. (Otherwise name defaults to ASPState) -E: use windows integrated log-in. (Instead you could use –U and –P to specify the SQL Server username and password to connect to the database). The options for aspnet_regsql are case-sensitive. MSDN documentation for ASPNet_RegSql

Because I used the –sstype c option, I need to add the “allowCustomSqlDatabase” property to the sessionState node and set it to true in the web.config file. (This is because you will need to provide the actual connection string, instead of just the user id and password, which is the default format for the sqlCOnnectionString property).

<system.web> <sessionState mode="SQLServer" allowCustomSqlDatabase="true" sqlConnectionString="Data Source=ServerName;Initial Catalog=DataBaseName;Persist Security Info=True;User ID=userID;Password=pwd;"/>