SSONorvia SSO Guide
Part 2 · User provisioning

Let Norvia Backoffice create Microsoft Entra users

This guide explains the database, Microsoft Graph, and Backoffice changes needed to give each tenant user one Entra identity that works across authorised Norvia products.

Simple objective

What are we building?

When an administrator creates a tenant user, Norvia Backoffice will also create that person's Microsoft Entra account. The database will remember the link between the two users.

Create the normal Norvia user in UserExtended.
Create the Microsoft Entra account through Microsoft Graph.
Save the Entra Object ID in ExternalIdentityMapping.
Use UserExtendedRoleMapping to decide which products and roles the user can access.
Why? Entra confirms who the person is. Norvia's role records decide what that person can use. If another product is assigned later, the same Entra identity must be reused.
Scope

Change only what Part 2 needs

Change these

  • Product Create and Edit
  • Tenant Administrator Create
  • Tenant Administrator Edit
  • Microsoft Graph calls used by those flows

Do not change these

  • Tenant Management
  • Unrelated screens or modules
  • Existing ProductHash behaviour
  • Existing product-role mapping logic
Why? Small, focused changes are easier to test and less likely to break working Backoffice features.
Runnable SQL

1. Prepare the Azure SQL database

Run this complete script in norvia-labs-database-dev. It checks whether each item already exists, so it is safe to run again.

  1. Open Azure Portal → SQL databases.
  2. Select norvia-labs-database-dev → Query editor and sign in.
  3. Select New query.
  4. Copy the complete SQL below, paste it into the editor, and select Run.
  5. Confirm there are no errors, then run the verification query.
Why? Products need Entra application settings. Users need separate personal and login emails. The mapping table stores the permanent link to the Entra user.
/* Norvia SSO Part 2 - database preparation
   Target: norvia-labs-database-dev
   Safe to run more than once. */

SET NOCOUNT ON;
SET XACT_ABORT ON;

BEGIN TRY
    BEGIN TRANSACTION;

    IF COL_LENGTH('dbo.NorviaProducts', 'SSOTenantID') IS NULL
    BEGIN
        ALTER TABLE dbo.NorviaProducts
        ADD SSOTenantID NVARCHAR(100) NULL;
    END;

    IF COL_LENGTH('dbo.NorviaProducts', 'SSOAppID') IS NULL
    BEGIN
        ALTER TABLE dbo.NorviaProducts
        ADD SSOAppID NVARCHAR(100) NULL;
    END;

    IF COL_LENGTH('dbo.NorviaProducts', 'SSOAppSecretReference') IS NULL
    BEGIN
        ALTER TABLE dbo.NorviaProducts
        ADD SSOAppSecretReference NVARCHAR(500) NULL;
    END;

    IF COL_LENGTH('dbo.UserExtended', 'PersonalEmail') IS NULL
    BEGIN
        ALTER TABLE dbo.UserExtended
        ADD PersonalEmail NVARCHAR(255) NULL;
    END;

    UPDATE dbo.UserExtended
       SET PersonalEmail = Email
     WHERE PersonalEmail IS NULL
       AND Email IS NOT NULL;

    IF OBJECT_ID('dbo.ExternalIdentityMapping', 'U') IS NULL
    BEGIN
        CREATE TABLE dbo.ExternalIdentityMapping
        (
            ID BIGINT IDENTITY(1,1) NOT NULL
                CONSTRAINT PK_ExternalIdentityMapping PRIMARY KEY,
            UserExtendedID BIGINT NOT NULL,
            IdentityProvider NVARCHAR(50) NOT NULL,
            ExternalTenantID NVARCHAR(100) NOT NULL,
            ExternalUserID NVARCHAR(100) NOT NULL,
            CreatedOn DATETIME2 NOT NULL
                CONSTRAINT DF_ExternalIdentityMapping_CreatedOn
                DEFAULT SYSUTCDATETIME(),

            CONSTRAINT FK_ExternalIdentityMapping_UserExtended
                FOREIGN KEY (UserExtendedID)
                REFERENCES dbo.UserExtended(Id),

            CONSTRAINT UQ_ExternalIdentityMapping_ExternalIdentity
                UNIQUE (IdentityProvider, ExternalTenantID, ExternalUserID)
        );
    END;

    COMMIT TRANSACTION;
    SELECT 'Norvia SSO Part 2 database setup completed.' AS Result;
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
    THROW;
END CATCH;

Verification query

Run this after the setup script. Every row should show OK.

SELECT 'NorviaProducts.SSOTenantID' AS Item,
       CASE WHEN COL_LENGTH('dbo.NorviaProducts','SSOTenantID') IS NOT NULL THEN 'OK' ELSE 'MISSING' END AS Status
