Pass people's identities through to SQL Server

The column defaults, triggers, and views of your database evaluate USER_NAME() or SUSER_SNAME(). So that they see the person instead of the service's login, the service runs every statement of a person signed in with Windows under that person's own SQL Server login (Impersonate).

The data source is set to Personen (People) = Impersonate; a deny list excludes logins the service must never impersonate (such as sa or sysadmins from outside). In SQL Server, the data source's login needs the permission to impersonate other logins, and every person needs a login of their own. Rules and responses: Identity pass-through.

1. In SQL Server, give the data source's login the permission, and additionally block logins there that must never be impersonated:

   GRANT IMPERSONATE ANY LOGIN TO [<DATA SOURCE login>];
   DENY IMPERSONATE ON LOGIN::[sa] TO [<DATA SOURCE login>];

   Residual risk. IMPERSONATE ANY LOGIN allows the data source's login to impersonate any login, including one with sysadmin permissions. A DENY blocks only the login named; other sysadmins, including ones created later, can still be impersonated. The service's deny list (step 3) checks before every switch, but it's part of the service: whoever has the data source's connection string bypasses it. The permission per login is narrower: GRANT IMPERSONATE ON LOGIN::[CORP\j.doe] TO [<data source login>] (GRANT Server Principal Permissions), at the cost of an entry for every new person.

2. Create a Windows login of their own in SQL Server for every person (CREATE LOGIN [CORP\j.doe] FROM WINDOWS) and give it the permissions in the database. Access only through a Windows group isn't enough: SQL Server doesn't impersonate such a person.
3. Switch the data source over, and create the service's deny list.
   • With an administration store: Datenquellen (Data sources), select the data source, set Personen to Impersonate; you maintain the deny list under Identitäten (Identities), tab Sperrliste Identitätswechsel (Impersonation deny list) (Weitere (More) > Neuer Sperreintrag (New deny entry)).

   • Without an administration store:

     { "Name": "fachdaten", "Provider": "sqlserver", "ConnectionString": "…",
       "Persons": "Impersonate",
       "DenyImpersonate": [
         { "Logins": [ "sa", "CORP\\Database-Operations" ] },                 // never
         { "Roles": [ "sysadmin" ], "ExceptFrom": [ "192.168.0.0/16" ] }    // sysadmins only from your own network
       ] }

4. If there's a proxy in front of the service and the deny list uses networks, register it under Security:TrustedProxies; see Behind IIS or a proxy.
5. Check with the person: a newly created row in a table whose column default enters SUSER_SNAME() carries the person. If the data source accepts custom statements (AllowPassThrough) and the person may write there, a custom statement SELECT SUSER_SNAME(), ORIGINAL_LOGIN() also shows the person and the data source's login. The person doesn't need the sql right for this: under impersonation, the person's permissions in SQL Server decide.

To revert. Set Personen back to Service; statements then run under the data source's login again—through the administration store with the next request, through the configuration file after a restart. You revoke the permission in SQL Server with REVOKE IMPERSONATE ANY LOGIN FROM [<data source login>].

• A Windows account without its own login is refused (403), not run under the data source's login as a fallback. A person with a personal key and no registered SQL Server login, however, runs under the data source's login, with their name in the session context; register a SQL Server login for them in the administration if they're to run as themselves. On the server, save-person --name … --login … does the same and leaves the person's rules and lock unchanged (see Commands).
• After renaming a Windows account, the login has to be renamed too: ALTER LOGIN [CORP\old] WITH NAME = [CORP\new]; see Troubleshooting.
• Cost: the switch lengthens every request in the service. As a guideline for local Windows accounts: about 15 ms per request compared with about 2 ms without a switch.
• Shared logins instead of one per person: if people are to work under a department login, set up “Set up database identities” (documentation of the administration app).
• Personal keys impersonate a person's login if the person has a SQL Server login of their own in administration; service keys never switch identity.

• Sign-in and identity pass-through
• Set up sign-in with the Windows account