HTTP: sign-in (reference)

All sign-in methods of the service with their rules and responses, self-service for your own keys, and the sign-in policy with the second factor. The model behind it is explained in Sign-in and identity pass-through.

Besides the service key, a caller can sign in with their Windows account, via HTTP Negotiate—Kerberos or, where Kerberos isn't available, NTLM. This is intended for people: Access via the ODBC driver, and Excel or Power BI, sign in as the user who runs them. No key or password is stored anywhere, and the audit names the person instead of a key name.

"Security": {
  "AuthMode": "ApiKey, Windows",
  "ApiKeys": [ { "Client": "fachanwendung", "Key": "env:FACHANWENDUNG_SCHLUESSEL" } ],
  "WindowsAccounts": [
    { "Account": "CORP\\BusinessData-Readers", "Client": "fachdaten-lesen" },
    { "Account": "CORP\\BusinessData-Editors", "Client": "fachdaten-pflege" },
    { "Account": "S-1-5-32-545",               "Client": "auskunft" }
  ],
  "Clients": [
    { "Id": "fachanwendung",    "Databases": [ "fachdaten" ], "Resources": [ "*" ],     "Verbs": [ "*" ] },
    { "Id": "fachdaten-lesen",  "Databases": [ "fachdaten" ], "Resources": [ "views" ], "Verbs": [ "GET" ], "ReadOnly": true },
    { "Id": "fachdaten-pflege", "Databases": [ "fachdaten" ], "Resources": [ "*" ],     "Verbs": [ "*" ] },
    { "Id": "auskunft",         "Databases": [ "fachdaten" ], "Resources": [ "views" ], "Verbs": [ "GET" ], "ReadOnly": true }
  ]
}

Without sign-in, the service responds with 401 and WWW-Authenticate: Negotiate; only then does a client sign in with Windows. Every new connection therefore begins with a request without sign-in, which appears in the audit as anonym … UNAUTHENTICATED (401). That's the first step of the method, not an error. After that, the sign-in applies to the whole connection.

Kerberos requires an SPN HTTP/<host-name> for the account under which the service runs; if it's missing, Windows falls back to NTLM.

If you have been reaching SQL Server with a name and password, you sign in to the service with the same login: via HTTP Basic, that is, with name and password in the Authorization header. The service doesn't check the password itself and doesn't store one. It opens a connection to the server of the addressed alias with the name and password, and SQL Server decides, as with a direct connection. All statements of the request then run over connections of this login: permissions, column defaults and triggers see the login itself, including ORIGINAL_LOGIN().

"Databases": [
  { "Name": "fachdaten", "Provider": "sqlserver", "ConnectionString": "…",
    "AllowSqlLogins": true }
],
"Security": {
  "AuthMode": "ApiKey, Windows, SqlLogin",
  "SqlLogins": [
    { "Login": "*",       "Client": "fachdaten-lesen"  },
    { "Login": "pflege1", "Client": "fachdaten-pflege" }
  ],
  "TrustedProxies": [ "10.0.0.5" ]
}

A personal key belongs to a person, not to a service. It requires no domain, no SQL Server password and no infrastructure, and so it works everywhere the service can be reached. People and keys are kept in the administration store; the method needs it and is turned on with PersonalKey:

"Security": { "AuthMode": "ApiKey, PersonalKey" },
"Store": { "Path": "store/dbgwsvc.db", "MasterKey": "env:WKL_GW_MASTER_KEY", "MaxKeyLifetimeDays": 365 }

