asp.net session state mode "SQLServer"

asp.net sql-server session session-state session-timeout
jackerj picture jackerj · Mar 13, 2013 · Viewed 37.1k times · Source

"My website is LIVE. And this problem is related to configure session timeout on LIVE server and not in localhost."

I have a problem with session expiring too soon. link in 2-5 minutes only. I tried lot of things and at last decided to store the session in "SQL Server" mode

in my web.config file i have following coding:

<sessionState mode="SQLServer" cookieless="false" timeout="45"
sqlConnectionString="data source=xxx.xx.xx.xxx;uid=xxxxxxx;pwd=xxxxxxxx"/>

and i have all the tables required in ASPState table on server. You can see it from the image below.

enter image description here

But i when i run my application, its throws the below error:

"Unable to use SQL Server because either ASP.NET version 2.0 Session State is not installed on the SQL server, or ASP.NET does not have permission to run the dbo.TempGetVersion stored procedure. If the ASP.NET Session State schema has not been installed, please install ASP.NET Session State SQL Server version 2.0 or above. If the schema has been installed, please grant execute permission on the dbo.TempGetVersion stored procedure to either the ASP.NET application pool identity, or the Sql Server user specified in the sqlConnectionString attribute."

Image of the error:

enter image description here

I am not able to understand the exact problem and how i can solve it. Any help will be appreciated.

Thank You

Answer

Kenn picture Kenn · Apr 30, 2014

In order to get this to work for me I ran the command with the following options. 

C:\Windows\Microsoft.NET\Framework\v4.0.30319\aspnet_regsql.exe -S . -E -ssadd -sstype p

I believe the -ssadd option ("Adds support for SQL Server mode session state.") is what caused it to properly set up the stored procedures. See this link for to complete list of option.

Related questions

How to check if SQL Server Agent is running

I am using SQL Server 2008 r2. I want to find a way to verify if SQL Server Agent is running. I am suspicious that the Agent isn't running, but I don't know how to check.

Read More
SQLServer vs StateServer for ASP.NET Session State Performance

I'm studying for a MS certification and one of the practice tests I'm doing has a question where the point of contention is the performance between storing the session in SQL Server as opposed to StateServer. Given the app is …

Read More
Passing session data between ASP.NET web applications

I'm trying to help a friend - they have a pretty big web application (ASP.NET 4.0, Visual Basic) that has a number of subfolders that all act as quasi sub-applications (but they're just subfolders of the main application). This application …

Read More