Sql server storage

This page describes the use of the Sql Server Storage strategy.

Prerequisites

The SqlServerClientStore and SqlServerNonceStore are included in the Dalion.HttpMessageSigning.Verification.SqlServer package. Install into your .NET Core or .NET Framework project:

dotnet add package Dalion.HttpMessageSigning.Verification.SqlServer

or

PM> Install-Package Dalion.HttpMessageSigning.Verification.SqlServer

Execute the following script in your target database:

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Clients] (
    [Id] [nvarchar](100) NOT NULL,
    [Name] [nvarchar](100) NOT NULL,
    [NonceLifetime] [decimal](5, 0) NOT NULL,
    [ClockSkew] [decimal](5, 0) NOT NULL,
    [SigType] [nvarchar](20) NOT NULL,
    [SigParameter] [nvarchar](max) NOT NULL,
    [SigHashAlgorithm] [nvarchar](20) NOT NULL,
    [IsSigParameterEncrypted] [bit] NOT NULL,
    [RequestTargetEscaping] [nvarchar](20) NOT NULL,
    [V] [tinyint] NOT NULL,
    CONSTRAINT [PK_Clients] PRIMARY KEY CLUSTERED ([Id] ASC)
)
GO

CREATE TABLE [dbo].[ClientClaims](
    [ClientId] [nvarchar](100) NOT NULL,
    [Type] [nvarchar](100) NOT NULL,
    [Value] [nvarchar](100) NOT NULL,
    [Issuer] [nvarchar](100) NOT NULL,
    [OriginalIssuer] [nvarchar](100) NOT NULL,
    [ValueType] [nvarchar](100) NOT NULL
)
GO

ALTER TABLE [dbo].[ClientClaims] WITH CHECK ADD CONSTRAINT [FK_ClientClaims_Clients] FOREIGN KEY([ClientId])
    REFERENCES [dbo].[Clients] ([Id])
    ON UPDATE CASCADE
    ON DELETE CASCADE
GO

ALTER TABLE [dbo].[ClientClaims] CHECK CONSTRAINT [FK_ClientClaims_Clients]
GO

CREATE TABLE [dbo].[ClientMigrations] (
    [Id] [nvarchar](100) NOT NULL,
    [StepName] [nvarchar](100) NOT NULL,
    [Time] [datetimeoffset](3) NOT NULL,
    [MigratorVersion] [nvarchar](20) NOT NULL,
    CONSTRAINT [PK_ClientMigrations] PRIMARY KEY CLUSTERED([Id] ASC)
)
GO

CREATE TABLE [dbo].[Nonces] (
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [ClientId] [nvarchar](100) NOT NULL,
    [Value] [nvarchar](36) NOT NULL,
    [Expiration] [datetimeoffset](3) NOT NULL,
    [V] [tinyint] NOT NULL,
    CONSTRAINT [PK_Nonces] PRIMARY KEY CLUSTERED([Id] ASC)
)
GO

IF EXISTS (SELECT [name] FROM sys.indexes WHERE [name] = N'IX_Expiration')
    DROP INDEX IX_Expiration ON [dbo].[Nonces];
GO

CREATE NONCLUSTERED INDEX IX_Expiration
    ON [dbo].[Nonces] ([Expiration]);

GO

IF EXISTS (SELECT [name] FROM sys.indexes WHERE [name] = N'IX_Client_Value')
    DROP INDEX IX_Client_Value ON [dbo].[Nonces];
GO

CREATE UNIQUE NONCLUSTERED INDEX IX_Client_Value
    ON [dbo].[Nonces] ([ClientId], [Value]);

GO

CREATE TABLE [dbo].[NonceMigrations] (
    [Id] [nvarchar](100) NOT NULL,
    [StepName] [nvarchar](100) NOT NULL,
    [Time] [datetimeoffset](3) NOT NULL,
    [MigratorVersion] [nvarchar](20) NOT NULL,
    CONSTRAINT [PK_NonceMigrations] PRIMARY KEY CLUSTERED([Id] ASC)
)
GO

Client store configuration

Register it in your IoC container, to use it instead of the default InMemoryClientStore:

public void ConfigureServices(IServiceCollection services) {
    services
        .AddHttpMessageSignatureVerification()
        .UseSqlServerClientStore(provider => new SqlServerClientStoreSettings {
            ConnectionString = "Server=my_sql_server;Database=HttpMessageSigning;User Id=dalion;Password=Dalion123;",
            ClientsTableName = "dbo.Clients",
            ClientClaimsTableName = "dbo.ClientClaims",
            VersionsTableName = "dbo.ClientVersions",
            ClientCacheEntryExpiration = TimeSpan.FromMinutes(3),
            SharedSecretEncryptionKey = "The_Big_S3cr37"
        });
}

The ClientCacheEntryExpiration property specifies the amount of time that Clients are cached in memory. During that time, the Sql Server table is not queried when verifying signatures. You can disable in-memory caching by setting this property to TimeSpan.Zero.

The SharedSecretEncryptionKey property specifies the symmetric key to use when encrypting the HMAC key for storage in Sql Server. To disable HMAC key encryption, omit this setter, or specify SharedSecretEncryptionKey.Empty. This setting has no effect on storing Clients using RSA or ECDsa signature algorithms.

There are a number of convenience overloads to the UseSqlServerClientStore method. Use the one that suits your needs best.

Note

Ensure that the SQL login has read and write permissions on the SQL tables.

Nonce store configuration

Register it in your IoC container, to use it instead of the default InMemoryNonceStore:

public void ConfigureServices(IServiceCollection services) {
    services
        .AddHttpMessageSignatureVerification()
        .UseSqlServerNonceStore(provider => new SqlServerNonceStoreSettings {
            ConnectionString = "Server=my_sql_server;Database=HttpMessageSigning;User Id=dalion;Password=Dalion123;",
            NonceTableName = "dbo.Nonces",
            VersionsTableName = "dbo.NonceVersions"
        });
}

Expired nonce values are automatically deleted by this library.

There are a number of convenience overloads to the UseSqlServerNonceStore method. Use the one that suits your needs.

Samples

A sample is available in the repository source: ASP.NET Core web application (.NET 10.0)