Data integration · data service and ODBC driver
Data integration for proven applications: SQL Server data behind a data service
A way to your data—with no database password on the workstation. And no rewrite of the application.
Two products: Database Gateway exposes SQL Server data under control, and the OData ODBC Driver makes it usable from Access and other ODBC applications. Excel, Power BI, and your own programs reach the data service without a driver.
- Access with the OData ODBC Driver
- Database Gateway
- SQL Server
The data service checks sign-in and permissions and keeps the log. Only it connects to the database.
The starting point
Straight to SQL Server: easy in the office, hard everywhere else
Connecting Access and Excel directly to SQL Server requires every workstation to reach the database server and every person or application to have a SQL Server login. That leads to three problems:
- From outside, only through a VPN or an open database port.
- The password lives in the file. If you check “Save password” when linking, Access stores it unencrypted, Microsoft says—anyone with a copy of the application can read it.
- Permissions and logging are spread across many places, with no central way to block access, require a second factor, or set rules by where a request comes from.
Which product?
What problem do you want to solve?
- Share SQL Server data in a controlled wayOver OData or HTTP, with sign-in, permissions per object, and logging—without giving users and programs access to the database. That's Database Gateway.
- Move an existing Access application overLink a data service's tables and edit them in datasheet view; forms and reports stay as they are. That's the OData ODBC Driver.
- Analyze data in Excel or Power BINo separate product: the data service's OData feed, loaded through Power Query.
- Access it from your own programRead data and call procedures through the data service's HTTP contract, with or without the .NET client.
- Allow personal devices and contractorsOne rule per situation, enforced on every request and auditable afterward.
- Do I need the driver?Only for Access and other programs that work through ODBC. Excel and Power BI don't need it.
When you need neither: if everyone works on the corporate network and the workstations may keep reaching the database directly, a direct SQL Server connection fully covers the work in the office.
How they combine
Designed to work together—and each one usable on its own
The data service works without the driver: Excel and Power BI read its OData feed, and programs call it over HTTP. The driver also works with another OData v4 service. Used together, the path looks like this:
Workstation
- Access with the OData ODBC Driver
- Excel or Power BI
- .NET program
- Administration app
no connection string with a database password
OData v4 over HTTPS · OData feed · HTTP
Database Gateway
- Sign-in
- Permissions per object
- SQL built or checked
- Log
Only the service checks sign-in and permissions.
Only here is a connection to the database opened
SQL Server
- addressed by a name, such as sales
SQL runs under the login of the alias, the signed-in person, or a shared login—set per alias.
What the driver promises—depending on the service
With Database Gateway, the driver uses its extensions. With another OData v4 service, every promise depends on what that service supports.
| What the driver promises | Another OData v4 service | Database Gateway |
|---|---|---|
| Reading, filtering, sorting, and counting on the service | yes | yes, measured |
| Writing with write-conflict detection | with Core.OptimisticConcurrency | yes, measured |
| Retrying a change without a duplicate record | only if the service honors Idempotency-Key—confirm with the vendor before you rely on it | yes, measured |
| Transactions | only with Store.Transactions | yes, measured |
| Totals and grouping without pass-through | only with Aggregation.ApplySupported | yes, measured |
| Statements that can't be translated | only with a pass-through address | yes, for authorized callers |
| Second factor, choice of database identity | only with the documented extensions | yes, measured |
The full table, and what a service has to support for each promise
What adopting it involves
Putting a data service between application and database is a decision with consequences for operations and for applications. Here they are up front.
| Aspect | Database Gateway | OData ODBC Driver |
|---|---|---|
| Runs | as a Windows service on a server, optionally behind IIS; one instance per administration store | on every workstation, in the bitness of Office; the package installs both 32-bit and 64-bit |
| Operations | an administration store with a master key kept separately; backup, key rotation, and emergency access are part of it | a service certificate the workstations trust; an update with every release |
| Impact on applications | programs speak HTTP or OData, no longer SQL Server; if the service is down, no workstation reaches the data | connection strings of existing Access applications change; every request carries a fixed HTTP overhead; loops that fetch row by row get slower than over a direct connection |
| Prerequisites | Data service prerequisites | Driver prerequisites, known gaps under Limits |
What each release changes is in the changelogs: data service · driver
What your IT security team will want to know
Whoever signs off on the data service and the driver asks three things: what the products do, what stays with operations, and what they don't do.
- What the data service and the driver do: workstations need neither a database password nor a connection to the database. Only the service checks sign-in and permissions per object. SQL is built there, from names it has looked up in the catalog and from bound values; a statement a caller writes itself is parsed and checked, or runs unchecked only on a route you've explicitly enabled. Whether a private device, a contractor, or access from abroad is allowed is your decision, by sign-in method, origin, and country; the service enforces it on every request, and a revocation takes effect with the next one. Every request is logged, including rejected ones. The driver sends credentials only over HTTPS or to the local machine, and a certificate that Windows doesn't trust ends the connection.
- What stays with operations: which login SQL Server runs under, set per alias—the alias's, the signed-in person's, or a shared login. Its database permissions limit what can be reached. Add to that the separately kept master key with backup, key rotation, and emergency access, the certificate the workstations trust, and retaining and reviewing the logs.
- What they don't do: the driver isn't a security filter; an application that calls the service directly over HTTP has the same permissions. The logs have no built-in protection against later changes. The service doesn't know the state of a device, and whatever a device has received stays there; saving, copying, and printing are up to device management and the application. With another OData service, that service's security applies.
The details: the data service's security model · the driver's security model · zero trust and private devices
Find your way into the docs
| You want to … | Database Gateway | OData ODBC Driver |
|---|---|---|
| get a first result | From an empty server to the first query | Link a table in Access |
| understand how it works | Concepts | Concepts |
| assess security | Security model | Security model |
| evaluate adoption | Background, use cases, and alternatives | Requirements for the service |
| look something up | Reference | Reference |
| fix a problem | Troubleshooting | Troubleshooting |
Not every proven application needs reinventing. Sometimes it just needs a sensible way to its data. Features, requirements, and limits for both products are in the shop.
See the products