Administrator guide for WKL Database Gateway Manager

WKL Database Gateway Manager, the administration app, is where you decide who can use WKL Database Gateway (Wkl.Data.Gateway) and in what way: people and their keys, rules, the second factor, data sources, and the sign-in policy. The app is an Access application that runs on your workstation; every change is validated and saved by the service itself.

User interface language: The administration app is available in German only. This guide quotes each label in bold exactly as it appears on screen and gives its English meaning in parentheses the first time it appears on a page, for example Weitere (More) > Schlüssel ausstellen … (Issue key …). Later mentions on the same page use only the German label.

Data service documentation: The data service is a separate product with its own documentation. References to it lead to its English pages and are followed by “(data service documentation)”.

graph LR
    subgraph AP["Administrator workstation"]
        V["Administration app (Access)"]
        T["ODBC driver Wkl OData"]
        K["VBA class WklGatewayClient"]
        V -->|"read and change lists"| T
        V -->|"commands"| K
    end
    subgraph SV["Server"]
        D["Data service Wkl.Data.Gateway"]
        S[("Administration store: data sources, rules, people, key hashes, sign-in policy")]
        D -->|"reads and writes"| S
    end
    Q[("SQL Server data sources")]
    A["Access, Excel, Power BI, custom programs"]
    T -->|"OData over HTTP(S) /admin/v1/odata/"| D
    K -->|"HTTP(S) /admin/v1/"| D
    A -->|"HTTP(S): OData, custom contract"| D
    D -->|"TDS, as the data source's login or as the person's identity"| Q

Figure: Components and connections. The “Workstation” and “Server” boxes are trust boundaries: anything that crosses them is a request to the data service, which authenticates it and checks it against the rules. Neither users nor the administration app ever connect to SQL Server directly. Between computers you need HTTPS, because keys and passwords would otherwise travel in clear text; plain HTTP is acceptable only on the same computer. The VBA class sends a key or password only over HTTPS or to the local computer.

This guide explains how to set up and run the data service with the administration app for Access: connect data sources, give people access, grant rights, secure sign-in, and back up the service.

This guide is for administrators who set up the data service and grant access. You need:

• a running data service with an administration store (Gateway:Store), set up as described in Service configuration (reference) (data service documentation);
• 32-bit or 64-bit Microsoft Access or Access Runtime, the “Wkl OData” ODBC driver, the WKL Office JSON library 2.0 or later (WKL Access JSON up to release 1.0.3), and the WKL Forms Object Library 2.0 or later (WKL Access Components up to release 1.68.2). Supported: Access from Microsoft 365 or Office LTSC 2024, full version or Runtime, 32-bit and 64-bit. Older versions from Access 2010 on usually work as well, but they aren't acceptance-tested and no longer receive security updates from Microsoft;
• for your own sign-in, a personal key or an assigned Windows account, plus an authenticator app for the second factor.

• Data sources: Connections to SQL Server databases. You can turn each data source on or off without restarting the service.
• People and personal keys: Everyone signs in with their own key, their Windows account, or their SQL login. Keys expire and can be revoked immediately.
• Rules: What someone can reach—which data sources, which kinds of objects, read or write.
• Second factor: Codes from an authenticator app (TOTP), required by a sign-in policy that you define per scope, sign-in method, and network.
• Database identities: People work under a shared SQL Server login without knowing its password, yet the service still knows who did what.
• Audit trail: A change log of every administrative change, an audit of data access, a list of open sessions, and evidence reports as Excel and PDF.
• Load: What each query shape costs the service and the database, the weekly pattern, and known fixes.
• Sign-ins: Sign-in decisions by time, outcome, and origin (country, city, and network operator from a geolocation database), unusual patterns, and blocking an origin.

