Production Readiness

This guide is the handoff between “the login flow works” and “the team can safely deploy, scale, restore, and troubleshoot it.” It describes the controls present in the current package and calls out the controls your host or platform must provide.

Production topology#

Use this as the minimum deployment contract:

1. Make the public URL stable#

SqlOS never uses the incoming request Host as the authority for invitation links, provider callbacks, discovery metadata, device verification, or SSO portal links. When PublicOrigin is omitted, those URLs use the authority of the validated absolute Issuer; the default https://localhost/sqlos/auth therefore keeps local development zero-configuration.

For production, set the issuer to the canonical external authorization-server URL. Set PublicOrigin as an explicit readiness setting when a reverse proxy or deployment configuration makes the externally reachable origin non-obvious, and keep it aligned with the issuer:

var publicOrigin = builder.Configuration["SqlOS:PublicOrigin"]
    ?? throw new InvalidOperationException("SqlOS:PublicOrigin is required.");
var connectionString = builder.Configuration.GetConnectionString("DefaultConnection")
    ?? throw new InvalidOperationException("DefaultConnection is required.");
 
builder.AddSqlOS<AppDbContext>(
    db => db.UseSqlServer(connectionString),
    options =>
    {
        options.DashboardBasePath = "/sqlos";
        options.AuthServer.PublicOrigin = publicOrigin;
        options.AuthServer.Issuer = $"{publicOrigin.TrimEnd('/')}/sqlos/auth";
    });

PublicOrigin is optional when the issuer already contains the correct public authority. When configured, it must be an absolute origin only: scheme, host, and optional port, with no path, query, or fragment. With DashboardBasePath = "/sqlos", SqlOS derives the auth base path as /sqlos/auth, and Issuer must be exactly {PublicOrigin}/sqlos/auth.

Examples:

The issuer is a security identifier, not just a link. Resource APIs validate the iss claim against it, and discovery publishes it. Treat an issuer change as a coordinated identity migration: update clients and resource servers, account for tokens minted with the old issuer, and smoke-test discovery and token validation before shifting traffic.

Reverse proxies and forwarded headers#

The trusted issuer/PublicOrigin keeps generated invitation, reset, callback, device, and portal URLs on the external origin regardless of request headers. Also configure ASP.NET Core to accept forwarded scheme and host information only from your known proxy so client-IP policy and the rest of the host see the original request correctly:

using System.Net;
using Microsoft.AspNetCore.HttpOverrides;
 
builder.Services.Configure<ForwardedHeadersOptions>(options =>
{
    options.ForwardedHeaders =
        ForwardedHeaders.XForwardedFor |
        ForwardedHeaders.XForwardedProto |
        ForwardedHeaders.XForwardedHost;
 
    options.KnownProxies.Add(IPAddress.Parse("10.0.0.10"));
});
 
builder.AddSqlOS<AppDbContext>(options =>
{
    // Configure SqlOS as shown above.
});
 
var app = builder.Build();
app.MapSqlOS();

Use your platform's known proxy or network configuration instead of copying an “accept all proxies” snippet. Trusting arbitrary X-Forwarded-* headers lets a caller influence host, scheme, client IP, and any policy built on them.

AddSqlOS installs a startup filter that runs the configured Forwarded Headers middleware before its dashboard middleware. That ordering makes dashboard login throttling and audit events use the trusted external client IP. Configure ForwardedHeadersOptions before building the app; you do not need a second UseForwardedHeaders() call solely for SqlOS.

Dashboard password failures and dynamic client registrations use the existing SqlOS database for shared rate-limit buckets. Multiple application instances therefore enforce one per-client and global state without Redis, a cloud rate-limiting service, or another production dependency. The schema is created automatically with the other SqlOS tables and stale buckets are deleted in bounded batches. Directly constructing the limiter classes outside dependency injection uses a bounded in-memory fallback intended for isolated tests and custom hosts, not multi-instance production.

When X-Forwarded-For processing is enabled for a password dashboard or DCR, SqlOS warns at startup if KnownProxies and KnownNetworks are empty or contain only loopback defaults. Do not suppress that warning by trusting every source: either list the actual proxy boundary or disable forwarded client-address processing and use the direct connection address. A loopback sidecar can be intentional, so this remains an operational warning rather than a startup failure. Test both a trusted proxy request and a forged header from an untrusted address before deployment.

2. Protect the right routes#

