Configure SQLServer sessionState for Umbraco

Configure Umbraco for SQLServer sessionState storage, for your sessions. This post explains how to prepare your Umbraco website for a high performance web garden or web farm, and load balancing environments: Store your ASP.NET / Umbraco sessions out-of-process (OutProc). As opposed to the default in-process (inProc) sessions, where sessions are saved in the worker process.

Create a high performing, fast loading Umbraco website.

High Performance Umbraco

We all want a high performing and fast loading web site. Whether the website is based on Umbraco, WordPress, DotNetNuke or Joomla. For ASP.NET websites, when one application pool worker process, or even one web server, isn't enough and you need to increase the number of worker processes (per application pool - web garden) or web servers (web farm), you have to store your ASP.NET sessions out-of-process.

How to optimize Umbraco 8 performance

Andras Nemes wrote an excellent blog series on web farms, .NET and sessions/sessionState. I suggest you read it if you are not familiar with such web hosting environments.

Let's eleborate a bit:

A web garden is the concept of having multiple worker processes configured in one applicaton pool. A web farm is having multiple (virtual) web servers for your site.

You have to configure out-of-process sessionState, because ASP.NET sessions lose their state in such environments. You never know which process or server picks up a particular request. If a request in which a session was made, is picked up by a different worker process, your session is lost because that worker process has no knowledge about the session being made earlier.

And if sessions are important for your site, then why not use SQL Server Database Mirroring? Learn how to failover all databases in an SQL Server Database Mirroring configuration using T-SQL.

Now back to Umbraco 7.1.4.

Umbraco SQLServer sessionState: Prepare your SQL Server Database for Session Storage

First a little disclaimer:

Our SQL Server database users don't have dbo-permission to create or delete SQL jobs. I had to use a more privileged account. You know you are in the same situation if the following error is thrown:

An error occurred during the execution of the SQL file 'InstallSqlState.sql'. The SQL error number is 229 and the SqlException message is: The EXECUTE permission was denied on the object 'sp_delete_job', database 'msdb', schema 'dbo'. If the job does not exist, an error from msdb.dbo.sp_delete_job is expected. SQL Server: sql.example.com Database: example SQL file loaded: InstallSqlState.sql

We use the ASP.NET SQL Server Registration Tool (Aspnet_regsql.exe) command to set up and configure our SQL Server database for SessionState session storage. Depending on your permissions, you use the following command, use a more privileged account or ask your administrator.

All in one command:

C:\Windows\Microsoft.NET\Framework\v4.0.30319>aspnet_regsql.exe
  -S sql.example.com
  -U user
  -P password
  -ssadd
  -sstype c
  -d database_name

In my environment, I need to use an administrator account to set up the sessionStorage database, using the -Eparameter. You then must leave out -U user and -P password:

If option -E is used, then the following options are illegal: -U and -P.

The -E option tells aspnet_regsql.exe to authenticate using the Windows credentials of the currently logged-on user. 

C:\Windows\Microsoft.NET\Framework\v4.0.30319>aspnet_regsql.exe
  -E
  -S sql.example.com
  -ssadd
  -sstype c
  -d database_name

An important note on -sstype c:

c: custom. Both session state data and the stored procedures are stored in a custom database. The database name must be specified.

If all goes well (...) the database is configured. Then aspnet_regsql.exe responds with:

Microsoft (R) ASP.NET SQL Registration Tool version 4.7.3062.0
Administrative utility to install and uninstall ASP.NET features on a SQL server.
Copyright (C) Microsoft Corporation. All rights reserved.

Start adding session state.
..
Finished

To use this custom session state database in your web application, please specify it in the configuration file by using the 'allowCustomSqlDatabase' and 'sqlConnectionString' attributes in the <system.web>\<sessionState> section.

And your dbo.ASPStateTempApplications and dbo.ASPStateTempSessions tables are created:

SQL Server Agent Job

Depending on how exactly you created the database, it might be required for you to change the account/owner under which the SQL Server Agent Job runs.

Connect to your SQL Server as an administrator and reconfigure your DeleteExpiredSessions job to run as your contained SQL database user.

The next step is to configure Umbraco to use SQLServer sessionState.

Configure Umbraco web.config for SQLServer sessionState

For Umbraco in High Availability, Load Balanced Environments:

Once your SQL Server database is configured, open up your Umbraco web.config file to configure ASP.NET sessionState. Look for the lines:

  <providers>
    <add name="DefaultSessionProvider"
      type="System.Web.Providers.DefaultSessionStateProvider,
      System.Web.Providers, Version=1.0.0.0, Culture=neutral,
      PublicKeyToken=31bf3856ad364e35"
      connectionStringName="DefaultConnection" />
  </providers>
</sessionState>

This needs to be changed to use SQLServer mode sessionState, copy and adjust the following:

<sessionState
  allowCustomSqlDatabase="true"
  mode="SQLServer"
  sqlConnectionString="Server=sql.example.com;Database=example;User
  ID=example;Password=p4Ssw0rd"
  useHostingIdentity="true">
  <providers>
    <add
      name="DefaultSessionProvider"
      type="System.Web.Providers.DefaultSessionStateProvider,
      System.Web.Providers, Version=1.0.0.0, Culture=neutral,
      PublicKeyToken=31bf3856ad364e35"
      connectionStringName="umbracoDbDSN" />
  </providers>
</sessionState>

I had to fiddle a bit to get sessionState to work, but this seems right:

If your sessionState SQLServer mode still doesn't work, locate the <httpModules> node in your web.config, and add:

<remove name="Session" />
<add name="Session" type="System.Web.SessionState.SessionStateModule" preCondition="" />

Next, locate the <modules runAllManagedModulesForAllRequests="true"> node, where you add:

<remove name="Session" />
<add name="Session" type="System.Web.SessionState.SessionStateModule" />

Don't forget to keep runAllManagedModulesForAllRequests="true", this might be necessary. As with <pages enableSessionState="true"> too.

Don't use cookieless="UseUri" in your sessionState!

That's it! :-)

Now you can upload your web.config file and Umbraco should create and use sessions in you SQL Server database. Please verify and send me your corrections.

ASP.NET SQLServer sessionState test script

Do you want to test your newly configured SQLServer sessionState? Copy and paste the following code in a new file, which you can call test.aspx for instance.

<%@ Page Language="VB" %>
<script language="VB" runat="server">
  Sub Page_Load(Sender As Object, E As EventArgs)
    Session("somevar") = "somevalue"
    SessionState.Text = Session.SessionID
  End Sub
</script>
<html>
  <head>
  </head>
  <body>
    <form id="form1" runat="server">
      <div>
        <asp:Label id="SessionState" runat="server"/></asp:Label>
      </div>
    </form>
  </body>
</html>

Maybe you also want to test your MySQL connection from ASP.NET?

Add ~/test.aspx to your Umbraco web.config file, in the umbracoReservedUrls key and you should see a Session.SessionID in your browser when you browse to ~/test.aspx.

Update 2018-29-10: tested and re/done with Umbraco 7.12.3, ASP.NET 4.7.2, SQL Server 2017 and contained databases / users. You're welcome :)

Thank you very much! <3 ❤️

Jan Reilink

Hi, my name is Jan. I am not a hacker, coder, developer or guru. I am merely an application manager / systems administrator, doing my daily thing at Embrace – The Human Cloud. In the past I worked at CLDIN (CLouDINfra) and Vevida. With over 20 years of experience, my specialties include Windows Server, IIS, Linux (CentOS, Debian), security, PHP, websites & optimization. I blog at https://www.saotn.org.