<< Back To All Blogs
Configuring ASP.NET (And SharePoint) to use SQL-based Sessions
Thursday, October 29th, 2009
ASP.NET allows for servers to store their session variables and state information on a SQL server, which is a very useful ability, especially in the
case of running clustered servers, or a server farm of web front ends in the case of SharePoint. When you move to a clustered or server farm scenario, you can no longer use InProc, or single-server-only session solutions, as they must be shared across multiple physical machines in order to prevent continuity errors for end users.
It is not a very difficult process, but the documentation required to do so is scattered over a number of different sections of MSDN documentation.
Here is the process that I used to recently complete migrating a group of ASP.NET servers to SQL-based sessions.
Create a SQL-based user, as well as a database for each cluster, or farm, you would like to use for SQL-based sessions. Make this user the owner of the database you create.
Configure the server:
Navigate to C:\WINDOWS\Microsoft.NET\Framework\v2.x\ and run the following command:
aspnet_regsql -S SQLSERVERADDRESS -U SAUSERNAME -P SAPASSWORD -d DATABASEYOUCREATEDABOVE -ssadd -sstype c
Please note that you need to create a non-sa user to configure later, but this command must be run as SA in order to create the proper tables in the sessions database, as well as configure options within SQL server which require SA access. You can view the full documentation for aspnet_regsql here.
Some documentation on MSDN points to an older method of executing InstallSqlState.sql which is located in the same directory as the aspnet_regsql tool. aspnet_regsql uses this same script internally, but Microsoft now recommends users to use this utility to avoid further typos, among other reasons.
Verify In SQL:
After executing this command, Fire up SQL Management Studio and login to the database with the username and password you created above, verify that tables were created, and verify that you can indeed connect to the database with the credentials.
Configure the server:
Now navigate to the web.config file which you wish to configure. In the case of SharePoint, this file is by default located in C:\InetPub\wwwroot\web.config.
Find the sessionState element within web.config.
Now add or modify the following attributes:
allowCustomSqlDatabase="true" (This is very important as it allows you to use a custom database, which otherwise ASP.NET will not allow you to do via error)
sqlConnectionString="data source=SERVER;initial catalog=DATABASENAME;uid=USERNAME;pwd=PASSWORD;"
Save web.config, and execute iisreset to restart the IIS server, and you should be good to go.
To verify it is working properly, which you should get an error if it is not, you can also connect to the database via SQL management studio and list data within the tables.
Sessionin' Tom Out.
Impersonating a user in ASP.NET
Resolving ASP.NET Web.Config Inheritance
Validate a Windows Username and Password against Active Directory
Resolving Odd 5 Minute Timeout in HttpWebRequest.
Retrieving data from SharePoint SOAP Requests using LINQ
Currently no comments.
Add A Comment
Email Address: (not public, used to send notifications on further comments)
Enter the text above, except for the 1st and last character: