Switching from App session to SQL session state

Setting up 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 similar to how this is done when using substring between two characters.

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.

Published on Jun 14, 2022

Tags: SQL Tutorials for Beginners, Intermediate and Advanced Users | session | ASP.NET MVC and Web API Tutorial