Do not place a blanket edge-authentication rule on /sqlos/*. That also captures the OAuth server and hosted sign-in page, causing login redirects, discovery, callbacks, and token exchange to fail.

For the default base path, start with this boundary:

Match the exact /sqlos root and the /sqlos/admin/ path segment. A prefix rule such as /sqlos* is too broad and can also match unrelated application paths.

Restrict portable-client metadata fetches#

CIMD accepts an HTTPS URL as client_id and fetches its metadata from the SqlOS host. General multi-client configuration enables CIMD by default; UseSingleApplication disables it. If portable clients are not a production requirement, set options.AuthServer.ClientRegistration.Cimd.Enabled = false.

When CIMD is enabled, populate Cimd.TrustedHosts so an untrusted host is rejected before the request, and enforce outbound DNS/IP controls against loopback, link-local, cloud metadata, and private-network destinations. An empty allowlist makes any syntactically valid HTTPS metadata host eligible for a fetch. See Client ID Metadata Documents.

SqlOS still enforces its own dashboard policy inside the process. Edge protection is defense in depth, not a replacement for configuring the dashboard.

Keep the hosted UI browser boundary intact#

SqlOS applies browser security headers automatically to every hosted AuthPage, verification page, SSO setup page, and dashboard HTML response:

• X-Frame-Options: DENY;
• Content-Security-Policy with frame-ancestors 'none';
• nonce-bound first-party scripts and styles, with no unsafe-inline script permission;
• X-Content-Type-Options: nosniff;
• Referrer-Policy: no-referrer.

The default policy permits same-origin forms, scripts, styles, fonts, and API calls plus data: images/fonts used by embedded branding. Most applications should leave it alone. If a reviewed dashboard or AuthPage customization requires another trusted source, replace the policy prefix while retaining the per-response nonce placeholder:

options.BrowserSecurity.ContentSecurityPolicy =
    "default-src 'none'; base-uri 'none'; object-src 'none'; form-action 'self'; " +
    "img-src 'self' data: https://cdn.example.com; font-src 'self' data:; connect-src 'self'; " +
    "script-src 'self' 'nonce-{nonce}'; style-src 'self' 'nonce-{nonce}' https://cdn.example.com";

SqlOS validates this value at startup. It must be one header-safe line, must contain {nonce}, and cannot declare frame-ancestors; SqlOS always appends frame-ancestors 'none' so a customization cannot accidentally make credential or consent UI frameable.

Set Strict-Transport-Security at the TLS-terminating reverse proxy or host for the whole application domain, not only SqlOS routes. Verify the header on /sqlos, /sqlos/auth/login, and normal application responses after deployment. Do not enable long-lived HSTS on a domain until every route is reliably HTTPS.

3. Harden dashboard access#

The default dashboard mode is DevelopmentOnly. Without an authorization callback, it is available without a login only when the host environment is Development and returns 404 otherwise. SqlOS logs a startup warning in either case so an accidentally deployed Development environment is visible in operational logs. Dashboard and admin path families are matched on complete URL segments, so lookalike paths such as /sqlos-evil or /sqlos/admin/fga-evil are never treated as SqlOS surfaces. For an operator-accessible production dashboard, configure password mode explicitly:

options.Dashboard.AuthMode = SqlOSDashboardAuthMode.Password;
options.Dashboard.Password =
    builder.Configuration["SqlOS:Dashboard:Password"]
    ?? throw new InvalidOperationException("SqlOS dashboard password is required.");
options.Dashboard.SessionLifetime = TimeSpan.FromHours(2);

Password mode uses a constant-time password comparison, a Data Protection-protected HttpOnly session cookie, login throttling, temporary lockout, and dashboard login/logout audit events. It is a useful baseline, not a complete public admin perimeter.

For production:

• read the password from a secret provider, never source control or a checked-in settings file;
• put the dashboard behind a VPN, identity-aware proxy, private ingress, or equivalent operator access layer;
• keep the session lifetime no longer than operations require;
• leave dashboard login throttling enabled and add edge rate limiting;
• use Dashboard.AuthorizationCallback when the host must impose an additional request-level decision;
• verify both page and API behavior—some unauthorized admin APIs intentionally return 404, while others return 401.

See Dashboard for the operator surfaces and Configuration for local secret setup.

4. Keep secrets out of the deployment artifact#

SqlOS options are assigned in code; the package does not automatically bind a SqlOS configuration section. Read sensitive values from ASP.NET Core configuration providers and assign them inside AddSqlOS.

Typical secrets include:

• dashboard password;
• Google, Microsoft, Apple, GitHub, or custom provider credentials;
• SAML/customer setup material supplied outside metadata;
• Azure Communication Services and Twilio credentials;
• calendar provider client credentials;
• any certificate password or key-encryption credential used by the host.

Startup seeds are reconciled on every startup. If a seed contains a provider secret, configuration remains the source of that secret and can overwrite a dashboard-managed change on the next deployment. Decide which settings are deployment-managed and which are operator-managed; do not alternate between both models accidentally.

SqlOS protects persisted provider credentials and other sensitive values with ASP.NET Core Data Protection, but encryption after ingestion does not make it safe to commit plaintext inputs. Restrict access to the configuration provider and database independently.

5. Persist and share Data Protection keys#

AddSqlOS registers ASP.NET Core Data Protection. The framework's default key storage is not a production durability plan for ephemeral containers or multiple replicas. Configure a shared store before registering SqlOS:

using Microsoft.AspNetCore.DataProtection;
 
builder.Services
    .AddDataProtection()
    .SetApplicationName("contoso-sqlos-production")
    .PersistKeysToFileSystem(new DirectoryInfo("/var/lib/contoso/dpkeys"));
 
builder.AddSqlOS<AppDbContext>(/* ... */);

