Background, use cases, and alternatives
This page explains the problem Database Gateway solves, the use cases it was built for, and which other options were considered and why they weren't chosen. It's written for anyone who has to decide on a deployment or justify one.
Summary
Business data lives in SQL Server databases. People work with Access, Excel, and Power BI, and there are custom programs as well. The workstations are not supposed to reach the database directly—not from home, on the road, or at a customer's site either. What's needed is access over HTTP that checks sign-in and rights in one central place, and that the Office programs can use the way they use a database today: Access with linked tables you can edit in datasheets and forms, and Excel and Power BI with queries whose filters run on the server.
The built-in Access feature for linking to data services („Datendienste“ in the German user interface) doesn't meet that need. It relies on an XML connection definition from Business Connectivity Services, not on OData, and the linked table is read-only (Microsoft: Link to a Data Service). To edit in a datasheet, Access needs an ODBC driver that binds Access's parameters as values and passes filters on to the server. That's why Database Gateway consists of a data service that offers OData v4 and a native HTTP API, plus an ODBC driver of its own that lets Access write through the service.
The starting point
Why not connect to the database directly? A direct connection from Access or Excel to SQL Server requires every workstation to reach the database server on the network (default port 1433) and every person or application to have a login in SQL Server. That leads to three problems:
| Problem | Consequence |
|---|---|
| Outside the corporate network, the database server can be reached only through a VPN or an open port | An open database port on the internet is a well-known target for attacks; a VPN has to be set up and maintained on every device, and every user has to operate it |
| The credentials live in the applications | Access stores a linked table's connection in the database file, so every copy of the file carries it along |
| Rights and logging are spread across many places | What an application may do is decided by the permissions of its SQL Server login and by each application's own code; there's no central way to block access, require a second factor, or tier access by location |
What operations require. In the intended setup, the data service is the only way to the database: workstations can't reach SQL Server. Every access from an application—including a query that passes SQL to the database unchanged (pass-through) and access from VBA through ADO—therefore has to go through the service.
Use cases
| Use case | Who | How |
|---|---|---|
| Link the tables of an existing Access application; read, filter, and edit them in datasheets and forms, with a write conflict on concurrent changes | case workers | Access through the OData ODBC driver |
| Pass-through queries and ADO access from the same Access applications | case workers | the same driver, using the service's pass-through route |
| Analyses and reports | finance, management | Excel and Power BI through Power Query (OData feed), no driver |
| Working in the office, from home without a VPN, at a customer's site, or on the road | the same people | the same programs; simple sign-in on the corporate network, a second factor outside it, and a time-limited, read-only key at a customer's site |
| The database needs to know who made a change („created by“, triggers, per-person views) | business application | passing the person through to SQL Server |
| Custom programs access the data | development | HTTP with JSON or XML; .NET client, VBA class |
| Manage people, keys, second factors, and rights | administration | the administration app for Access, through the administration API |
Alternatives considered
| Alternative | Outcome |
|---|---|
| Direct access to SQL Server, on the corporate network and through a VPN | covers the office use cases completely, but contradicts the goal of operating without direct database access and leaves the problems above unsolved |
| Built-in Access linking to data services („Datendienste“) | relies on Business Connectivity Services, not on OData; the linked table is read-only (Microsoft: Link to a Data Service) |
| Power Query alone | enough for reading in Excel and Power BI; writing isn't supported |
| An API with its own functions or procedures for each table | every new table and every schema change requires a change to the API, and Office programs can't link such an API as a table. Instead, the chosen design is a service that builds the statement from the request (method, table, key, fields) and reads the tables from the database's catalog |
Cost considerations
- No per-workstation license fees. The data service, the driver, and the client aren't licensed per workstation.
- Existing Access applications stay. They aren't rewritten: only the connections of their tables and queries change, while forms and reports stay as they are. If an application builds its connections in its startup code, that code needs a small change.
- Running costs come from operating and maintaining the data service and the driver.
Limitations of this solution
- SQL Server only as the database. The service connects databases through loadable providers (see Service configuration (reference)); the provider for SQL Server is included.
- The OData ODBC driver doesn't offer every capability of the SQL Server ODBC driver. The driver documentation lists the gaps („Limits and known restrictions (reference)“).
- One instance per administration store; there's no failover across two instances (see Administration store and master key).
- A new dependency: if the service is down, no workstation can reach the data. With direct access, availability depends on the database server alone.