SingleSignOnLoginProcedure (SAML)

This procedure is used with SAML 2.0-based single sign on. It is called each time a user logs in and serves to create and customize the user's account and permissions.

The procedure receives a SAML Response containing a SAML Assertion that describes the user. It must extract at least the username, and preferably also first name, last name and, email address, as well as group memberships or other permissions from this assertion. It must then create or update a Softadmin-user with these values and return its UserId.

CREATE PROCEDURE dbo.ExampleSamlLogin
    @SamlResponse xml
AS
BEGIN
    SET XACT_ABORT ON;

    -- Get asssertion from Response.
    DECLARE @AssertionXml xml = @SamlResponse.query('
        declare namespace saml="urn:oasis:names:tc:SAML:2.0:assertion";
        declare namespace samlp="urn:oasis:names:tc:SAML:2.0:protocol";
        (samlp:Response/saml:Assertion[1])');

    -- Get username from Subject.
    DECLARE @Username varchar(120) = @AssertionXml.value('
        declare namespace saml="urn:oasis:names:tc:SAML:2.0:assertion";
        (saml:Assertion/saml:Subject/saml:NameID)[1]', 'varchar(120)');

    IF @Username IS NULL
    BEGIN
        RAISERROR('Was unable to determine a username from the supplied assertion.', 16, 1);
        RETURN -1;
    END;

    -- Get attributes from AttributeStatement.
    CREATE TABLE #Attribute
    (
        AttributeName  nvarchar(4000) NULL,
        AttributeValue nvarchar(4000) NULL
    );

    INSERT #Attribute
    (
        AttributeName,
        AttributeValue
    )
    SELECT
        Attributes.Attribute.value('@Name', 'nvarchar(4000)'),
        AttributeValues.AttributeValue.value('.', 'nvarchar(4000)')
    FROM
        @AssertionXml.nodes('
            declare namespace saml="urn:oasis:names:tc:SAML:2.0:assertion";
            (saml:Assertion/saml:AttributeStatement/saml:Attribute)') Attributes(Attribute)
        CROSS APPLY Attributes.Attribute.nodes('
            declare namespace saml="urn:oasis:names:tc:SAML:2.0:assertion";
            (saml:AttributeValue/text())') AttributeValues(AttributeValue);

    -- Get user properties from the attributes.
    DECLARE @UsernameFirst nvarchar(50) =
    (
        SELECT AttributeValue
        FROM #Attribute
        WHERE AttributeName = 'http://schemas.xmlsoap.org/ws/2005/05/identity/claims/givenname'
    );

    DECLARE @UsernameLast nvarchar(50) =
    (
        SELECT AttributeValue
        FROM #Attribute
        WHERE AttributeName = 'http://schemas.xmlsoap.org/ws/2005/05/identity/claims/surname'
    );

    DECLARE @UserEmail varchar(100) =
    (
        SELECT AttributeValue
        FROM #Attribute
        WHERE AttributeName = 'http://schemas.xmlsoap.org/ws/2005/05/identity/claims/emailaddress'
    );

    -- Create or update the user.
    DECLARE @UserId int =
    (
        SELECT UserId
        FROM SoftadminApi.[User]
        WHERE Username = @Username
    );

    IF SoftadminApi.User_IsSoftadminAdministrator(@UserId) = 1
    BEGIN
        RAISERROR('User %d is an administrator account and should not use SAML login.', 16, 1, @UserId);
        RETURN -1;
    END;

    EXEC SoftadminApi.User_InsertUpdate
        @id            = @UserId OUTPUT,
        @UserName      = @Username,
        @UserNameFirst = @UsernameFirst,
        @UserNameLast  = @UsernameLast,
        @UserEmail     = @UserEmail,
        @IsEnabled     = 1,
        @Password      = NULL,
        @RoleId        = NULL;

    -- Return the ID to the caller.
    SELECT
        @UserId AS UserId;
END;

Example procedure

This is a fully working example of a procedure handing an assertion from an AD FS-server. Other SAML Identity Providers and older versions of AD FS will use different attribute names.

Example of working with groups

If you want to map Active Directory security groups to function groups in the system then you need a table that maps group names to function group ids, and to reassign user functions for each login.

    BEGIN TRANSACTION;

        EXEC SoftadminApi.User_InsertUpdate
            @id            = @UserId OUTPUT
            .........


        DELETE dbo.UserFunctionGroup WHERE UserId = @UserId;

        INSERT dbo.UserFunctionGroup (UserId, FunctionGroupId)
        SELECT
            @UserId,
            FG.FunctionGroupId
        FROM
            #Attribute A
            JOIN dbo.FunctionGroup FG ON FG.ActiveDirectoryName = A.AttributeValue
        WHERE
            A.AttributeName = 'http://schemas.xmlsoap.org/claims/Group';

        EXEC dbo.UserFunctionGroup_Refresh
            @UserId = @UserId;
            
    COMMIT TRANSACTION;