What database schema links an Entra ID (or other SSO) identity to an existing local user table?

howto · identity · entra-id · database · sso · oauth · schema

Add a separate external-logins table - one row per (provider, tenant, subject) triple with a foreign key to the local user - instead of touching the existing user/auth table at all. Key it on the ID token's `oid` plus `tid` - Microsoft's guidance for sharing an identity across services, and what lets a tenant be revoked; cache claims and refresh tokens there too, never in the local user row. First sign-in looks the pair up in that table; on no match it links by email only if the app opts in and the token carries `xms_edov: true`, else creates the user and the login in one transaction.

A linking table, not a merge

An app that already has its own users table - rows created by a signup form, a password hash, maybe an existing sessions or API-key scheme - doesn’t replace any of that to add Entra ID sign-in. It adds one new table that maps an external identity to a row in the table that already exists:

SCHEMA = """
create table external_logins (
  id            integer primary key,
  user_id       integer not null references users(id),
  provider      text not null,        -- 'entra-id'
  tenant_id     text not null,        -- the ID token's tid
  subject       text not null,        -- the ID token's oid
  email         text,                 -- cached, not authoritative
  display_name  text,
  refresh_token text,                 -- only when something needs it, encrypted
  created_at    timestamp not null default current_timestamp,
  updated_at    timestamp not null default current_timestamp,
  unique (provider, tenant_id, subject)
);
"""

compiled and run by the build

Show the whole file

# An existing local users table, the external_logins linking table added
# beside it, and the first-sign-in flow, against an in-memory SQLite
# database, standard library only. main() asserts what the entry claims:
# the linking table refuses a second row for the same (provider,
# tenant_id, subject) and a row that points at no user; a known pair signs
# in as the linked user and writes nothing; a token email links to an
# existing user only when linking is on and the token carries
# xms_edov: true; every other first sign-in creates the user and the login
# in one transaction, and a failure between the two leaves neither; and
# no existing users row is ever modified.
import sqlite3

# The table the app already had, written before anyone thought of SSO.
EXISTING = """
create table users (
  id            integer primary key,
  email         text unique,
  password_hash text
);
"""

SCHEMA = """
create table external_logins (
  id            integer primary key,
  user_id       integer not null references users(id),
  provider      text not null,        -- 'entra-id'
  tenant_id     text not null,        -- the ID token's tid
  subject       text not null,        -- the ID token's oid
  email         text,                 -- cached, not authoritative
  display_name  text,
  refresh_token text,                 -- only when something needs it, encrypted
  created_at    timestamp not null default current_timestamp,
  updated_at    timestamp not null default current_timestamp,
  unique (provider, tenant_id, subject)
);
"""


def sign_in(con: sqlite3.Connection, claims: dict, link_by_verified_email: bool) -> int:
    """Return the local user id for a validated ID token's claims."""
    tid, oid = claims["tid"], claims["oid"]

    # 1. The pair is the key: a known one is the linked user, and nothing is written.
    row = con.execute(
        "select user_id from external_logins "
        "where provider = 'entra-id' and tenant_id = ? and subject = ?",
        (tid, oid),
    ).fetchone()
    if row:
        return row[0]

    with con:  # one transaction: commits at the end, rolls back on any error
        user_id = None
        # 2. Link on email only when the app allows it and Entra ID says the
        #    email's domain owner is verified (there is no email_verified claim).
        email = claims.get("email")
        if link_by_verified_email and email and claims.get("xms_edov") is True:
            match = con.execute("select id from users where email = ?", (email,)).fetchone()
            if match:
                user_id = match[0]

        # 3. Neither matched: a new person. The user row needs no password.
        if user_id is None:
            user_id = con.execute("insert into users (email) values (null)").lastrowid

        con.execute(
            "insert into external_logins "
            "(user_id, provider, tenant_id, subject, email, display_name) "
            "values (?, 'entra-id', ?, ?, ?, ?)",
            (user_id, tid, oid, email, claims.get("name")),
        )
    return user_id


def count(con: sqlite3.Connection, table: str) -> int:
    return con.execute(f"select count(*) from {table}").fetchone()[0]