UNION ALL
SELECT 'NorviaProducts.SSOAppID',
       CASE WHEN COL_LENGTH('dbo.NorviaProducts','SSOAppID') IS NOT NULL THEN 'OK' ELSE 'MISSING' END
UNION ALL
SELECT 'NorviaProducts.SSOAppSecretReference',
       CASE WHEN COL_LENGTH('dbo.NorviaProducts','SSOAppSecretReference') IS NOT NULL THEN 'OK' ELSE 'MISSING' END
UNION ALL
SELECT 'UserExtended.PersonalEmail',
       CASE WHEN COL_LENGTH('dbo.UserExtended','PersonalEmail') IS NOT NULL THEN 'OK' ELSE 'MISSING' END
UNION ALL
SELECT 'ExternalIdentityMapping table',
       CASE WHEN OBJECT_ID('dbo.ExternalIdentityMapping','U') IS NOT NULL THEN 'OK' ELSE 'MISSING' END;
Do not store a client secret here: SSOAppSecretReference contains only the name or location of a secure App Service setting.
Microsoft Graph

2. Allow Backoffice to manage Entra users

2A

Register Norvia Backoffice

  1. Open Entra ID → App registrations → New registration.
  2. Name it NorviaBackoffice.
  3. Choose single tenant and leave Redirect URI empty.
  4. Register it and copy its Tenant ID and Client ID.
  5. Create a client secret and store it securely.
Why? Backoffice needs its own identity before Graph will accept server-to-server requests.
2B

Grant the permission

  1. Open API permissions → Add a permission.
  2. Select Microsoft Graph → Application permissions.
  3. Add User.ReadWrite.All.
  4. Select Grant admin consent.
Why? This permission allows Backoffice to create and update users without an interactive sign-in. It is powerful, so only trusted administrators should trigger provisioning.
App Service settings

3. Add the Graph settings

Open App Services → Norvia Backoffice → Settings → Environment variables. Add these settings and select Apply.

EntraProvisioning__TenantId     = <tenant-guid>
EntraProvisioning__ClientId     = <backoffice-client-guid>
EntraProvisioning__ClientSecret = <secret-value>
EntraProvisioning__Domain       = <tenant>.onmicrosoft.com
Why? Environment variables keep credentials out of source code. ASP.NET Core reads __ as a configuration separator.
Backoffice changes

4. Update the existing flows

FlowWhat to addWhy
Product Create/EditAdd optional SSO Tenant ID, Client ID, and Client Secret Reference fields.Each product needs to identify its Entra registration without storing a secret.
Tenant Admin CreateUse Personal Email for contact. Generate a read-only Login Email, create the Entra user, then save both emails and the mapping.The controlled Entra username is separate from a personal contact address.
Tenant Admin EditKeep Login Email unchanged. Sync names and optional password resets using the mapped Object ID.A stable login prevents broken access and duplicate identities.
Existing userIf no mapping exists, offer an explicit provisioning action in the existing Edit flow.Old users need a safe migration path, but unrelated edits must not create accounts.
Login email

5. Generate a unique username

Use first name, surname, and the Entra domain—for example john.smith@yourtenant.onmicrosoft.com.

  • Check both UserExtended.Email and Entra ID.
  • If used, try john.smith2, then john.smith3, until one is free.
  • Check again when the form is submitted.
  • Never regenerate the login for an already-provisioned user.
Why? Two people can have the same name. Checking both systems prevents duplicate usernames.
Create user flow

6. Provision in this order

Run existing validation and Entra password checks.
Generate and recheck the unique login email.
Create the Entra user through Graph.
Save login email in Email and contact email in PersonalEmail.
Save the Entra Object ID in ExternalIdentityMapping.
Use existing role mapping and show the generated login—but never show the password again.
Why this order? The mapping needs values returned by Entra and the saved Norvia user ID.
Safe updates

7. Keep one identity per person

Name change

Update local and Entra names, but keep the login unchanged.

Password change

Reset only when a valid new password is entered.

Another product

Add the new role mapping and reuse the existing Entra account.

Access removed

Update local roles or active status; do not automatically delete the Entra account.

Errors and testing

8. Recover safely and confirm the result

  • Explain missing Graph settings
  • Handle Graph authentication errors
  • Handle duplicate logins
  • Explain password-policy failures
  • Handle Entra create/update/reset failures
  • Handle SQL failures
  • Never expose passwords, secrets, or tokens
  • If Entra succeeds but SQL fails, securely log the Object ID for recovery
SQL verification shows five OK results
Product SSO settings save and reload
Unique login generation works
Entra user and mapping are created
Personal and login emails save correctly
A second product reuses the identity
Name and password updates work
Existing users can be explicitly provisioned

← Return to the SSO overview