SingleSignOnOpenIdConnectSignInProcedure

This procedure is called a user signs in with OpenID Connect. Its purpose is to transform the claims in the ID token (and optionally also claims from the @ExtraUserInfo parameter) into a user, which involves either updating an existing user or generating a new one.

Example ID token

This is an example of what an ID token might look like

{
    "at_hash": "R4HNCtA0ul4-yPXu7D7UWA",
    "sub": "user-subject-1234531",
    "aud": "Softadmin",
    "iss": "https://www.certification.openid.net/test/a/dsa43fdgsgf/",
    "exp": 1697558253,
    "nonce": "638331547528131249.TNXmEdaCeFv52FFCWzagkEc7qUNsRTgawkGrYUUCso8=",
    "email": "bob@example.com",
    "given_name": "Bob",
    "family_name": "Smith",
    "locale": "en"
}

Example implementation of procedure

CREATE PROCEDURE Example.OpenIdSignIn
	@IdToken nvarchar(MAX),
	@ExtraUserInfo nvarchar(MAX) = NULL
AS
BEGIN
	SET XACT_ABORT, NOCOUNT ON;

	-- Get claims from the id token.
	DECLARE
		@Issuer		nvarchar(MAX),
		@Subject	nvarchar(MAX),
		@Email		nvarchar(MAX),
		@GivenName	nvarchar(MAX),
		@FamilyName nvarchar(MAX),
		@Locale		nvarchar(MAX);

	SELECT
		-- issuer and subject are guaranteed to exist in the id token.
		@Issuer = JSON_VALUE(@IdToken, '$.iss'),
		@Subject = JSON_VALUE(@IdToken, '$.sub'),
		-- The following are just examples.
		-- A server may not return them, or return them in @ExtraUserInfo instead of in @IdToken.
		@Email = JSON_VALUE(@IdToken, '$.email'),
		@GivenName = JSON_VALUE(@IdToken, '$.given_name'),
		@FamilyName = JSON_VALUE(@IdToken, '$.family_name'),
		@Locale = JSON_VALUE(@IdToken, '$.locale');

	-- Subject is a claim that will always be present and so it is used in this example.
	-- There may be another claim that is more suitable for usernames sent by the server
	-- you're integrating against.
	-- Using subject alone as username is NOT a good idea if you have more than one IdP.
	DECLARE @Username varchar(120) = @Subject;

	SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
	BEGIN TRANSACTION;

		DECLARE
			@UserId int,
			@RoleId int,
			@LanguageId int,
			@IsNewUser bit = 1;

		SELECT
			@UserId = U.UserId,
			@RoleId = U.RoleId,
			@LanguageId = U.LanguageId,
			@IsNewUser = 0
		FROM
			SoftadminApi.[User] U
		WHERE
			U.Username = @Username;

		IF @LanguageId IS NULL AND @Locale IS NOT NULL
		BEGIN
			SELECT @LanguageId  = (
				SELECT TOP (1)
					L.LanguageId
				FROM
					SoftadminApi.Language
				L WHERE
					L.CultureCode LIKE @Locale + '%' AND
					L.IsEnabled = 1
				);
		END;

		DECLARE @id int;
		EXEC SoftadminApi.User_InsertUpdate
			@id = @UserId OUTPUT,
			@Password = NULL,
			@UserName = @Username,
			@UserNameFirst = @GivenName,
			@UserNameLast = @FamilyName,
			@UserEmail = @Email,
			@RoleId = @RoleId,
			@LanguageId = @LanguageId,
			@IsEnabled = 1;

		--IF @IsNewUser = 1
		--BEGIN
		--	-- Assign default permissions to the new user here,
		--	-- or try to extract their permissions from the claims.
		--END

	COMMIT TRANSACTION;

	-- Return the user id that should be signed in.
	SELECT @UserId AS UserId;
END;