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
SQL
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:
CODE
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:
CODE
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
SQL
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:
SQL
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!