What is the data service?

graph TD
    A["Access through the ODBC driver"] -->|"OData v4, HTTPS"| D
    E["Excel, Power BI (Power Query)"] -->|"OData v4, HTTPS"| D
    P["Your own programs: .NET, VBA, scripts"] -->|"HTTP, JSON or XML"| D
    V["Administration app (Access)"] -->|"/admin/v1/"| D
    D["Wkl.Data.Gateway data service"] --> Q[("SQL Server: one or more databases")]

The data service (Wkl.Data.Gateway) is the only way for users and programs to reach your SQL Server databases. It accepts requests over HTTP, checks sign-in and rights, generates the SQL, and returns the result. You need it when workstations, Excel workbooks, or programs must not connect directly to the database—in the office, from home without a VPN, or at a customer's site.

• Access, Excel, and Power BI without a database connection. Access links tables through the Wkl OData ODBC driver and edits them in datasheets and forms. Excel and Power BI load data through Power Query (OData feed), and the database does the filtering and sorting.
• Only SQL that the service generated or checked. For ordinary requests, the service writes the SQL itself. When a caller sends a statement of its own, the service parses it and checks every object against the catalog and the rules. A statement runs unchecked only on a route you enable separately, and there the permissions of the database login are the only limit.
• Sign in the way that fits: Windows account, SQL Server login, a personal key for each person, or a service key for programs—plus a second factor (authenticator app), depending on the method and the network.
• The database sees the actual person. If you choose, statements run under the person's own SQL Server login or under an assigned department login, so triggers and column defaults can tell who made a change.
• Administration without restarts. Data sources, rules, people, and keys are kept in an administration store that you maintain with the administration app for Access. Connection strings and second-factor secrets in the store are encrypted with a master key, and personal keys are stored only as a check value; names, rules, and people are readable.
• Audit trail. Every request for data or administration is logged with caller, alias, object, method, result, and correlation ID—including refused requests. Every change made through the administration API is recorded in the store's change log.
• One installer: Windows service, HTTPS certificate (including Let's Encrypt), firewall, and running behind IIS.

Aliases instead of database names. Each database is exposed under a name of its own (an alias), such as fachdaten. Only the service knows the connection string; no caller picks a database.

The catalog is the allow list. The service reads tables, views, columns, and keys from the database itself. It looks up every name in a request there and binds every value as a parameter. Anything that isn't in the catalog doesn't exist for the caller.

Two interfaces over the same catalog. OData v4 (/odata/v4/…) for Access, Excel, and Power BI, and a native HTTP API (/api/v1/cf/…, JSON or XML) for programs. Sign-in, rights, and audit work the same way on both.

Rules define the scope. A rule names aliases, kinds of objects (tables, views, procedures, custom statements, administration), methods, and whether it is read-only. People, Windows groups, SQL Server logins, and service keys are assigned rules.

A write affects exactly one row. Every change for which the service generates the SQL addresses a single row by its complete key. A table without a key can be written only if you enable it for that; the row is then identified by its original values. The service detects changes made by others if the table has a row version (rowversion) or a row stamp is turned on for it: if the row has changed since it was read, nothing is written, and Access shows its familiar write conflict. Without either, the last change from your own program overwrites the previous one, and Access, through the ODBC driver, refuses to change the row. See Make tables without a row version editable.

• Background, use cases, and alternatives—the problem the product solves and the other options
• Do I need the ODBC driver?
• Install the data service
• “Administrator guide for WKL Database Gateway Manager”—data sources, people, rights, second factor
• Wkl.Data.Gateway data service: overview—concepts, how-to guides, reference