Set up sign-in with the Windows account

People sign in with the Windows account under which Access, Excel, or Power BI runs—without a key or password in the connection or workbook. Rights depend on Windows groups, as elsewhere in Windows.

You turn on the sign-in method in the configuration file (AuthMode with Windows); mappings determine which accounts and groups get which rule. Rules and responses: Sign-in: Windows account.

1. In the configuration file, add the method Windows to AuthMode under Gateway:Security, and keep the methods that are already listed there. Example with an administration store:

   "Security": { "AuthMode": "ApiKey, PersonalKey, Windows" }

   • With an administration store: Restart the service now.
   • Without an administration store: Don't restart yet. Without a store there are no personal keys (with PersonalKey the service doesn't start), and the Windows mode requires at least one mapping in the same file (step 2). There, the example is "ApiKey, Windows".
2. Map groups or accounts to rules. Each rule you name must already exist.
   • With an administration store: in the administration app under Zuordnungen (Assignments), tab Windows-Konten und -Gruppen (Windows accounts and groups); see „Assign Windows accounts and groups“ (documentation of the administration app).

   • Without an administration store: in the configuration file under Security; Client names the Id of a rule under Security:Clients. Then restart the service:

     "Security": {
       "WindowsAccounts": [
         { "Account": "CORP\\BusinessData-Readers", "Client": "fachdaten-lesen" },
         { "Account": "CORP\\BusinessData-Editors", "Client": "fachdaten-pflege" },
         { "Account": "S-1-5-32-545",               "Client": "auskunft" }
       ]
     }

     Specify built-in groups by their SID (S-1-5-32-545 instead of BUILTIN\Users): their names depend on the Windows language. A name Windows doesn't know prevents startup or is rejected when saving.

3. Connect the client with Windows sign-in: the ODBC driver with AUTH=WINDOWS, PowerShell with -UseDefaultCredentials (PowerShell 7 over http additionally with -AllowUnencryptedAuthentication).
4. Check: a request responds 200; the audit names the Windows account. Before every new connection, there's an anonymous 401—that's the first step of the method, not an error.

• Rights add up. If a person is a member of several mapped groups, all the rules apply. Anyone who should only read doesn't belong in any group that may write.
• Group changes take effect with the person's next sign-in, not immediately.
• Kerberos and NTLM: Windows negotiates the method itself. Kerberos requires an SPN HTTP/<name>, where <name> is the host name clients use in the service's address. The SPN belongs to the account the service runs under; for a virtual service account or Network Service, that's the computer account. If the SPN is missing, Windows falls back to NTLM.
• A person without a matching mapping reaches nothing; the audit names who tried.

• Sign-in and identity pass-through
• Pass people's identities through to SQL Server