The key goes into the same header as a service key (X-Api-Key, see ApiKeyHeader). Every client that can send a service key—the ODBC driver with APIKEY/APIKEYENV, a script, a reporting tool—sends a personal one without any change. The prefix distinguishes the two. A personal key also works as the password of HTTP Basic (the name isn't checked; pkuser is recommended, see below): Excel and Power BI store such credentials (authentication kind “Basic”) in their own credential management, whereas a header would have to be written into the query—and therefore into every workbook. The form of the key distinguishes it from the password of a SQL Server login.

<a id=“user-name-pkuser”></a>User name pkuser (convention). If you enter a personal key as the password, enter pkuser as the user name (personal key user). This isn't required: the service recognizes the key by its form (wkl_…), not by the name, and accepts any name. A uniform name still has advantages:

• In the credential managers of Excel, Power BI and Windows, you can see at a glance that this entry holds a personal key and not a SQL Server login.
• On the phone, “user name pkuser, password your key” is enough, without explaining which name is meant.
• Nobody enters the name of another system and then mistakes it for part of the access.

Don't create a pkuser login in SQL Server: the service checks a password that doesn't have the form of a key as a SQL Server login of this name.

“Last used” is collected and written every 60 seconds and on shutdown, not per request: every write to the file would make the service read it again. A crash costs at most that minute of statistics, never an authorization. The service doesn't treat writes that don't change the operating data (usage, keys, people) as a new version and doesn't report them.

Manage people and keys—via the administration API, or on the service's computer with the commands save-person, issue-key, revoke-key and list-keys; they run with the configuration and environment of the service, even while it's running. Invocation, options and return codes are listed under Server commands: people and keys. These commands don't write to the change log (whoever runs them has access to the file anyway); the recovery commands init-admin, reset-totp and remove-sign-in-rule, however, do, with the actor “Konsole (Rechner, Konto)” (console (computer, account)).

A key can be issued so that it's dormant until an administrator activates it for a period—for example a read-only key without a second factor that a person stores in Power BI for presentations to the board. The key itself stays the same across all activations, so Power BI doesn't need to be set up again. Outside an activation it opens nothing, even if it has been left behind in a report, on a laptop or in a mail.

Only an administrator may activate it, not the person themselves; both are recorded in the change log with the actor and the end. The maximum lifetime of the key (MaxKeyLifetimeDays) still applies: after it, a new key is needed.

A person who can sign in another way—with their Windows account or their SQL Server login—issues personal keys to themselves, for example for Excel on a laptop, and revokes them again. Administrators don't need to do anything for this except turn self-service on once.

“WklGatewayClient: VBA class for the data service” (documentation of the administration app) provides OwnKeys, IssueOwnKey and RevokeOwnKey for this.

The sign-in policy defines which sign-in method needs a second factor or is refused for which part of the service from which networks or countries—for example “personal keys from the corporate network without, from outside only with TOTP”, “SQL Server logins never from outside” or “data access only from Germany”. It's kept in the administration store and maintained via the sets SignInRules, SignInRuleItems, SignInRuleExceptions, SignInRuleExceptionItems and Settings of the administration API; without a store, only denials are possible (Gateway:Security:SignInRules in the configuration file).

For individual people or groups. A rule can be restricted to people or Windows groups, or can exclude them—for example “nobody from outside, except John Doe”: { Scope: Data, Effect: Deny, ExceptFrom: [10.0.0.0/8], ExceptPersons: [John Doe] }. The service recognizes a person by their personal key, their own Windows account or their SQL Server login; a group only with a Windows sign-in. A caller who isn't assigned to a person (service key) falls under no such rule and no such exception. explain-sign-in of the administration API shows what a rule means for a person.

By country. The service reads the country of a request from the geolocation database of the sign-in log—in memory, without network access, and only when a rule names countries. A request has no country when no geolocation database is loaded, when it comes from this machine, from one of your own networks (`Gateway:SignInLog:Networks`) or from a private network, or when the database doesn't know the address. Such an unknown country falls within the scope of every rule: “data access only from Germany” refuses it. So exclude your own networks by their addresses, not by a country:

{ "Scope": "Data", "Effect": "Deny", "Label": "Germany only",
  "ExceptCountries": [ "DE" ], "ExceptFrom": [ "10.0.0.0/8" ] }

Important: A country derived from an address is a hint, not proof. A VPN, a proxy or a mobile network moves the origin to another country, and geolocation databases are wrong for some networks. Rules by country add a layer to keys, networks and the second factor; they don't replace any of them.

A rule can also ask for the network operator of the origin, for the program and for the time of day. The three attributes differ in how far you can rely on them:

Like an unknown country, an unknown operator or a missing program falls within the scope of the rule and meets no attribute of an exception. A claimed program is therefore fit for narrowing (“the administration only from WinHTTP” refuses a browser). As the only attribute of an exception, it spares anyone who sends the text; combine it there with people, networks or an end.

Write a time window as [days] [hh:mm-hh:mm]:

• Days Mon, Tue, Wed, Thu, Fri, Sat, Sun as a list (Sat,Sun) or a range (Mon-Fri, also Fri-Mon); without days, every day applies.
• Times with an exclusive end; 24:00 is the end of the day; without times, the whole day applies.
• A window across midnight (Mon-Fri 22:00-06:00) belongs to the day it begins on: Friday 10 p.m. to Saturday 6 a.m. is inside it, Monday 3 a.m. isn't.

“SQL Server logins only Monday to Friday, 6 a.m. to 8 p.m.”:

{ "Method": "SqlLogin", "Effect": "Deny", "Label": "SQL office hours only",
  "ExceptHours": [ "Mon-Fri 06:00-20:00" ] }

“From other network operators only with a second factor, never from Plant 3”:

{ "Scope": "Data", "Effect": "RequireTotp", "Label": "code from other networks",
  "ExceptOperators": [ "AS3320" ], "ExceptFrom": [ "Plant 3" ] }

Your own and private networks have no network operator. Without ExceptFrom, the rule would therefore require the code in the plant as well.

The sign-in event names in Signals the network operator it judged by (operator), the program (client) and, under trust, how reliable each signal is (observed, claimed, verified).

Exceptions with an end. The lists ExceptPersons, ExceptCountries and ExceptFrom each exclude on their own and permanently: ExceptPersons: [John Doe] spares John from every country. An exception in Exceptions, by contrast, spares only when all of its attributes apply at once—for example “John Doe from Thailand until November 2”:

{ "Scope": "Data", "Effect": "Deny", "Label": "Germany only",
  "ExceptCountries": [ "DE" ], "ExceptFrom": [ "10.0.0.0/8" ],
  "Exceptions": [ { "Persons": [ "John Doe" ], "Countries": [ "TH" ],
                    "Until": "2026-11-02T00:00:00Z", "Label": "vacation" } ] }

• At least one attribute or an end. An exception with neither would spare everyone permanently; the startup check and the administration API reject it.
• Not knowing never spares. An unknown country, an unknown origin, an unknown network operator, a missing program or a caller who isn't assigned to a person meets no attribute that asks for it. Without a geolocation database, an exception by country therefore closes instead of opening the rule.
• The end takes effect immediately. The service compares it with its clock on every request; no change is needed. When it notices the end, it writes it once into the change log (Entity exception-lapsed, actor Dienst (Ablauf), before: rule, label and end). An exception that has ended stays in place until it's removed.

Caution when creating a denial. A rule takes effect as soon as it's saved. If you first create a denial for all scopes without exceptions and add the attributes afterward, you lock yourself out at that moment. So create denials with save-sign-in-rule of the administration API, which saves the rule and exceptions in one change; if it has happened, remove-sign-in-rule on the server helps.

Every matching rule counts, a denial before anything else; the order means nothing. Exception: the service judges the exchange of a code and the setup of the factor (/auth/v1/totp, /auth/v1/enrol) only by rules with scope All; a denial only for Data or Admin doesn't prevent an attempt there. And SQL Server checks a SQL Server login before the policy judges: from a denied origin, a wrong password results in 401, a correct one in 403—so an attacker learns whether a password is correct, limited only by the failed-attempt limit. If no rule matches, the rights alone decide. Unauthenticated operation in the development environment isn't judged; a policy that has locked out the administration is repaired with the server commands. A new store starts with the rule “administration only with a second factor” (Scope: Admin, Effect: RequireTotp); it can be changed or deleted like any other. Independently of this, the fixed lower bound Gateway:Store:RequireAdminTotp (default true) requires the second factor for the administration and self-service, for every sign-in method and every origin; not even a key with TotpExempt lifts it. It can only be turned off in the configuration file. The startup check rejects RequireTotp for service keys—a service isn't a person and has no second factor; write Deny instead.

Second factor (TOTP). Time-based codes according to RFC 6238 (6 digits, 30 seconds), as generated by every authenticator app. The factor belongs to a person: a personal key names them; a Windows account and a SQL Server login find them via their own account (WindowsAccount) or their own login (SqlLogin); only the login name is compared, case-insensitively, across all aliases and servers, and a session opened this way applies not only to the alias where the code was exchanged. A group isn't a person; a sign-in that matches no person or two people gets 403 if a second factor is required. An administrator sets it up (enroll-totp), ideally as a one-time link: the page shows the otpauth:// address as a QR code and next to it the key for manual entry. The secret is stored sealed in the store (its own purpose, its own master key change via reseal-store).

Set up at first sign-in. If a person doesn't have a second factor yet but the policy requires one, they may set it up themselves for a limited time—with their first factor, which until then is valid only for this:

POST /auth/v1/enrol[/{alias}]   first factor as when exchanging a code
Body:  { }                      200: { "person", "secret", "uri", "qrPng", "openUntil" }
Body:  { "code": "123456" }     201: as for the exchange - the factor is set up, a session opened

• When. Within totp_enrol_hours (set Settings, otherwise Gateway:Security:TotpEnrolHours, default 24, at most 168, 0 turns off setup at sign-in) after the key with which the request comes was issued; for a person without a key (Windows account), until the time set by init-admin or reset-totp on the server.
• Why a deadline. Until the factor is set up, the key alone stands for the person. Anyone who intercepts it during this time could bind their own authenticator app. The deadline keeps this time short; a key that has waited too long must be reissued.
• Once. The confirmation ends the period and is recorded in the change log; a person who has a factor is no longer offered setup. If someone else set it up first, the rightful holder notices it this way. The administration then sets up a new factor.
• Two steps. Without a code, the service generates a secret and responds with the address, the key for typing in and a QR code as PNG; it's stored sealed only with the first matching code. A new call without a code replaces the generated secret.
• Until then, the service answers every other request of the person with 401 and WWW-Authenticate: Wkl-Totp realm="Wkl.Data.Gateway", enrol="/auth/v1/enrol". The administration application then shows the setup card; the VBA class WklGatewayClient reports gwErrEnrolmentRequired and offers BeginTotpEnrolment/ConfirmTotpEnrolment.

Code for session. A code lives for 30 seconds; a driver paging through a table can't ask for one with every request. The code is therefore exchanged once for a session, which then accompanies every request in the X-Wkl-Session header:

POST /auth/v1/totp            first factor: personal key or Windows account
POST /auth/v1/totp/{alias}    first factor: SQL Server login (Basic), checked at this alias
Body:  { "code": "123456" }                  optionally with "role": "vertrieb"
201:   { "session": "…", "id": "t…", "person": "…", "header": "X-Wkl-Session", "expiresAt": "…" }

If the body names role, the session is bound to this database identity: a request without its own choice runs under it. The choice is checked on every request, not at the exchange.

• The session isn't a sign-in. Every request still carries its first factor; the session applies only to the person, the method and—with a key—the key with which it was opened. A stolen token alone opens nothing. That's why it's a separate header instead of Authorization: Bearer: with Windows and Basic, that header already carries the first factor.
• Lifetime totp_session_hours (set Settings, otherwise Gateway:Security:TotpSessionHours), default 10 hours, 1 to 168.
• End: expiry; Revoked in SessionTokens; revoking the key; disabling the person; setting up again or removing their second factor. This is looked up on every request.
• A code is valid once (its time step must be later than the last one accepted) and one step (30 seconds) before or after the service's clock; this tolerance is fixed and can't be configured. Five wrong codes per person or twenty per origin within 15 minutes lead to 429. The counters are kept in memory: a restart resets them, and anyone who knows a name can lock the person out for a while with wrong attempts.
• The exchange only over a protected connection (HTTPS, trusted proxy with https, same computer).

Clients without a dialog—Power BI, Excel, scripts—can't ask for a code. For them there are keys with TotpExempt: exempt from a required second factor, not from a denial, and permitted only as a read-only key. With rights that restrict the person to released views, and optionally a network binding, this is the narrowly limited access for reports.

Try a new rule—above all a denial—first with Mode = Report. The rule is then judged at every sign-in like an enforced one but changes nothing: whoever would be admitted without it stays admitted. The sign-in event names it in Rules with "mode": "report"; SignInRuleReports counts, per rule and version, how many sign-ins it would have hit, from how many people and origins, and which ones an exception would have spared (see administration). explain-sign-in shows it separately under tried.

If the result fits, enforce the rule with save-sign-in-rule and "mode": "Enforce"; from then on it takes effect. The switch is in the change log and changes the rule's version, so events from the trial and from operation stay distinguishable. The administration's lower bound (RequireAdminTotp) is no rule and can't be tried.

• Sign-in and identity pass-through
• Configuration: Security
• Tier access by location