The filesystem path is only an example. Use a durable local or shared volume for your platform, and protect the key ring at rest with operating-system permissions and encrypted storage. Every replica and every concurrently deployed revision must use the same key ring and the same ApplicationName.

The key ring protects more than dashboard cookies. SqlOS uses it for JWT signing private keys, persisted OIDC client secrets and Apple private keys, TOTP secrets, protected phone values, refresh-rotation replacement tokens, calendar access and refresh tokens, and other protected values.

SqlOS protects JWT private signing keys automatically; there is no signing-key flag to enable. SQL Server stores the public key and an opaque protected reference, so the database remains part of recovery without containing plaintext private key material. For automatic single-host and shared-filesystem deployment examples, see Production Readiness: Signing Keys.

6. Roll schema changes forward through one instance#

SqlOS owns its tables separately from your EF Core migrations. At host startup, SqlOSBootstrapHostedService calls SqlOSBootstrapper, which:

1. discovers embedded, numbered AuthServer SQL scripts;
2. applies pending scripts in deterministic version/name order and records each completed script in SqlOSAppliedMigrations;
3. ensures an active signing key, settings, templates, and startup seeds;
4. applies the FGA scripts tracked by SqlOSFgaSchema;
5. creates or verifies FGA functions and seed data when enabled.

Fga.MaxResourceHierarchyDepth is one shared limit for resource synchronization, manual resource helpers, in-process authorization, and the SQL authorization function. Valid values are 1 through 100 because SQL Server limits the recursive CTE used by list authorization to 100 levels. Startup validates persisted resources against that limit and fails with the affected resource ID when existing data is deeper or cyclic. Before lowering the value, inspect and repair the stored hierarchy in a restored environment; do not lower it during a rollout and rely on authorization to silently truncate deeper ancestors.

The SQL authorization function is installed with CREATE OR ALTER FUNCTION, which requires SQL Server 2016 SP1 or newer. Updating SqlOS does not drop the live function during startup, so concurrent authorization queries never observe a missing-function window. If manual database changes introduce a cycle or a hierarchy deeper than the configured limit, the function terminates and denies access rather than returning a partial inherited grant.

Each AuthServer script and its ledger entry commit in one transaction, so an interrupted startup safely retries the unrecorded script. The initializer does not acquire a distributed migration lock, wrap the entire release upgrade in one cross-script transaction, or provide down migrations.

FGA migration scripts carry a matching persisted schema version. After every FGA migration, SqlOS reloads SqlOSFgaSchema.Version and requires it to match the embedded migration number before startup can continue. A missing, stale, or incorrectly advanced marker fails initialization at the offending migration instead of reporting a schema level the database has not actually reached. Do not edit shipped scripts or their version updates; add a new numbered migration and validate the upgrade against a restored database.

Use this rollout sequence:

1. Back up the database and Data Protection key ring; record the application and SqlOS package versions.
2. Restore that backup into a non-production environment and run the new build against it.
3. In production, hold the old replicas and start one canary/new revision first.
4. Wait for SqlOS initialization complete. and confirm there are no migration, function, seed, or signing-key errors.
5. Run the discovery, JWKS, login, token, API, and logout smoke tests below.
6. Roll the same tested build to the remaining replicas; avoid a prolonged mixed-version fleet.

If application migrations contain foreign keys to SqlOS-owned tables, resolve and call SqlOSBootstrapper.InitializeAsync() before your application's MigrateAsync() step. Do not copy SqlOS tables into your EF migrations. See Schema ownership.

Treat rollback as a recovery decision, not “deploy the old binary and hope.” An older package may not understand a newer, forward-only schema. Rehearse whether your release can roll the binary back safely; otherwise restore a compatible database/key-ring recovery set and application version according to your runbook.