def refused(con: sqlite3.Connection, sql: str, params: tuple) -> bool:
    try:
        with con:
            con.execute(sql, params)
    except sqlite3.IntegrityError:
        return True
    return False


def main() -> None:
    con = sqlite3.connect(":memory:")
    con.execute("pragma foreign_keys = on")
    con.executescript(EXISTING)
    con.execute("insert into users (email, password_hash) values ('ann@contoso.com', 'h1')")
    con.execute("insert into users (email, password_hash) values ('bo@contoso.com', 'h2')")
    con.commit()
    con.executescript(SCHEMA)
    before = con.execute("select * from users order by id").fetchall()

    tenant = "72f988bf-0000-0000-0000-000000000001"
    insert = (
        "insert into external_logins (user_id, provider, tenant_id, subject) "
        "values (?, 'entra-id', ?, ?)"
    )

    # The linking table refuses an identity that points at no local user.
    assert refused(con, insert, (999, tenant, "oid-orphan"))

    # A brand-new identity: exactly one user and one login, nothing else touched.
    new = {"tid": tenant, "oid": "oid-new", "email": "new@contoso.com", "xms_edov": True}
    new_id = sign_in(con, new, link_by_verified_email=True)
    assert count(con, "users") == 3 and count(con, "external_logins") == 1
    assert con.execute("select * from users where id <= 2 order by id").fetchall() == before

    # Signing in again finds the pair and writes nothing.
    assert sign_in(con, new, link_by_verified_email=True) == new_id
    assert count(con, "users") == 3 and count(con, "external_logins") == 1

    # The unique key refuses a second row for the same (provider, tenant_id, subject)...
    assert refused(con, insert, (1, tenant, "oid-new"))
    # ...while the same oid under another tenant is another identity.
    assert not refused(con, insert, (1, "another-tenant", "oid-new"))
    con.execute("delete from external_logins where tenant_id = 'another-tenant'")
    con.commit()

    # Linking on: a verified email that matches links the existing user, once.
    ann = {"tid": tenant, "oid": "oid-ann", "email": "ann@contoso.com", "xms_edov": True}
    assert sign_in(con, ann, link_by_verified_email=True) == 1
    assert count(con, "users") == 3 and count(con, "external_logins") == 2
    assert sign_in(con, ann, link_by_verified_email=True) == 1
    assert count(con, "external_logins") == 2

    # The same email without xms_edov (or with it false) does not link: a new user.
    for i, edov in enumerate([None, False]):
        claims = {"tid": tenant, "oid": f"oid-bo-{i}", "email": "bo@contoso.com"}
        if edov is not None:
            claims["xms_edov"] = edov
        assert sign_in(con, claims, link_by_verified_email=True) not in (1, 2)

    # Linking off: even a verified, matching email creates a new user.
    bo = {"tid": tenant, "oid": "oid-bo-verified", "email": "bo@contoso.com", "xms_edov": True}
    assert sign_in(con, bo, link_by_verified_email=False) != 2
    users_now = count(con, "users")

    # A failure between the two inserts leaves neither: the login's subject is
    # missing, so its insert fails and the user insert before it rolls back.
    try:
        sign_in(con, {"tid": tenant, "oid": None}, link_by_verified_email=False)
        raise AssertionError("the login insert should have failed")
    except sqlite3.IntegrityError:
        pass
    assert count(con, "users") == users_now

    # The rows the old auth scheme wrote are exactly as they were.
    assert con.execute("select * from users where id <= 2 order by id").fetchall() == before

    # A whole tenant is revoked in one query, because tenant_id is a column.
    removed = con.execute("delete from external_logins where tenant_id = ?", (tenant,)).rowcount
    assert removed == count(con, "users") - 2 + 1  # every new user's login plus Ann's link
    print("ok")


if __name__ == "__main__":
    main()