The administration app is an Access file: dbgwmgr_x86.accde for the full version of Access, dbgwmgr_x86.accdr for the free Access Runtime. This delivery contains the edition for 32-bit Office. 64-bit Office needs its own edition (dbgwmgr_x64.accde/.accdr); ask the manufacturer for it. When it starts, you see only the sign-in card. After you sign in, a single window shows the navigation bar on the left and the selected area on the right:

The groups follow how often you need them: first the daily work in Personen und Zugang (People and access: people, rules), then Betrieb (Operations) and Überwachen (Monitoring), and last Einrichten (Set up: data sources, identities, assignments, sign-in policy), which you mainly need for a new installation. Einrichten is collapsed when the app starts; click its header to expand it. Day to day, you usually start in Übersicht (Overview); its cards take you to the right list.

On the Personen und Schlüssel page, the commands for the selected person are right in the command bar, ordered by a person's lifecycle:

In the command bar, the groups Schlüssel, Zweiter Faktor und Sitzung, and Rechte are split buttons: a click runs the group's most frequent command (the main part), and the arrow next to it opens the others. A row's shortcut menu (right-click) offers Zugang wiederherstellen … for that person at the top, the three groups as submenus below it, and Person sperren … after a line, followed by the list's own commands (sort, copy, export …). This guide writes the path as group > command, for example Schlüssel > Schlüssel ausstellen …. Commands that affect the whole list, such as Neue Person … or Personen importieren …, are only in the command bar.

In the screenshot: (1) navigation bar, (2) command bar, (3) cards for items that need action, and the load.

The command bar below the header holds the commands for the list, the area's main command, Weitere (More), Exportieren … (Export …), and Aktualisieren (Refresh). Where each command is and how to reach it from the keyboard: Work with areas, lists, and commands.

Edit form. You edit data sources, rules, people, identities, and sign-in rules in the form to the right of the list (on the Personen und Schlüssel page, in the Person card). It shows the selected entry with all of its fields; when the area opens, the first entry is selected. The create command (for example Neue Datenquelle) clears the form for a new entry; the edit command, a double-click, Enter, or F2 opens the selected entry in it. The entry is saved as soon as you go back to the list. Fields shown in gray without a border are filled in by the service.

Input validation. Before saving, the form checks:

• required fields;
• the name of a data source: the same rules as an ordinary SQL Server name; it starts with a letter or _, followed by letters, digits, or @ # $, up to 128 characters;
• the values of the resource and verb attributes;
• networks in from and except_from, for example 10.0.0.0/8 or 2001:db8::/32; an IPv4 address always needs all four octets;
• for a shared-login identity, the shared login.

If something is wrong, the field gets a red border, the message appears as a tooltip on the field and as a summary below the form's title, the entry isn't saved, and the cursor stays in the form. Anything only the service can know (such as whether a shared login exists in SQL Server) is checked by the service when you save; if it rejects the entry, you see the reason. Correct your input, or press Esc to discard it.

Trees. Rules, the block list, and sign-in rules are shown as trees.

• Rules and block list: Each kind of entry has its own form; edit and delete follow the selected entry.
  • Neue Regel (New rule) or Neuer Sperreintrag (New block-list entry) creates a top-level entry, also when an entry is selected.
  • Merkmal hinzufügen … (Add attribute …) (block list: Weitere > Merkmal zum Sperreintrag hinzufügen …) creates an attribute below the selected rule; the form already links the new attribute to the rule.
  • Regel löschen … (Delete rule …) or Sperreintrag löschen … (Delete block-list entry …) deletes the selected entry and everything below it.
  • Attributes can be created and deleted but not changed; Merkmal ansehen (View attribute) only displays an existing one.
• Sign-in rules: You edit a rule as a whole in the Anmelderegel (Sign-in rule) dialog: Neue Anmelderegel … (New sign-in rule …) opens it for a new rule, and Anmelderegel bearbeiten … (Edit sign-in rule …) or a double-click opens it for the selected rule (see Define the sign-in policy).

