Aliases, the catalog, and the central rule

The data service writes its own SQL: it uses an alias instead of a database name, names that it looks up in the database's catalog, and values that it binds as parameters. This page explains the model behind every request, and the two routes for statements that callers write themselves: checked statements, which the service parses and checks object by object, and unchecked pass-through, which you enable separately.

• Databases are visible to callers only under the alias names you release.
• Every identifier is checked against the catalog, and every value is bound as a parameter.
• Statements written by callers take one of two routes: checked—parsed, with every object checked against the catalog and the rules—or unchecked, on a route you enable separately, limited by the permissions of the database login.

Callers never see a database name. They address databases through the alias names you release, and the mapping to connection strings lives in this service's configuration.

Callers aren't separated by port either, but by their credentials and the scope assigned to them: everyone reaches the service on the same HTTPS port (default 8433, modeled on SQL Server's 1433). The HTTP port 8434 responds only on the server itself, for tools, health checks, and an upstream web server.

The service runs no SQL that it hasn't generated or checked itself. It generates SQL from a typed contract: a released resource identifier, selected fields, structured filter nodes, sort specifications, and paging specifications. A statement that a caller writes runs only after the service has parsed it and checked every object (checked statements). The only exception is unchecked pass-through, which you have to enable separately.

The service refuses:

All values are parameterized. All identifiers are checked against released names. These are two different safeguards, and both are necessary.

Through POST /api/v1/cf/{alias}/sql.json, the service accepts a statement that the caller wrote and runs it. This isn't a loophole in the rule but a separate route that you have to enable—and, deliberately, the only one on which the service runs text without evaluating it.

Why it exists. A client that can only address one object at a time can't express a join, a grouping, or a set-based change at all. It would have to fetch every row involved and do the work locally instead of receiving only the result, and the effort grows with the size of the tables, not with the size of the result. That's exactly why database tools have pass-through queries.

What limits it—and what doesn't. The service doesn't read the statement: it doesn't parse it, doesn't match it against a pattern, and doesn't classify it as reading or writing. Such a check would be a promise this service can't keep; SELECT alone says nothing about what can be reached through views, functions, and ownership chains. The limit is therefore where it's actually enforced: in the permissions of the login under which the statement runs. Whatever that login may do, a pass-through statement may do—including reading, writing, and schema changes. Which login that is depends on how the caller signed in to the service (see Identity pass-through):

In the cases without sql, the rule's object lists don't limit the statement; it runs with all the permissions of that login.

Under the person's own login, SQL Server checks every statement against that login's permissions— just as with a direct connection through Microsoft's ODBC driver. The sql right would add nothing there, but it would deny every user without it the joins, groupings, and function calls that Access sends as statements of its own; every saved query that spans several linked tables falls into that category. Under the alias's login, nothing but the rule limits an unchecked statement, so there sql remains the only door to it. Callers without sql send checked statements instead. On an alias with Persons: Impersonate, a Windows account without its own SQL Server login is refused when the session opens (403) and never falls back to the alias's login. A personal key whose person has no SQL Server login on record, on the other hand, runs under the alias's login, so the first row of the table applies to it.

In addition, these conditions always apply; otherwise the service responds with 403:

An existing * under Resources does not enable the route. The * is a statement about the object kinds in the catalog; if it could also mean „and any statement you write,“ every rule written earlier would have expanded the moment this route was added. A read-only caller is refused outright rather than restricted to reading statements: the service can't tell the two apart, so read-only here means no custom statements at all. The startup check catches both contradictions: if a rule names sql and is also read-only, the service doesn't start.

Access sends joins, subqueries, totals across several tables, functions, and set-based changes across linked tables as one statement, and doesn't compute them itself if the driver refuses them. With Microsoft's ODBC driver they run, because SQL Server checks the permissions. So that the same works through the service without giving every user the sql right, the service accepts checked statements at the same address—from every caller whose rules name the alias, if the alias allows it (AllowCheckedStatements, on by default).

Checked means that the service parses the statement with Microsoft's T-SQL parser and accepts it only if:

• each of its statements is one of these: SELECT (with joins, subqueries, UNION, WITH, grouping, window functions), INSERT, UPDATE, DELETE, or a call to a named procedure. Several statements in one batch (SELECT …; SELECT …) run as they do with Microsoft's ODBC driver; each is checked on its own, and a GO separator line is refused;
• every construct it uses is on a closed list—no schema changes, no dynamic SQL, no SELECT … INTO, no variables other than the bound parameters, no names qualified with a database or server, and no function that describes the server, the login, or other objects;
• every object it names is in the alias's catalog and is released for what the statement does with it, under the same rules as a request on the object itself: reading like GET, inserting like POST, updating like PATCH, deleting like DELETE, and running a procedure like POST. The target of a change also needs read permission, because a condition on its columns reveals values. Rights per object (Objects, ExceptObjects) apply as well.

What runs isn't the caller's text but a rewritten version in which every object carries the schema that the service checked it against. This way a caller can't reach anything they couldn't also reach object by object through OData; they're only spared fetching every row. A read-only rule allows only reading statements. Callers who have the sql right or work under their own login still get unchecked pass-through.

Large results are streamed. If a statement returns more than 500 rows, the response (JSON) starts with the first rows while SQL Server is still producing the rest; smaller results are collected and sent in one piece. Both forms are the same document—columns first, then data, then the counts. The response therefore starts at about the time SQL Server delivers the first row instead of waiting for the last one. If the statement fails after the response has started, the service drops the connection: the 200 status has already been sent, and a properly closed but truncated document would look complete. Every reader reports the aborted document as an error, and the cause is in the log under the correlation ID. If the statement fails within the first 500 rows, the service responds with the matching status. If the caller stops reading and closes the connection, the service cancels the statement in SQL Server instead of reading and discarding the rest. The XML representation is still collected in full.

The statement text is written to the audit stream before it runs, under the same correlation ID as the outcome of the request. Values are left out, because they're the data. The stream otherwise contains no statements, because the resource is enough there: it tells you what happened. Here, the text is the only description of what happened.

The service deliberately doesn't offer three seemingly obvious options:

• Enable custom statements
• HTTP: native API (reference)
• Sign-in and identity pass-through