7. Add host-owned health and observability#

The current package does not add health endpoints. Your host should expose separate liveness and readiness checks:

• Liveness answers whether the process should be restarted. Do not make it depend on every external identity provider.
• Readiness should stay false until application startup succeeds and should verify the SQL Server dependency your login and authorization paths require.
• If your policy requires a schema assertion, add a host-owned check for the expected SqlOSSchema and SqlOSFgaSchema versions. Update that expectation with the package version.
• Keep detailed diagnostics private; public health responses should not reveal connection strings, schema names, or provider errors.

At minimum, retain Information logs for the SqlOS category during deploys:

{
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "SqlOS": "Information",
      "Microsoft.EntityFrameworkCore.Database.Command": "Warning"
    }
  }
}

SqlOS logs bootstrap and schema versions, signing-key rotation, provider failures, calendar synchronization, and FGA initialization through normal ILogger categories. Its persisted Audit Logs are a separate security/governance record; ship application logs to your observability system and define retention/export policy for audit records.

Alert on:

• a revision that never reaches ready after a package upgrade;
• any bootstrap, schema, FGA-function, or signing-key rotation error;
• sustained OAuth 5xx responses or provider callback failures;
• dashboard and password-login rate-limit/lockout audit events;
• unusual refresh replay, logout-all, client disable/revoke, or signing-key rotation events;
• database saturation or latency that threatens token and authorization requests.

8. Back up and restore the identity system#

The SQL Server backup must include SqlOS auth, FGA, audit, email, and calendar tables alongside any application rows that refer to them. The recovery plan must also include:

• the matching Data Protection key ring and its at-rest decryption material;
• application configuration and secret-provider versions;
• the application build and SqlOS package version compatible with the restored schema;
• DNS, certificate, proxy, and issuer configuration;
• any external provider callback/redirect configuration that cannot be reconstructed from the database.

Run a restore drill, not just a backup job. In an isolated environment, restore the database and key ring, start the matching build, then prove that existing protected material can be read and a new sign-in can complete. If OIDC, SAML, MFA, calendar, or email are business-critical, include each enabled path in the drill.

Release smoke checklist#

Run this against the public origin through the real proxy, not directly against a pod or container:

Protocol and keys#

• GET /sqlos/auth/.well-known/oauth-authorization-server returns 200 and publishes the exact production issuer.
• GET /sqlos/auth/.well-known/jwks.json returns 200 and at least one current validation key.
• A known public client can start /authorize with an exact registered redirect URI and S256 PKCE.
• Code exchange succeeds once; reuse of the code or a wrong verifier fails.

Tokens and application access#

• The access token signature, expiry, iss, aud, sub, sid, and expected org_id are correct.
• The intended protected API accepts the token.
• A different audience, missing token, malformed token, and revoked session are rejected.
• Refresh rotates the refresh token; logout revokes the session and subsequent SqlOS session-aware validation fails.

User journeys#

• Hosted password or passwordless sign-in works through the external HTTPS origin.
• The dashboard OTP readiness view reports every enabled method ready, and an explicit test reaches operator-controlled email/phone destinations without exposing provider secrets in the response or audit log.
• Each enabled OIDC or SAML provider returns through its production callback.
• Invitation, password-reset, OTP, and MFA messages contain production—not internal—URLs.
• Multi-organization selection and organization-scoped claims behave as expected.

Administration and recovery#

• /sqlos/auth/* remains reachable without operator edge authentication.
• /sqlos, /sqlos/, and operator routes under /sqlos/admin/* are unavailable to untrusted callers, except the narrowly reviewed delegated SSO portal paths when that customer flow is enabled.
• CIMD is disabled or constrained by a pre-fetch host allowlist and outbound network policy.
• An authorized operator can sign in, open the dashboard, and view audit events.
• Restarting or replacing an instance with the shared key ring preserves protected secrets and sessions as designed.
• The current database/key-ring backup has a recent successful restore drill.

Source evidence#

The operational behavior described here is visible in the current source:

• SqlOSPathDefaults and SqlOSOptionsValidator define the base-path, public-origin, and issuer relationship.
• SqlOSBootstrapper shows the startup order.
• SqlOSSchemaInitializer and SqlOSFgaSchemaInitializer execute embedded forward scripts.
• SqlOSCryptoService shows Data Protection-backed persisted secrets and signing-key behavior.
• SqlOSDashboardMiddleware and the auth endpoint mappings show the dashboard/admin boundary.

Next steps#

• Test your integration
• Troubleshooting
• Token validation
• Audit Logs