Hardening an ASP.NET session state database

Recently I did come across a problem with the ASPState database of ASP.NET. This database is used to store the ASP.NET user session data in a SQL Server instance. The database is being installed by executing Aspnet_regsql.exe with parameter –ssadd (see Session-State Modes). Principally there are two configuration modes: persistent and non-persistent, the latter being the default. In persistent mode the two tables ASPStateTempSessions and ASPStateTempApplications, which hold the current ASP state, are being created in the ASPState database, whereas in the non-persistent mode both tables are being created in tempdb and are recreated each time SQL Server restarts using the startup procedure CreateTempTables. Thus, in persistent mode the ASP state can survive a SQL Server restart, while in non-persistent mode the state is lost whenever SQL Server restarts, which usually is not a problem.

This setup works perfectly for members of the sysadmin fixed server role or the owner of the ASPState database. But, since no permissions are being granted by the setup program, all other principals do not have access to the ASP.NET procedures. When ASP.NET is configured to store its state in SQL Server, the user that connects to the ASPState database is hardcoded together with the password in the connection string stored within the <sessionState> element of the Web.config file. Although there are ways to encrypt this information (see Encrypting Configuration Information Using Protected Configuration), this still could a pose the risk for a security breach when using an administrative account for this purpose. Thus, in order to harden your ASP.NET environment it would be more desirable to have a low privileged SQL Server login and granting this login access to the ASPState database. Now, the only thing that is left is to grant this database user (or the public role) EXECUTE rights on the ASPState procedures. That is all that needs to be done for the persistent mode. But, for the non-persistent mode things are a little bit more tricky.

In the persistent case, it is sufficient to grant EXECUTE rights on the procedures, because access to the tables works by means of an ownership chain, i.e. if the owner of a procedure also owns the tables being accessed by the procedures, no additional checks are being performed when accessing those tables. In the ASPState database the owner of all objects is also the database owner, i.e. the account who did create the ASPState database when running the Aspnet_regsql tool. In the non-persistent mode, where the tables do not reside in the ASPState database but are located in tempdb, there is no direct ownership chain and therefore one more step has to be performed: a cross-database ownership chain has to be established.

First, we need to allow cross-database ownership chains for the participating databases. This can be set server wide by means of a configuration parameter. But this could create undesired security holes. Therefore, it’s always recommended to enable this on the database level. On tempdb this already has been set by default. But on ASPState we have to perform this manually, executing the “ALTER DATABASE ASPState SET DB_CHAINING ON” statement. But still this does not suffice. We are still receiving the error “The INSERT permission was denied on the object 'ASPStateTempSessions', database 'tempdb', schema 'dbo'.” when calling an ASPState procedure, like TempInsertStateItemShort. The problem we are still facing is the fact that cross-database ownership chaining does not work on a user basis, but on a login basis. The owner of the ASPState database is the login that did create the database. But for tempdb it is always the sa login. Thus, for the cross-database ownership chain to work the owners of both databases have to match the same principal. Since it is not practical to change the ownership of tempdb, we have to change it on database ASPState. This is being accomplished by executing “ALTER AUTHORIZATION ON DATABASE::ASPState TO sa”.

To summarize, here are the steps to be performed in order to harden your ASP.NET applications that are using SQL Server mode for storing the session state:

Either create a low privileged login for the identity your ASP.NET applications run with (see Configuring ASP.NET Process Identity) and Integrated Security is used in the sqlConnectionString attribute, or create a SQL Server login to be used for this purpose. After having created the ASPState database by running Aspnet_regsql.exe, define this login as a user on the ASPState database. Then run the following script:

USE master

GO

IF EXISTS (

   SELECT * FROM tempdb.sys.tables

  WHERE schema_id = 1 AND name = 'ASPStateTempSessions')

BEGIN

   ALTER DATABASE ASPState SET DB_CHAINING ON;

   ALTER AUTHORIZATION ON DATABASE::ASPState TO sa;

END

GO

USE ASPState

GO

DECLARE @cmd nvarchar(max)

SET @cmd = N''

SELECT @cmd = @cmd +

      N'GRANT EXECUTE ON dbo.' + QUOTENAME(name) + ' TO PUBLIC;'

FROM   sys.objects

WHERE  type = 'P' AND schema_id = 1 AND name LIKE 'Temp%'

EXEC (@cmd)