Sessions with SQL Server

Use SQL Server as the distributed cache backing for ASP.NET Core session state.

Full source

File: pwsh/tutorial/examples/19.3-Sessions-Sql.ps1

<#
    Sample Kestrun Server Configuration – Sessions Demo
    This script shows how to enable Session middleware and use it in routes.
    FileName: 19.3-Sessions-Sql.ps1
#>

param(
    [int]$Port = 5000,
    [IPAddress]$IPAddress = [IPAddress]::Loopback
)

# Configure default logging
New-KrLogger |
    Set-KrLoggerLevel -Value Debug |
    Add-KrSinkConsole |
    Register-KrLogger -Name 'myLogger' -SetAsDefault

# Create a new Kestrun server
New-KrServer -Name 'Sessions Demo Server'


# Listener
Add-KrEndpoint -Port ($Port) -IPAddress $IPAddress -SelfSignedCert

# --- Session setup -----------------------------------------------------------
# Cookie is optional, but nice to be explicit.
$cookie = New-KrCookieBuilder `
    -Name 'Kr.Session' `
    -HttpOnly `
    -SameSite Lax `
    -SecurePolicy Always  # set to None if you want to test over plain http

# SQL Server configuration (if using SQL Server)
Add-KrDistributedSqlServerCache -ConnectionString 'Server=localhost;Database=KestrunSessions;User Id=sa;Password=Your_password123;' `
    -SchemaName 'dbo' -TableName 'Sessions'

# Add session services + middleware. By default, Add-KrSession will ensure a distributed memory cache exists.
Add-KrSession -Cookie $cookie -IdleTimeout 20 -IOTimeout 10


# Enable configuration
Enable-KrConfiguration

# Tip:
# If you're wiring Redis/SQL yourself, use: Add-KrSession -NoDistributedMemoryCache
# and add your cache provider first.

# --- Routes -----------------------------------------------------------------

# 1) Simple counter: increments an integer stored in session.
Add-KrMapRoute -Verbs Get -Path '/session/counter' -ScriptBlock {
    # Get current value (or 0 if not set)
    $current = Get-KrSessionInt32 -Key 'counter'
    # Increment and store back
    Set-KrSessionInt32 -Key 'counter' -Value ($current + 1)

    Write-KrJsonResponse -StatusCode 200 -InputObject @{
        message = 'Counter incremented'
        counter = $current + 1
    }
}

# 2) Login: set a 'user' value in session (?user=max)
Add-KrMapRoute -Verbs Get -Path '/session/login' -ScriptBlock {
    # Get user from query
    $user = Get-KrRequestQuery -Name 'user'
    # Validate
    if ([string]::IsNullOrWhiteSpace($user)) {
        Write-KrJsonResponse -StatusCode 400 -InputObject @{
            error = "Missing 'user' query parameter"
            example = '/session/login?user=max'
        }
        return
    }
    # Store in session
    Set-KrSessionString -Key 'user' -Value $user
    # respond to login event
    Write-KrJsonResponse -StatusCode 200 -InputObject @{
        message = 'Logged in'
        user = $user
    }
}

# 3) WhoAmI: read 'user' from session
Add-KrMapRoute -Verbs Get -Path '/session/whoami' -ScriptBlock {
    # Get user from session
    $user = Get-KrSessionString -Key 'user'
    # If not set, return 401
    if ([string]::IsNullOrEmpty($user)) {
        Write-KrJsonResponse -StatusCode 401 -InputObject @{
            error = 'No active session or user not set'
        }
        return
    }
    # respond with user info
    Write-KrJsonResponse -StatusCode 200 -InputObject @{
        user = $user
    }
}

# 4) Logout: clear the session
Add-KrMapRoute -Verbs Get -Path '/session/logout' -ScriptBlock {
    # Clear the session
    Clear-KrSession
    # respond to logout event
    Write-KrJsonResponse -StatusCode 200 -InputObject @{
        message = 'Session cleared'
    }
}

# 5) Generic set: /session/set?key=foo&value=bar
Add-KrMapRoute -Verbs Get -Path '/session/set' -ScriptBlock {
    # Get key and value from query
    $key = Get-KrRequestQuery -Name 'key'
    $value = Get-KrRequestQuery -Name 'value'

    # Validate
    if ([string]::IsNullOrWhiteSpace($key) -or [string]::IsNullOrWhiteSpace($value)) {
        Write-KrJsonResponse -StatusCode 400 -InputObject @{
            error = "Missing 'key' and/or 'value' query parameter(s)"
            example = '/session/set?key=color&value=purple'
        }
        return
    }
    # Store in session
    Set-KrSessionString -Key $key -Value $value
    # respond to set event
    Write-KrJsonResponse -StatusCode 200 -InputObject @{
        message = 'Stored in session'
        key = $key
        value = $value
    }
}

# 6) Generic get: /session/get?key=foo
Add-KrMapRoute -Verbs Get -Path '/session/get' -ScriptBlock {
    # Get key from query
    $key = Get-KrRequestQuery -Name 'key'
    # Validate
    if ([string]::IsNullOrWhiteSpace($key)) {
        Write-KrJsonResponse -StatusCode 400 -InputObject @{
            error = "Missing 'key' query parameter"
            example = '/session/get?key=color'
        }
        return
    }
    # Get from session
    $val = Get-KrSessionString -Key $key
    # If not found, return 404
    if ($null -eq $val) {
        Write-KrJsonResponse -StatusCode 404 -InputObject @{
            error = "Key '$key' not found in session"
        }
        return
    }
    # respond with value
    Write-KrJsonResponse -StatusCode 200 -InputObject @{
        key = $key
        value = $val
    }
}

# Convenience: hello (non-session)
Add-KrMapRoute -Verbs Get -Path '/hello' -ScriptBlock {
    Write-KrTextResponse -StatusCode 200 -InputObject 'Hello, Session World!'
}

# Start the server asynchronously
Start-KrServer -CloseLogsOnExit

Step-by-step

  1. Logging: Register a console logger and set level to Debug.
  2. Server: Create a Kestrun server named “Sessions Demo Server”.
  3. Listener: Listen on loopback using a self-signed certificate on the provided port (HTTPS).
  4. Cookie: Build a secure session cookie (Kr.Session) with HttpOnly, SameSite=Lax, and SecurePolicy=Always.
  5. SQL cache: Configure SQL Server using Add-KrDistributedSqlServerCache -ConnectionString ... -SchemaName dbo -TableName Sessions.
  6. Session: Enable session services and middleware with idle (20s) and I/O (10s) timeouts.
  7. Configure: Call Enable-KrConfiguration to build the app.
  8. Routes: Map endpoints for counter, login, whoami, logout, set, and get using session cmdlets.
  9. Start: Run the server asynchronously with Start-KrServer.

Try it

Because this sample uses a self-signed certificate and a Secure cookie, use HTTPS and skip certificate verification during local testing.

$base = 'https://127.0.0.1:8080'

# Counter increments with the same cookie jar
curl -k -s -c jar.txt -b jar.txt "$base/session/counter" | jq
curl -k -s -c jar.txt -b jar.txt "$base/session/counter" | jq

# WhoAmI requires login
curl -k -s -c jar.txt -b jar.txt -i "$base/session/whoami"

# Login and read identity
curl -k -s -c jar.txt -b jar.txt "$base/session/login?user=max" | jq
curl -k -s -c jar.txt -b jar.txt "$base/session/whoami" | jq

# Generic set/get within the same session
curl -k -s -c jar.txt -b jar.txt "$base/session/set?key=color&value=purple" | jq
curl -k -s -c jar.txt -b jar.txt "$base/session/get?key=color" | jq

# Logout clears the session
curl -k -s -c jar.txt -b jar.txt "$base/session/logout" | jq
curl -k -s -c jar.txt -b jar.txt -i "$base/session/whoami"

PowerShell equivalents (Invoke-WebRequest):

$base = 'https://127.0.0.1:8080'
$sess = New-Object Microsoft.PowerShell.Commands.WebRequestSession

# Counter increments with the same WebRequestSession
((Invoke-WebRequest -Uri "$base/session/counter" -WebSession $sess -SkipCertificateCheck).Content | ConvertFrom-Json).counter
((Invoke-WebRequest -Uri "$base/session/counter" -WebSession $sess -SkipCertificateCheck).Content | ConvertFrom-Json).counter

# WhoAmI requires login (expected 401 before login)
try {
    Invoke-WebRequest -Uri "$base/session/whoami" -WebSession $sess -SkipCertificateCheck -ErrorAction Stop | Out-Null
} catch {
    'status: 401 (unauthorized)'
}

# Login and read identity
Invoke-WebRequest -Uri "$base/session/login?user=max" -WebSession $sess -SkipCertificateCheck | Out-Null
(Invoke-WebRequest -Uri "$base/session/whoami" -WebSession $sess -SkipCertificateCheck).Content | ConvertFrom-Json

# Generic set/get within the same session
Invoke-WebRequest -Uri "$base/session/set?key=color&value=purple" -WebSession $sess -SkipCertificateCheck | Out-Null
(Invoke-WebRequest -Uri "$base/session/get?key=color" -WebSession $sess -SkipCertificateCheck).Content | ConvertFrom-Json

# Logout clears the session
Invoke-WebRequest -Uri "$base/session/logout" -WebSession $sess -SkipCertificateCheck | Out-Null
try {
    Invoke-WebRequest -Uri "$base/session/whoami" -WebSession $sess -SkipCertificateCheck -ErrorAction Stop | Out-Null
} catch {
    'status: 401 (unauthorized)'
}

Tip: Ensure the SQL cache table is created using the standard schema for Microsoft.Extensions.Caching.SqlServer.

Key Points

  • Use SQL Server for session storage compatible with multi-instance deployments.
  • The sample demonstrates Add-KrDistributedSqlServerCache plus Add-KrSession.
  • Cookie settings matter in production: Secure, HttpOnly, SameSite.
  • Session must be enabled before routes that depend on it.

Troubleshooting

  • SQL connection errors: Verify credentials and network connectivity.
  • Table not found: Ensure the cache table is provisioned; see official docs for schema.
  • Session not persisting: Confirm cookie is preserved and DB operations succeed.

References

Previous / Next

Previous: Sessions with Redis Next: Index