Sign-in and identity pass-through

The service answers two separate questions: who is making the request (the sign-in method), and as whom the statement runs in SQL Server (identity pass-through, configured per alias). This page explains both and the combinations that are allowed; the detailed rules are in HTTP: sign-in (reference) and Identity pass-through and database identities (reference).

• Sign-in with a service key, a Windows account, a SQL Server login, or a personal key.
• A second factor according to a sign-in policy, by method, area, and network.
• Statements run under the alias's login, the person's own login, or an assigned shared login; the person remains visible in the session context.

Building blocks: Persons with Service and Impersonate, the DenyImpersonate deny list with network conditions and TrustedProxies, the SQL Server login over HTTP Basic through its own connection, and the database identities from the administration store.

Guiding principle: toward the database, the service behaves like a direct connection of the person. This comes down to three properties you can verify. When a statement runs as the person, USER_NAME(), SUSER_SNAME(), and CURRENT_USER return the person—in column defaults, triggers, and views. SQL Server checks the person's permissions, and a denial comes back as 403, just as a direct connection would be refused. And SQL Server itself verifies a SQL Server login when its connection opens. Where this doesn't apply—service keys, or pass-through set to Service—the database sees the alias's login. To make this work, the service separates two questions:

1. Who is calling? The sign-in method determines that.
2. As whom does the statement run in SQL Server? The alias's configuration determines that.

This matters because databases evaluate the login themselves: column defaults fill in a value with USER_NAME() (such as „created by“), and triggers, views, and procedures read USER_NAME(), CURRENT_USER, SYSTEM_USER, or HOST_NAME(). With Service, all of them see the alias's login: a „modified by“ column names the service account, and a view that evaluates CURRENT_USER does so for the service account instead of the person.

Who is calling—the sign-in methods:

The service doesn't accept an identity that an upstream proxy passes along in a header: as long as the service can also be reached without that proxy, anyone could set the header themselves.

As whom the statement runs—pass-through, configured per alias:

How the alias's Persons setting works for each method:

A database identity that a person chooses takes precedence over both settings (see database identities).

The person stays visible. When a person registered in administration signs in with a personal key or chooses a database identity, the service writes the person to the session context as read-only before every switch (SESSION_CONTEXT(N'wkl.person'), plus wkl.key and wkl.role)—under Service as well. When someone signs in with a Windows account or a SQL Server login, wkl.person stays empty, even if the account is assigned to a person; there, SQL Server identifies the person through SUSER_SNAME(), provided the statement runs as that person. There's no separate setting for this; the rules are under Row-level restriction.

• Rights: rules and scope
• Pass people's identities through to SQL Server
• Set up sign-in with the Windows account
• Set up sign-in with a SQL Server login