JumpStart # 44 – SQL Server based Session State in ASP.Net Core MVC Application

In this Jumpstart, we are going to see how to use SQL Server as Session store for an ASP.Net Core Application. There are multiple advantages in having a distributed cache storage (in this case it is SQL Server) acting as Session store. Sessions can be shared across multiple applications and also between multiple servers in a cluster. There are also some disadvantages around performance (in-memory is faster than SQL Server based session store) and operational overheads of maintaining additional SQL Server database.

NOTE: Updated this tutorial on 2/26/2017 with MSBuild based Dotnet SDK.

Create an ASP.Net Core Application using VS 2017. I am using following version of SDK which is based on MSBuild/CSProj (remember that previous versions of ASP.Net Core are based on Project.json/XProj.).


Now we need to use AddDistributedSqlServerCache. To use SQL Server Cache, we need to add following packages dependencies and tools.

  <PackageReference Include="Microsoft.ApplicationInsights.AspNetCore" Version="2.0.0" />
  <PackageReference Include="Microsoft.AspNetCore" Version="1.0.3" />
  <PackageReference Include="Microsoft.AspNetCore.Mvc" Version="1.0.2" />
  <PackageReference Include="Microsoft.AspNetCore.Session" Version="1.1.0" />
  <PackageReference Include="Microsoft.AspNetCore.StaticFiles" Version="1.0.1" />
  <PackageReference Include="Microsoft.Extensions.Caching.SqlServer" Version="1.1.0" />
  <PackageReference Include="Microsoft.Extensions.Configuration.UserSecrets" Version="1.1.0" />
  <PackageReference Include="Microsoft.Extensions.Logging.Debug" Version="1.0.1" />
  <PackageReference Include="Microsoft.VisualStudio.Web.BrowserLink" Version="1.0.1" />
  <DotNetCliToolReference Include="Microsoft.VisualStudio.Web.CodeGeneration.Tools" Version="1.0.0-msbuild3-final" />
  <DotNetCliToolReference Include="Microsoft.Extensions.SecretManager.Tools" Version="1.0.0-msbuild3-final" />
  <DotNetCliToolReference Include="Microsoft.Extensions.Caching.SqlConfig.Tools" Version="1.0.0-msbuild3-final" />

Open Command Prompt and navigate to the project directory and execute a dotnet restore as shown below (in my case Visual Studio restored package already).


Create a new database in SQL Server (I am using SQL Server 2016 Express) with name AspNetSessionDB.


Then execute following command to create required AspNetSessions Table in AspNetSessionDB to hold Session values.

dotnet sql-cache create “Data Source=Server Name;Initial Catalog=AspNetSessionDB;Integrated Security=True” “dbo” “AspNetSessions”


Following table will be created in SQL Server.


Now lets configure ASP.Net Core Application with SQL Server Cache in Startup class under ConfigureServices method.

services.AddDistributedSqlServerCache(options =>
    options.ConnectionString = "Server=RAMIDEVVM\\SQLEXPRESS;Database=AspNetSessionDB;Trusted_Connection=True;";
    options.SchemaName = "dbo";
    options.TableName = "AspNetSessions";

services.AddSession(options =>
    options.CookieHttpOnly = true;
    options.CookieName = ".ASPNetCoreSession";
    options.IdleTimeout = TimeSpan.FromMinutes(10);
    options.CookiePath = "/";

As the last step of configuration, lets add Session service to our HTTP Pipeline as shown below in Configure method of Startup class. One important point to be remembered is that UseSession()should be added to pipeline before UseMvc().


To demonstrate usage of session, lets create (or update if they already exist) below actions in HomeController.

public IActionResult Index()
    HttpContext.Session.SetString("User", "Rami");
    return Json(true);

public IActionResult About()
    var userContent = HttpContext.Session.GetString("User");
    return Json(userContent);

Now using Postman tool, lets make a request to Index action. We should see below response.


Lets make a request to About Action. We should see below response with data being loaded from session and sent back in JSON format.


If we look at the AspNetSessions table, data will be in below format.


That’s it for now. Happy Coding and Stay Tuned!!!

You may also like...

  • Muaddib Atredes

    This doesn’t work for me. I have setup my Session state table and configured my app to use AddDistributedSqlServerCache

    For example if do the following in my HomeController index method I don’t get an error, but X is null.

    HttpContext.Session.SetString(“test”, “1”);

    string x = HttpContext.Session.GetString(“test”);

    I’m targeting .net framework 4.6. Could this be the reason. Does it only work with .net core?

  • ramiramilu

    Hi Atredes,

    It worked for me. In fact I tried this approach with latest CSPROJ and MSBUILD based ASP.Net Core Solution. I tried with net461 framework moniker.

    Share your sample code in Github with me. I will look into it.