The example runs it on SQLite, which is why the types read integer and timestamp and the default is current_timestamp; on PostgreSQL the same table takes BIGINT GENERATED ALWAYS AS IDENTITY for id, TIMESTAMPTZ for the two dates, and for user_id whatever type the existing users.id already has (BIGINT in the diagram below), because a foreign key has to match the key it references. The constraint that matters is the last line: the database itself refuses a second row for the same (provider, tenant_id, subject), so two sign-ins racing for the same new identity cannot both create a login - the loser’s insert fails with a unique violation, which the app catches and answers by looking the pair up again, finding the winner’s row - while the same oid under another tid is a different row. The foreign key on user_id refuses a login that points at no user.

The same shape drawn as the two tables it relates, one user to many external logins, unique together on provider, tenant_id and subject:

erDiagram
    users ||--o{ external_logins : has
    external_logins {
        bigint user_id FK
        text provider UK
        text tenant_id UK
        text subject UK
    }

This is the same shape ASP.NET Core Identity ships out of the box: AspNetUserLogins holds (LoginProvider, ProviderKey) -> UserId, a composite key separate from AspNetUsers, so one local user can carry a password and one or more external logins, and AddLoginAsync on an already-signed-in user attaches a new provider without ever writing to the password columns. Rolling a schema by hand instead of using Identity follows the same rule: the existing username/password or JWT-issuing table stays exactly as it was, and everything Entra ID adds - the provider, the tenant, the external subject, whatever is cached about the session - lives in the new table, one row per external identity, referencing the local user by its existing primary key. Nothing about the local table’s shape has to anticipate SSO in advance; the linking table is what makes it additive.

Why the key includes `tenant_id`, even though `oid` is already a GUID

Entra ID’s ID token carries oid - the user’s object ID - which is stable for that user in that tenant across every sign-in. It is a GUID, and Microsoft’s own claims reference states that it is never reassigned to a different user once issued, so a single-tenant app could in principle key on oid alone without ever conflating two people: unlike email or preferred_username, it isn’t something a tenant admin can reassign. What still earns tenant_id a place in the key is two other concerns. First, this table exists to let the identity be recognized consistently - by this app’s own other services, or by a second linking table added later - and Microsoft recommends carrying oid and tid together for exactly that case, since every service sharing the data ends up agreeing on the same pair. Second, a multi-tenant app (one registered with common or organizations as its authority, letting users from more than one organization sign in) has to check the token’s tid against an allow-list of authorized tenants before it trusts the token at all; having tenant_id already sitting in the external_logins row, rather than re-derived from a cached claim, is what lets a whole tenant be revoked - disable every row for that tenant_id - in one query. email and preferred_username are not usable as that key at all: Entra ID lets a tenant admin reassign or edit both, so a value that identified one person last month can identify someone else today - fine to cache for display, never safe to look a user up by as if it were permanent.

What to store beyond the id

Whether anything beyond (provider, tenant_id, subject) belongs in the row depends on what the app does after sign-in:

Cached claims (email, display_name, the tenant’s display name) save a Graph call on every page load and are fine to store, as long as the app treats them as a cache - refreshed from the next ID token, not read back to authorize anything - because Entra ID, not this table, is the source of truth for who owns an email address today.
A refresh token is only worth persisting when the app needs to call an API after the interactive session ends - a background job acting on the user’s behalf, a long-lived integration
- because MSAL’s own token cache already handles silent renewal for the lifetime of a browser session or a native app’s process (identity/entra-id-client-sign-in); storing one when nothing needs it is a stored secret with no consumer. A stored refresh token has to be encrypted at rest (a KMS-backed column, not plaintext), because it is as good as the password the app doesn’t otherwise have for this user.
An access token is never worth storing. It is short-lived by design (typically an hour) and Entra ID reissues it from the refresh token on demand; a stored access token is a stale credential the moment it’s written, not a cache.

The first time a given (tenant_id, subject) pair is seen, there is no row in external_logins yet, and the app has to decide whether this is a new person or an existing one signing in through a new door. The three steps below, drawn as a decision tree:

flowchart TD
    T[ID token: tid, oid, email, xms_edov] --> L{external_logins row for tid + oid?}
    L -- yes --> S1([sign in as the linked user])
    L -- no --> V{linking by verified email on?}
    V -- no --> N[insert users + external_logins rows, one transaction]
    V -- yes --> M{verified token email matches a users row?}
    M -- yes --> K[insert external_logins row once] --> S2([sign in as that user])
    M -- no --> N

Look up the pair first. An unmatched pair is the only case that reaches steps 2-3; every later sign-in for the same person matches here and goes straight to the linked user.
Fall back to a verified claim, deliberately, or not at all. An app migrating existing password users to Entra ID often wants “the same email now signs in with Entra ID” to just work, which means matching email (only the token’s email, and only when the token also carries xms_edov: true - Entra ID’s own claim for “this email’s domain owner is verified”, requested as an optional claim alongside email itself, since Entra ID has no email_verified claim of the kind other OIDC providers emit) against the existing users table and creating the external_logins row for that match, once, the first time. This step is the one an app can also skip entirely - require the user to sign in with their existing password once and add the Entra ID login from an authenticated “connect account” action - which is the only fully safe option when the existing table’s emails were never verified to begin with, because auto-linking on an unverified email lets anyone who can get an Entra ID account with a matching address take over an existing local account.
Only create a new local user when neither matches. A pair with no linking-table row and no claim-based match is a genuinely new person: insert the local users row (with whatever that table already requires - it doesn’t need a password when the app allows external-only accounts) and the external_logins row in the same transaction, so a crash between the two never leaves an external identity pointing at nothing.

The three steps as code, run against the schema above; it takes the claims of an ID token the app has already validated (signature, issuer, audience, the tenant allow-list), because nothing here should run on a token that has not passed those checks:

def sign_in(con: sqlite3.Connection, claims: dict, link_by_verified_email: bool) -> int:
    """Return the local user id for a validated ID token's claims."""
    tid, oid = claims["tid"], claims["oid"]

    # 1. The pair is the key: a known one is the linked user, and nothing is written.
    row = con.execute(
        "select user_id from external_logins "
        "where provider = 'entra-id' and tenant_id = ? and subject = ?",
        (tid, oid),
    ).fetchone()
    if row:
        return row[0]

    with con:  # one transaction: commits at the end, rolls back on any error
        user_id = None
        # 2. Link on email only when the app allows it and Entra ID says the
        #    email's domain owner is verified (there is no email_verified claim).
        email = claims.get("email")
        if link_by_verified_email and email and claims.get("xms_edov") is True:
            match = con.execute("select id from users where email = ?", (email,)).fetchone()
            if match:
                user_id = match[0]

        # 3. Neither matched: a new person. The user row needs no password.
        if user_id is None:
            user_id = con.execute("insert into users (email) values (null)").lastrowid

        con.execute(
            "insert into external_logins "
            "(user_id, provider, tenant_id, subject, email, display_name) "
            "values (?, 'entra-id', ?, ?, ?, ?)",
            (user_id, tid, oid, email, claims.get("name")),
        )
    return user_id

compiled and run by the build

Show the whole file

# An existing local users table, the external_logins linking table added
# beside it, and the first-sign-in flow, against an in-memory SQLite
# database, standard library only. main() asserts what the entry claims:
# the linking table refuses a second row for the same (provider,
# tenant_id, subject) and a row that points at no user; a known pair signs
# in as the linked user and writes nothing; a token email links to an
# existing user only when linking is on and the token carries
# xms_edov: true; every other first sign-in creates the user and the login
# in one transaction, and a failure between the two leaves neither; and
# no existing users row is ever modified.
import sqlite3

# The table the app already had, written before anyone thought of SSO.
EXISTING = """
create table users (
  id            integer primary key,
  email         text unique,
  password_hash text
);
"""

SCHEMA = """
create table external_logins (
  id            integer primary key,
  user_id       integer not null references users(id),
  provider      text not null,        -- 'entra-id'
  tenant_id     text not null,        -- the ID token's tid
  subject       text not null,        -- the ID token's oid
  email         text,                 -- cached, not authoritative
  display_name  text,
  refresh_token text,                 -- only when something needs it, encrypted
  created_at    timestamp not null default current_timestamp,
  updated_at    timestamp not null default current_timestamp,
  unique (provider, tenant_id, subject)
);
"""


def sign_in(con: sqlite3.Connection, claims: dict, link_by_verified_email: bool) -> int:
    """Return the local user id for a validated ID token's claims."""
    tid, oid = claims["tid"], claims["oid"]

    # 1. The pair is the key: a known one is the linked user, and nothing is written.
    row = con.execute(
        "select user_id from external_logins "
        "where provider = 'entra-id' and tenant_id = ? and subject = ?",
        (tid, oid),
    ).fetchone()
    if row:
        return row[0]

    with con:  # one transaction: commits at the end, rolls back on any error
        user_id = None
        # 2. Link on email only when the app allows it and Entra ID says the
        #    email's domain owner is verified (there is no email_verified claim).
        email = claims.get("email")
        if link_by_verified_email and email and claims.get("xms_edov") is True:
            match = con.execute("select id from users where email = ?", (email,)).fetchone()
            if match:
                user_id = match[0]

        # 3. Neither matched: a new person. The user row needs no password.
        if user_id is None:
            user_id = con.execute("insert into users (email) values (null)").lastrowid

        con.execute(
            "insert into external_logins "
            "(user_id, provider, tenant_id, subject, email, display_name) "
            "values (?, 'entra-id', ?, ?, ?, ?)",
            (user_id, tid, oid, email, claims.get("name")),
        )
    return user_id


def count(con: sqlite3.Connection, table: str) -> int:
    return con.execute(f"select count(*) from {table}").fetchone()[0]


def refused(con: sqlite3.Connection, sql: str, params: tuple) -> bool:
    try:
        with con:
            con.execute(sql, params)
    except sqlite3.IntegrityError:
        return True
    return False


def main() -> None:
    con = sqlite3.connect(":memory:")
    con.execute("pragma foreign_keys = on")
    con.executescript(EXISTING)
    con.execute("insert into users (email, password_hash) values ('ann@contoso.com', 'h1')")
    con.execute("insert into users (email, password_hash) values ('bo@contoso.com', 'h2')")
    con.commit()
    con.executescript(SCHEMA)
    before = con.execute("select * from users order by id").fetchall()

    tenant = "72f988bf-0000-0000-0000-000000000001"
    insert = (
        "insert into external_logins (user_id, provider, tenant_id, subject) "
        "values (?, 'entra-id', ?, ?)"
    )

    # The linking table refuses an identity that points at no local user.
    assert refused(con, insert, (999, tenant, "oid-orphan"))

    # A brand-new identity: exactly one user and one login, nothing else touched.
    new = {"tid": tenant, "oid": "oid-new", "email": "new@contoso.com", "xms_edov": True}
    new_id = sign_in(con, new, link_by_verified_email=True)
    assert count(con, "users") == 3 and count(con, "external_logins") == 1
    assert con.execute("select * from users where id <= 2 order by id").fetchall() == before

    # Signing in again finds the pair and writes nothing.
    assert sign_in(con, new, link_by_verified_email=True) == new_id
    assert count(con, "users") == 3 and count(con, "external_logins") == 1

    # The unique key refuses a second row for the same (provider, tenant_id, subject)...
    assert refused(con, insert, (1, tenant, "oid-new"))
    # ...while the same oid under another tenant is another identity.
    assert not refused(con, insert, (1, "another-tenant", "oid-new"))
    con.execute("delete from external_logins where tenant_id = 'another-tenant'")
    con.commit()

    # Linking on: a verified email that matches links the existing user, once.
    ann = {"tid": tenant, "oid": "oid-ann", "email": "ann@contoso.com", "xms_edov": True}
    assert sign_in(con, ann, link_by_verified_email=True) == 1
    assert count(con, "users") == 3 and count(con, "external_logins") == 2
    assert sign_in(con, ann, link_by_verified_email=True) == 1
    assert count(con, "external_logins") == 2

    # The same email without xms_edov (or with it false) does not link: a new user.
    for i, edov in enumerate([None, False]):
        claims = {"tid": tenant, "oid": f"oid-bo-{i}", "email": "bo@contoso.com"}
        if edov is not None:
            claims["xms_edov"] = edov
        assert sign_in(con, claims, link_by_verified_email=True) not in (1, 2)

    # Linking off: even a verified, matching email creates a new user.
    bo = {"tid": tenant, "oid": "oid-bo-verified", "email": "bo@contoso.com", "xms_edov": True}
    assert sign_in(con, bo, link_by_verified_email=False) != 2
    users_now = count(con, "users")

    # A failure between the two inserts leaves neither: the login's subject is
    # missing, so its insert fails and the user insert before it rolls back.
    try:
        sign_in(con, {"tid": tenant, "oid": None}, link_by_verified_email=False)
        raise AssertionError("the login insert should have failed")
    except sqlite3.IntegrityError:
        pass
    assert count(con, "users") == users_now

    # The rows the old auth scheme wrote are exactly as they were.
    assert con.execute("select * from users where id <= 2 order by id").fetchall() == before

    # A whole tenant is revoked in one query, because tenant_id is a column.
    removed = con.execute("delete from external_logins where tenant_id = ?", (tenant,)).rowcount
    assert removed == count(con, "users") - 2 + 1  # every new user's login plus Ann's link
    print("ok")


if __name__ == "__main__":
    main()

The example asserts each branch: a known pair returns its user and writes no row; a matching email links only when linking is on and xms_edov is true (absent or false makes a new user); and when the login insert fails after the user insert, the transaction rolls both back. Entra ID’s other optional claims about email, verified_primary_email and verified_secondary_email, list addresses from the user’s directory object rather than the email claim; an app can match on those instead, under the same rule that it asks for them deliberately and treats their absence as “not verified”.

The trap: keying on `email` alone, or skipping the linking table

Two shortcuts both look fine until a second identity provider or a renamed mailbox turns up. Using email as the actual foreign key into users - instead of a cached column on a separate linking table - means a tenant admin’s rename of the address silently detaches the account on the next sign-in, and it makes adding a second provider (say, a Google sign-in later) impossible without inventing a second special-cased lookup. Skipping the linking table altogether and adding entra_oid as a nullable column directly on users works only as long as there is exactly one external provider, ever: a second one needs a second nullable column, and “which provider does this row’s identity belong to” stops being answerable from the schema at all. The linking table costs one join on sign-in and buys back both problems for free.

How to verify it worked

Sign in as a brand-new Entra ID identity against an app whose users table already has rows from the old auth scheme: confirm exactly one new row lands in external_logins, referencing exactly one row in users, and that no existing users row is modified. Sign in a second time with the same account and confirm no new row is inserted anywhere - the lookup in step 1 above should be the only thing that runs. If email-based linking (step 2) is in place, create a local password account, then sign in with an Entra ID identity carrying the same, verified email, and confirm it links to the existing user rather than creating a duplicate; then repeat with an unverified email claim and confirm it does not link automatically.

When this does not apply

An app with no existing user table - Entra ID is the only way in, from day one - has nothing to link against: the external-logins table (or just users keyed directly on (tenant_id, subject)) is the only identity table it needs, and the whole “don’t disturb the existing scheme” concern here doesn’t arise. And a resource server that only ever validates bearer tokens on API calls, with no session or local account of its own, needs no linking table at all - identity/validate-entra-id-access-tokens covers that case, where the token’s claims are read per-request and nothing is persisted.

Open in the graph

Source: https://learn.microsoft.com/en-us/entra/identity-platform/id-token-claims-reference

History: 4 changes

Oct 5, 2026 Short answer changed; body changed; example changed
Sep 22, 2026 Body changed
Sep 18, 2026 Made public
Sep 18, 2026 Created

What database schema links an Entra ID (or other SSO) identity to an existing local user table?

A linking table, not a merge

Why the key includes tenant_id, even though oid is already a GUID

What to store beyond the id

Linking first sign-in to a local user

The trap: keying on email alone, or skipping the linking table

How to verify it worked

When this does not apply

Related

Why the key includes `tenant_id`, even though `oid` is already a GUID

The trap: keying on `email` alone, or skipping the linking table