SingleSignOnLoginProcedure (SAML)

 Show all Hide all

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.

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.

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 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;
Type: Stored procedure
Area of use: Editable

Parameters:

@SamlResponse mandatory xml
The SAML Response received from the Identity Provider. Any XML signatures on the response have been verified and then removed.

Resultsets:

Resultset: User ID

The procedure should return the ID the user should be logged in as.

The Identity Provider is supposed to be the party that decides who gets access to the system, so ideally the stored procedure should always create a user and returns the user ID. Nevertheless, you can deny access by returning NULL as UserId though the end user will be faced with a most user-unfriendly 403-error page.
Repeat mode: repeated exactly once
Row count: exactly one row

Columns:

UserId mandatory int
The User ID.