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

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