Connect a line-of-business application

An Access line-of-business application with VBA reads master data, posts transactions through procedures, and should work in a way the database can attribute to the person operating it. It should reach only its own objects, not everything the person may do otherwise.

The service knows the person, not the program: a request from the application and one from Excel by the same person look the same to it. So you separate the application through its own alias and a rule for that alias; you pass the person to SQL Server so procedures, triggers, and views know who is acting.

1. Create the alias (DataSources) with a name that names the application and, if wanted, Persons: Impersonate; see pass people's identities through to SQL Server.

2. A rule for the application on its objects only:

   [ { "Id": "fachanwendung-auftrag", "Databases": [ "fachanwendung" ],
       "Resources": [ "views", "procedures" ], "Objects": [ "auftrag.*" ],
       "Verbs": [ "GET", "POST" ], "ReadOnly": false } ]

   Assign it to the people or their Windows group (PersonRules, WindowsAccounts).

3. Link in Access through the ODBC driver with the alias fachanwendung; on the company PC with the Windows account, from outside with a personal key and a code.
4. The VBA class's own queries you enable as checked statements, not with the right sql; see enable custom statements.
5. Check: In SQL Server, SUSER_SNAME() (with Impersonate) or SESSION_CONTEXT(N'wkl.person') shows the person; AuditEntries names person, alias, and object per request. The service answers an object outside auftrag.* through this alias with 404.

Result: Through its alias, the application reaches only its objects; the database knows for every posting which person triggered it.

• No identifier of its own for the application. A key always belongs to a person or a service. Another program of the same person using the same alias reaches the same. Where that isn't enough, the database decides: writing only through procedures that check their own rules.
• Service keys are for programs without a person (interfaces, nightly reconciliation); they never switch identity.
• The VBA class and its sign-in are described in “DbGwClient – VBA class for the data service” (in the documentation of the administration app).

• Rights: rules and scope
• Sign-in and identity pass-through
• Zero Trust and private devices