ASP.Net Session Management With SQL Server 2000

In this tutorial I will be walking you through how to set up maintaining ASP.Net session state using Microsoft SQL Server 2000. Before getting into the actual set up of SQL Server, lets take a bit to compare session state in classic ASP with that of ASP.Net. A session is the period of time a user interacts with your Web application. With classic ASP if you wanted to maintain session state, that is, store the session data for each unique user while they interact with your Web application, you could use the intrinsic Session State object.

To simplify things, session state is a dictionary, hashtable, e.g. in memory, or key-value pairs, that can be used to read from and write to for the length of a users session. An example is with a shopping cart, lets say when the user selects "Add to cart" you write the product to the session object until the user selects "Checkout", that would look like

Session("Products") = "19' LCD Monitor,Seagate 300GB SATA HDD; "

And you just keep adding the users products until they decide to checkout and leave. That is how you can write data to the users session state, you can also read data from the same session state. In this example when the user selects "Checkout" we will have to get the items from their session so we can add it to the database, or however you're doing your storage. One way of doing that, given we have a delimited string, is like so

' Get the users products
Dim products
products = Session("Products")

We then have a delimited string that we can split on the comma's and manipulate it how we wish. In ASP session state is maintained by assigning each user a unique id that is then stored in a HTTP cookie. Each time the user makes a request, this cookie is sent along with the request and the server uses that value to identify each user and what they have in their session. ASP developers used this method for many years, and successfully I might add, but it does have it's limitations.

Session state is process dependent, meaning it has to run in the same process that the ASP.DLL runs in, so if the server restarts or crashes, the users session, and it's data, is lost forever. Session state is also pretty much useless in a server farm environment, as each server assigns a different unique ID to each visitor, making it impossible to them to track and maintain each user. Lastly session state is rendered useless is the user has cookies turned off. As I stated before session is maintained via HTTP cookies, so no cookie means no session.

Then along comes ASP.Net, and the world of session state was dramatically changed. ASP.Net offers 3 distinct ways of maintaining state. Session state in ASP.Net is configured in the Web applications web.config file. Here is an example of a Web applications web.config file:

<configuration>
  <sessionstate 
	  mode="inproc"
	  cookieless="false" 
	  timeout="20" 
	  sqlconnectionstring="data source=127.0.0.1;user id=youruserid;password=yourpassword"
	  server="127.0.0.1" 
	  port="42424" 
  />
</configuration>

As stated above, ASP.Net offers 3 different ways of maintaining state:

InProc Session State (in-process)

In-process session state is maintained the same way ASP session state is maintained, through HTTP cookies, so if the process is recycled, i.e; server crashes, server reboot, etc., all the data is lost. In my opinion this isn't really a viable option for Web applications today.

SQL Server Session State

In this mode Microsoft SQL Server is employed to maintain session state. Though the overhead for using SQL Server mode is higher than InProc, you can restart the web server all you want and the users session data will persist.

An example of the web.config for a Web application employing this method would look like

<configuration>
  <sessionstate 
	  mode="sqlserver"
	  cookieless="false" 
	  timeout="20" 
	  sqlconnectionstring="data source=YourSQLServer;
						   user id=YourUserId;
						   password=YourPassword"
	  server="127.0.0.1" 
	  port="42424" 
  />
</configuration>

You simply tell your application which SQL server to use, the user id to connect with and the password, your application will take care of the rest.

State Server

This method employs a dedicated server for maintaining session state. As with the SQL Server method the overhead is higher than InProc, but the session state data is far more stable and safe from being lost if the process were to cycle before the user was completed.

Set up SQL Server Mode

In this tutorial we are going to focus on setting up the SQL Server method for maintaining session state. Here are the steps for setting this method up on your SQL server:

Connect to your SQL server using Query Analyzer
Open the SQL script InstallSqlState.sql script that is normally located in C:\Windows\Microsoft.Net\Framework\[YourVersion]. Replace C: with the drive letter you have yours installed on. This script will create the ASPState database, tables, and stored procedures needed for this method of maintaining session state.
If using a trusted connection for connecting to your SQL server you must change ownership of the ASPNet database to the 'sa' account. To do this, in Query Analyzer run this
```
use ASPNet
exec sp_changedbowner 'sa', 'true'
```
The sp_changedbowner stored procedure is a system stored procedure that is used to, you guessed it, change the owner of a database.
If you decide to use SQL Authentication instead of Windows Authentication you need to create a user account for the process. This account must have, at the very minimum, execute rights for all the stored procedures in the ASPNet database.

Now that we have your SQL Server configured for this mode of state management, we now need to configure your Web application to look at your newly created ASPState database. Fortunately doing this is far fewer steps than getting the database ready. These steps are:

Set the mode of the sessionState element in your web.config to SQLServer.

Set the sqlConnectionString attribute of the same element to the connection string for your SQL server. The web.config should now look like:

<sessionState
  mode="SQLServer"
  sqlConnectionString="data source=YourServer;user id=YourUsername;password=YourPassword"
  cookieless="false" 
  timeout="20" />

Now you have your SQL Server ready for this, and your Web application ready, so you would think you're done now right? Not necessarily; Lets say you're running SQL Server 2000 with Service pack 3, if this is the case the first time you run your Web application against your ASPState database you're going to get errors such as:

SELECT permission denied on object 'ASPStateTempApplications', database 'tempdb', owner 'dbo'.

Why is this you might ask, well Im glad you asked because I have the answer. Service Pack 3 for SQL Server 2000 adds a new secutiry feature, which as you can imagine from the above error message, is disabled by default. This new security feature is Cross DB Ownership Chaining. How nice of Microsoft to inform you of this before you went through all this isn't it.

don't be discouraged, fortunately this can be enabled by running the sp_configure and sp_dboption system stored procedures. First you run the sp_configure stored procedure, like so

use master
go
EXEC sp_configure 'Cross DB Ownership Chaining', '0';
RECONFIGURE
GO

Then reboot your SQL Server. Once your server is rebooted you will then need to run the sp_dboption stored procedure, like this:

use master
go
EXEC sp_dboption 'ASPState', 'db chaining', 'true'
go

Once you have completed this final step your SQL Server is now ready to handle session state management. There you have it, setting up session management using SQL Server 2000. In the next tutorial we will look at setting up a state server for session management, until then I hope you found this tutorial informative and useful. Thanks for reading and happy coding!

This post has been edited by PsychoCoder: 13 March 2008 - 05:05 PM