Check out my new blog at

Thursday, March 27, 2008

Implementing ASP.NET Session State in Microsoft SQL Server 2005

<from an email I wrote today>

Here are some notable items, followed by my references and then finally the specifics on how to use it in a web application:

  • There is a performance cost associated with using SQL Server to manage state:  SQL Server’s price includes the cost of network travel, the cost of the state DB's store procedures that manage the state, again the cost of network travel, and then finally the cost of serialization/deserialization

  • If you plan on storing objects in state (which isn’t a horrible thing to do when using something like SQL Server for persistence machinery as long as you consider the cost above), the objects must be marked with the SerializableAttribute attribute (makes sense)

  • To improve performance, set the page directive EnableSessionState = False for static pages or pages that aren’t using session.  For those pages that read session but do not write session, set the directive = ReadOnly

  • Web applications connect to our state server using Windows auth must ensure the service accounts are added to the DB and proper roles are assigned (I give you a query to do this below)

  • The session data is locked away in the two binary columns “SessionItemShort” and “SessionItemLong” in the ASPStateTempSessions table.   I wrote a little concept program that deserializes the data without a problem. Which is useful for debugging.  I will blog on this later.


I have SQL session state machinery setup on  The following additions to the web config are required:



sqlConnectionString="data source=;trusted_connection=true"



Windows auth is used here, so run the following query against ASPState to add your application pool’s service account to the DB with the correct roles:


DECLARE @dbUser varchar(100);

DECLARE @dbUserPass varchar(100);

-- Configure Script Here!

SET @dbUser = <domain>\<user name>';

SET @dbCatalog = 'ASPState';

-- Create the new application account

EXEC sp_grantlogin @loginame=@dbUser

-- Set the default database for the new account

EXEC sp_defaultdb @loginame=@dbUser, @defdb=@dbCatalog;

-- Grant access to the Session State database to the dbUser account

EXEC sp_grantdbaccess @loginame=@dbUser;

-- Create a new Role for stored procedure execution

-- Note: This only works in SQL Server 2005

CREATE ROLE db_executor

GRANT EXECUTE TO db_executor

-- Assign read, write, and execute permissions to the dbUser account

EXEC sp_addrolemember @rolename='db_datareader', @membername=@dbUser;

EXEC sp_addrolemember @rolename='db_datawriter', @membername=@dbUser;

EXEC sp_addrolemember @rolename='db_executor', @membername=@dbUser;

Kick-off your site and viola:


To setup SQL Session State on a particular server, run aspnet_regsql.exe –ssadd –sstype p –S <Server or IP Address> –E.  The commands are case-sensitive and essentially are telling the tool to add a state server database type “p” (default database of ASPState) to <Server or IP Address> using Windows auth.


No comments :


Content on this site is provided "AS IS" with no warranties and confers no rights. Additionally, all content on this site is my own personal opinion and does not represent my employer's view in any way.