How to implement SQL session state

1. Start Query Analyzer, connected to the server you want to use for state storage.
2. Open and execute InstallSqlState.sql script file. By default, InstallSqlState.sql is located in one of the following folders; system drive\ Windows\ Microsoft.NET\ Framework\version\
3. If you are using trusted connections to connect to your server, you must change ownership of the state database to sa after creation. In Query Analyzer run use ASPState
4. exec sp_changedbowner ’sa’,'true’
5. If you are using SQL authentication create a user and password for session state to use. At a minimum this user should have permissions to execute the stored procedures in the ASPState database. You will have to manually set these, or if you’re feeling dangerous, give the state user dbo rights to ASPState.

Configuring ASP.Net
To switch ASP.Net to use SQL you must update the element of your application’s Web.config file as follows;

• Set the mode attribute of the element to SQLServer.
• Set the sqlConnectionString attribute to specify the connection string to your SQL Server

For example

<sessionState
  mode="SQLServer"
  sqlConnectionString="data source=server;user id=uid;password=pwd"
  cookieless="false" timeout="20" />

Some time this is not enough for your application and you may get this exception
(i am telling you because i was getting same exception in my project and i have spend so much time to find solution)

“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.”

Before you can actually store a session state in SQL server, you need to configure it. This configuration is done via a command line tool called ASPNET_REGSQL.EXE.

Run this command
C:\Windows\Microsoft.NET\Framework\v2.0.50727>aspnet_regsql.exe -S <ServerName>
-U <Username> -P <Password> -ssadd -sstype p

i hope this will work for you .