Switching from App session to SQL session state

Published on Jun 14, 2022

When you have a single web server, storing the user sessions on the single server works perfect; however, when you have multiple web servers and users need to potentially round robin between servers or you need to remove servers from the pool and don't want to affect your user's experience enter: SQL Session State.



Create the database

Run the following from a command line (with admin priv):
aspnet_regsql.exe -S . -E -ssadd -sstype c -d SqlSession

This will create a new database called SqlSession. This is a prebuilt asp.net database that it will use to store the session data.

Create user and login for the new database

Copy and paste the following script into a sql script window in ssms.

CREATE LOGIN [SqlSession] WITH PASSWORD=N'12345678', DEFAULT_DATABASE=[SqlSession], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=ON

USE SqlSession
GO
CREATE USER [SqlSession] FOR LOGIN [SqlSession] WITH DEFAULT_SCHEMA=[dbo]
GO
EXEC sp_addrolemember N'db_datawriter', 'SqlSession'
GO
EXEC sp_addrolemember N'db_datareader', 'SqlSession'
GO
Grant Execute to [SqlSession]
go

Update the web.config

The final step is to change the existing sessionState to use the SQLServer mode. Find the sessionState setting in the web.config, in system.web.

To switch to sql session state (new way), use:


Add a new machine key entry to the web.config in system.web

<machinekey decryption="AES" decryptionkey="D4270FE59953F08B02DD9A33BDE1B4515549198937D176C8" validation="HMACSHA256" validationkey="8AA9BA9F511F8BBEF6F7445FE4C230BDA2D9B6DFEC4E676A5F2C55ED82DD5EC01E8D59D7D4621E18E5CAE5714B6C112EA64348EB59A616E560AC0B19B70EC386">
</machinekey>

The machine key is required for cookie based authentication in a multi-server environment. That's it, you've now successfully switched to storing your authentication sessions inside a database called SqlSession.

Tags: SQL | session | ASP.NET MVC and Web API Tutorial