Identity pass-through and database identities (reference)

Rules and responses when the service runs statements under the login of a person (Impersonate) or under an assigned shared login. The model is explained in Sign-in and identity pass-through.

"Databases": [
  { "Name": "fachdaten", "Provider": "sqlserver", "ConnectionString": "…",
    "Persons": "Impersonate",       // Service | Impersonate; default Service
    "DenyImpersonate": [
      { "Logins": [ "sa", "CORP\\Database-Operations" ] },                 // never
      { "Roles": [ "sysadmin" ], "ExceptFrom": [ "192.168.0.0/16" ] }    // sysadmins only from the corporate network
    ] }
],
"Security": {
  "AuthMode": "ApiKey, Windows",
  "TrustedProxies": [ "10.0.0.5" ]  // X-Forwarded-For is trusted only from here
}

For this, the alias login needs the following in SQL Server:

GRANT IMPERSONATE ANY LOGIN TO [<alias login>];
-- also possible, see below: DENY IMPERSONATE ON LOGIN::[sa] TO [<alias login>];

The responses to a rejection don't name a rule; which entry matched, for whom and from which origin is written as a warning to the log under the correlation ID.

In many installations, people have no permissions of their own in the database. They work through a shared login per department, such as vertrieb or marketing, whose password everyone knows. The customer decides who belongs to which login. The service maps this in the administration store: each person signs in as themselves, and the service switches to the shared login for them. Nobody needs the password of the shared login for this.

Two sets of the administration API:

Identity and assignment each have an Enabled switch. A person also has a default (DefaultRole in Persons).

Which identity applies—per request, as soon as the alias is known:

An assignment matches via the person to whom the sign-in belongs (via the key or their own Windows account), via the key itself, or via the Windows account and each of its groups. Names in X-Wkl-Role and X-Wkl-Roles are percent-encoded UTF-8 (Au%C3%9Fendienst); the list is comma-separated.

Check on save. The service saves an enabled identity of kind Login only after a trial switch: it opens a connection of the data source, switches to the login and immediately reverts the switch. It rejects with 422 and a message for the administrator if the login is missing (or is a Windows group), if an entry of DenyImpersonate names it—on save, every entry counts, including one with a network condition –, if it's a member of a server role named there, if it has no access to the data source's database, or if SQL Server refuses the switch (for example because IMPERSONATE is missing). For this, the data source login needs the following in SQL Server:

GRANT IMPERSONATE ON LOGIN::[vertrieb] TO [<DATA SOURCE login>];   -- per shared login

The customer creates the shared logins themselves and their permissions in SQL Server; the service doesn't manage them. A shared login that people share via Basic with a common password is not recommended: the service then can't tell the people apart.

Clients. The ODBC driver for OData (Wkl.Data.OData.Odbc) chooses with the ROLE= keyword or—where the application allows a dialog—with a drop-down list that shows the list from X-Wkl-Roles. Access and DAO share a connection among all links of a process with the same DATABASE value, even if their ROLE= differs: there, the identity of the first connection applies to all these links. A second identity in the same Access session needs its own DATABASE value in its connection string; the rule is described in the driver documentation (connection keywords).

• Pass people's identities through to SQL Server
• “Set up database identities” (documentation of the administration app, administrator guide)