Using SQL Server for ASP.NET session state

Configuration

Configuring ASP.Net

To switch ASP.Net to use SQL you must update the <sessionState> element of your application's Web.config file as follows;

  • Set the mode attribute of the <sessionState> 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" />

If you specify integrated security/trusted connections in the database connection string (ie. "trusted_connection=true", or "integrated security=sspi"), you cannot use impersonation in asp.net, as your database connection will then run the context of the impersonated user, which will not have rights to the state database. You can, of course grant connections to that user context. KB 326606 has more details.

If you are configuring session state to be stored on a cluster you must manually override the .net machine keys on each server. KB 323262 has more details.

Configuring SQL2000 SP3

So, you have created the database, you've created the user, given it rights to all the stored procedures in the ASPState database, you've up to date with patches, Windows, IIS SQL, and you connect to your web site...

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

SP3 for SQL 2000 adds a new security feature, disabled by default, cross-database ownership chaining (see KB 810474 for details). When this feature is disabled, ASP.Net session state stops working.

To reconfigure SQL 2000 SP3 for ASP.net session state you must run

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

Now restart your SQL server, then run

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

You might also like...

Comments

Contribute

Why not write for us? Or you could submit an event or a user group in your area. Alternatively just tell us what you think!

Our tools

We've got automatic conversion tools to convert C# to VB.NET, VB.NET to C#. Also you can compress javascript and compress css and generate sql connection strings.

“Brevity is the soul of wit” - Shakespeare