Filtered lists. When a card on the overview opens a list, the list shows why it is filtered as a filter button, for example “Ohne zweiten Faktor ✕”; on the Personen und Schlüssel page, the matching work list on the left is selected as well. Click the button (or press Del) to remove the filter and show all entries again.

Detail lists (the arrow at the start of a row) and lists without a form work like an Access datasheet: a change is saved as soon as you leave the cell. Right-click for the shortcut menu with New, Delete, Copy, and Export; Shift+F9 reloads the list. In lists with detail views, Ctrl++ and Ctrl+- expand and collapse the details instead of creating or deleting entries. Each list remembers column widths, column order, sorting, grouping, and filters for your Windows account; Weitere > Spalten zurücksetzen restores the original layout.

The administration app is a tool that gives people access to databases. Whoever can use it can grant rights, and much of what it handles is personal data.

What needs protecting

Personal data

Responsibility under the GDPR. The controller as defined in Art. 4(7) GDPR is the organization that operates the installation, not the software vendor. The operator is responsible for the legal basis of the processing (Art. 6), the entry in the record of processing activities (Art. 30), the technical and organizational measures (Art. 32), and the periods after which data is erased (Art. 5(1)(e)). The app helps by never disclosing keys, by protecting administrative sign-ins with a second factor, and by deleting temporary local data. It doesn't replace any of these obligations. Text of the regulation: https://eur-lex.europa.eu/eli/reg/2016/679/oj.

Limitations

• Anyone who can write to the administration store file on the server (the service account, administrators of that computer) can also change the change log. The log is deliberately not tamper-proof (see Administration store and master key, section “Change log and audit evidence” (data service documentation)).
• The change log is kept indefinitely, and the service offers no way to delete or pseudonymize entries—not even for a deleted person. Their values (name, Windows account, email address) remain in the rows of their changes and in backups of the file. Operators must assess this with regard to storage limitation and erasure (Art. 5(1)(e) and Art. 17 GDPR) in their retention and deletion policy (see Administration store and master key, section “Retention and deletion” (data service documentation)).
• The app's delivery file isn't signed (see Set up the administration app on a workstation).

The table lists the most common situations and where to find the steps. Most of them start in Personen und Schlüssel with the person selected and a command in the command bar or the shortcut menu (right-click).

Setup

• Install the data service (data service documentation) (data service, on the server)
• Create the first administrator (data service documentation) (data service, on the server)
• Set up the administration app on a workstation
• Sign in to the administration app
• Work with areas, lists, and commands
• Connect a data source

People and access

• Create a person and issue a key
• Import many people at once
• Work with many people at once
• Set up or reset the second factor
• Revoke a key or lock a person
• Issue a presentation key and replace a lost key
• Set up a person's access by location
• Send an invitation by email

Rights

• Create a rule
• Assign Windows accounts and groups
• Assign SQL logins
• Set up database identities

Security

• Define the sign-in policy
• Review sign-ins and where they come from, set up the geolocation database
• Set up access for each trust situation: company PC, personal device, contractor, lost device

Auditing

• Read the log and sessions
• Create evidence reports

Load

• Find out what is loading the service and the database

Operations

• Oversee database updates and decide on packages
• Back up, restore, and move the administration store (data service documentation) (data service, on the server)
• Change the master key (data service documentation) (data service, on the server)
• Regain access when administration is locked out (data service documentation) (data service, on the server)

Problems

• Troubleshooting

• Signing in with a Windows account is supported with NTLM, not with Kerberos in a domain.
• The user interface is in German.
• Figures from the data service in this guide (for example a 15-minute lockout, 72-hour validity, 300 requests per minute) are its defaults at the time of writing. The values in the data service documentation take precedence.

• Wkl.Data.Gateway data service: overview (data service documentation): concepts, how-to guides, and a reference with the HTTP contract, configuration, and all responses
• Work with areas, lists, and commands
• VBA class `WklGatewayClient`: administration commands from your own VBA applications