Can be several reasons why to use SQL Server for session state. In my case hosting service provider does not allow me to control application pool so user losing his session state from time to time and it’s very big issue. There are a lot of articles in Internet how to setup database to store ASP.NET session state, but no one explains how to setup it on a remote SQL Server database where you have no ‘sa’ rights. The problem with a standard InstallSqlState.sql script is that it tries to create SQL server job for cleaning up database from expired sessions.
I’ve modified this script for my needs. Basically I remove part of script which creates SQL server job and rename standard database name to mine. Download the script, replace all ‘libou_kitsula’ (without quotes) with your database name and run it. Script will create two tables and a number of stored procedures that ASP .NET uses to handle and manage session states. Keep in mind that you should manually execute from time to time DeleteExpiredSessions stored procedure to cleanup DB from expired and unused sessions.
After all to configure you web application to use SQL Server for session state in web.config:
<system.web> ... <sessionstate allowcustomsqldatabase="true" cookieless="UseCookies" mode="SQLServer" sqlconnectionstring="data source=SQLServerAddress;initial catalog=DatabaseName;user id=UserName;password=Password" timeout="43200"/> ... </system.web>
Reply
