Set up database identities

In many databases, people don't have permissions of their own. Instead they work through a shared login per department, such as sales, whose password everyone knows. With database identities, each person signs in as themselves and the service switches to the shared login on their behalf. Nobody needs the password anymore, and the service still knows who did what.

In the Identitäten (Identities) area, you create the identities that requests may run under, separately for each data source. In each identity's “Zuordnungen” (Assignments) detail list, you specify who may assume it: a person, a Windows account, a Windows group, or an individual key.

Prerequisite in SQL Server (the database owner's job): the shared login exists, it has its permissions, and the data source's login is allowed to switch to it:

GRANT IMPERSONATE ON LOGIN::[sales] TO [<DATA SOURCE login>];

What this permission means. IMPERSONATE lets the data source's login work as sales with EXECUTE AS LOGIN, with all of the permissions of sales and without its password (Microsoft: “GRANT Server Principal Permissions (Transact-SQL),” https://learn.microsoft.com/sql/t-sql/statements/grant-server-principal-permissions-transact-sql, and “EXECUTE AS (Transact-SQL),” https://learn.microsoft.com/sql/t-sql/statements/execute-as-transact-sql). This applies in SQL Server itself, not just through the data service: anyone who knows the data source's credentials can assume any login it's allowed to switch to. Therefore:

• Grant IMPERSONATE only on the shared logins that an identity actually needs, and never on logins with server roles such as sysadmin.
• The data source's credentials are stored encrypted in the administration store; ideally, nobody knows them outside of it (see Connect a data source).
• The block list (below) is a second safeguard: the service never switches to anything on it, even if SQL Server would allow it.

1. Open Identitäten, select Neu (New), and fill in the form on the right:

   Field	What to enter
   Datenquelle (Data source)	Pick it from the list
   Name zur Auswahl (ROLE=) (Name to choose by)	The name a person uses to choose the identity, for example sales; unique per data source
   Art (Kind)	Sammelanmeldung (Login) (Shared login): switch to the shared login. Eigene Anmeldung der Person (Own) (Person's own login): switch to the person's own login. Anmeldung der Datenquelle (Alias) (Data source's login): no switch. Below the list, a line explains what the selected kind does
   Sammelanmeldung (Shared login)	Only for the shared-login kind: the login in SQL Server, for example sales
   Aktiv (Active)	Selected

2. Go back to the list. For the shared-login kind, the service tries the switch right away and rejects the identity with a message if the login doesn't exist, is on the block list, has no access to the database, or SQL Server refuses the switch.
3. Expand the identity, open the Zuordnungen tab, and add one row for each person or account allowed to use it, filling in exactly one of the columns Person, Windows-Konto oder -Gruppe (Windows account or group), or Schlüssel (Key). You pick the person and the key from a list (the person's name; the key's label and how it ends); you type a Windows account or group by name.

In the screenshot: (1) who may assume the identity.

Result: Requests from the assigned people run under the shared login. The audit names both: … durch Jane Doe (k…) als sales (… by Jane Doe (k…) as sales).

• Several identities per person: If the person doesn't choose one, their Vorgabe-Identität (Default identity) applies (a drop-down list under Personen und Schlüssel (People and keys); it offers the identities assigned to the person, to their keys, or to their Windows account, but not those assigned through a Windows group). If there isn't one, the service rejects the request and lists the possible identities. With the ODBC driver, the person chooses with ROLE= or from a selection list.
• One identity per Access session. Access shares a single connection among all linked tables in a process whose connection strings are identical apart from the password, so the identity of the first connection applies to all of them. This is observed Access behavior that Microsoft doesn't document as guaranteed. To use a different identity, restart Access.
• Block list: The service never switches to logins and server roles on the data source's block list, such as sysadmin. The list is in the Identitäten area on the Sperrliste Identitätswechsel (Impersonation block list) tab, shown as a tree: each entry is a data source, with its attributes below it. Create an entry with Weitere (More) > Neuer Sperreintrag (New block-list entry) and its attributes with Weitere > Merkmal zum Sperreintrag hinzufügen … (Add attribute to block-list entry) on the selected entry: login (a login, by name or SID) or role (a server role), optionally with from or except_from and a network.
• Turn off instead of deleting: Clearing Aktiv on an identity or an assignment takes effect with the next request.
• Visible in the database: SESSION_CONTEXT(N'wkl.person') returns the person and SESSION_CONTEXT(N'wkl.role') the identity, for triggers and column defaults that record who made a change.

• Assign SQL logins
• Connect a data source
• Reference: Identity pass-through and database identities (reference) (data service documentation)