WKL Database Gateway · data service for SQL Server
SQL Server data for Access, Excel, and your own programs—without database access from the workstation
No database password in the Access file. Sign-in, permissions, and logging in one place.
The data service takes requests over HTTPS, checks sign-in and permissions, builds the SQL, and returns the result. Access links tables the way it always has, and Excel and Power BI load through Power Query—in the office and outside the corporate network.
- Access, Excel, programs
- Data service: sign-in, permissions, log
- SQL Server
Workstations reach only the data service. It alone knows how to connect to the database.
The starting point
Linked tables on SQL Server work fine—until the work leaves the office network
Connecting Access and Excel straight to SQL Server is the usual approach. But it requires every workstation to reach the database server on the network, 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 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 travel with the file. Access stores a linked table's connection string in the database file. With “Save password” checked, it holds the password too—unencrypted, according to Microsoft—and every copy carries it along.
- Permissions and logging are spread across many places. What an application may do depends on its SQL Server login and on the code of each application. There's no central way to block access, require a second factor, or set rules by where a request comes from.
What about the usual options?
VPN, Access Data Services, Power Query—what they cover and where they stop
- Direct access on the corporate network and through a VPN covers office work completely. The three problems above remain, though, and it rules out running without direct database access.
- Access's built-in Data Services link relies on Business Connectivity Services, not on OData, and the linked table is read-only.
- Power Query alone is enough when Excel and Power BI only read. Writing isn't supported there.
- A custom API with functions for each table has to grow with every new table and every schema change, and Office programs can't link to it as a table.
The data service reads tables and views from the database's own catalog and builds each statement from the request. To edit in a datasheet, you add the OData ODBC driver, which lets Access write through the service. Background, use cases, and alternatives in detail
With the data service
One way to the database—checked on every request
To the outside, a database appears only under a name such as sales. On top of it sit two interfaces: OData v4 for Access, Excel, and Power BI, and a native HTTP API with JSON or XML for programs. Sign-in, permissions, and logging are the same on both.
You maintain data sources, people, rules, and keys in the administration app, without restarting the service. Its overview shows what needs attention.
- No database connection on the workstation—Access links tables through the ODBC driver, Excel and Power BI load through Power Query, and the database does the filtering and sorting.
- Only SQL the service built or checked—every name is looked up in the database's catalog and every value is bound as a parameter. When a caller sends its own statement, the service parses it and checks each object; an unchecked statement runs only on a route you've explicitly enabled.
- Rules define the scope—per alias, kind of object, method, and read or write; for people, Windows groups, SQL logins, and service keys.
- The database knows who made the change—under their own SQL Server login if you want, so triggers and column defaults can see who acted.
- Sign in the way that fits—Windows account, SQL Server login, personal key, or service key, with a second factor depending on the method and the network.
- A record of everything—every request is logged with caller, object, method, and result, including rejected ones, and every change to the administration goes into the change log.
- See who signs in from where, and what's causing load—sign-ins by outcome, hour, country, and network operator, with notes on unusual patterns, and load per query shape, with the time spent in the database and a suggested fix.
- Conflicting changes are caught—with a row version or timestamp, the service never writes over someone else's change, and Access shows the familiar write conflict.
A single installer sets it up: Windows service, HTTPS certificate, firewall, and optionally IIS in front—Install the data service.
Personal laptops, contractors, business trips—a rule for each
Whether a personal laptop may see customer data, whether someone works from abroad, or whether a contractor gets two weeks of access is your call. The data service enforces that decision on every request and records what it decided.
Request
- Access, Excel, Power BI
- Line-of-business app
from a company PC, a private device, or a contractor
Sign-in · origin · country · second factor
Data service
- Sign-in policy: deny or ask for a code
- Permissions: alias, object, method, read-only
- Log: sign-in, request, change
You set the policy and the permissions.
The database limits rows and columns through views
Database
- Views and stored procedures
under the person's own login, if you want
Being on the corporate network can spare someone a code, but it never replaces signing in. Each device and each purpose gets its own key—restricted, time-limited, and revocable on its own—and a revocation takes effect with the next request.
The service can't tell that a device is private, because it has no connection to a device management system. What it can tell is how someone signs in and from where—and that's what the rules are built on.
Ten everyday situations—and how to set them up
- Managed company PCSign-in with the Windows account and rules by Windows group; from the corporate network, optionally without a code.
- Personal laptopA personal key of its own, restricted, and from outside only with a code.
- Employee abroadA rule by country, plus an exception for the trip that ends on its own.
- Agent or supplierOne view for all of them that filters by the person asking, so each sees only their own rows. A rule on that view only, and a time-limited, read-only key.
- Excel and Power BIOne key per purpose, read-only and limited to one alias.
- Access or VBA line-of-business appAn alias of its own per application, and SQL Server sees the actual person.
- Unknown originDeny by origin, ask for a code, get notified of unusual patterns. In the administration app, an origin in the sign-ins overview becomes a denying rule in one step.
- Temporary contractorA person with a time-limited key, issued on demand.
- Lost deviceRevoke the key, end the sessions, reset the second factor—effective with the next request.
- Review by auditorsThe sign-in event, the request log, and the change log show why the service decided the way it did.
A sign-in rule takes effect as soon as it's saved. So create it for a test person first, have the service work out what applies, sign in for real—and only then widen it and watch the refused sign-ins for an hour: Check a rule before enforcing it.
Requirements and limits
Is the data service a fit for your setup?
It fits when your data lives in SQL Server and workstations, Excel workbooks, and programs shouldn't reach the database directly. Check these points first:
- SQL Server only. The service connects databases through loadable providers; the one that ships is for SQL Server.
- One instance, one new dependency. Each administration store runs one instance; there's no failover across two. If the service goes down, no workstation can reach the data.
- Operations: an administration store with a master key kept separately, plus backup, key rotation, emergency access, and an HTTPS certificate the workstations trust.
- Access through the ODBC driver: it doesn't offer everything the SQL Server ODBC driver does; the driver's limits list what's missing.
- Administration app: Access from Microsoft 365, full version or Runtime, 32-bit or 64-bit. Windows sign-in from the app uses NTLM, not Kerberos in a domain.
What your IT security team will want to know
Whoever signs off on running the data service asks three things: what the service does, what stays with operations, and what the service doesn't do.
- What the service does: no database connection and no database password on the workstation. Sign-in and permissions per alias, object, and method live in one place. Every request is logged with caller, object, method, and result, including rejected ones, and every change to the administration goes into the change log. Revoked keys, disabled people, and ended sessions take effect with the next request. A person who has left keys, sign-ins, or changes behind can only be locked, not deleted, so every log entry still points to someone.
- What stays with operations: the permissions of the SQL Server logins the service runs under, and who may send their own statements—for those callers, only the database permissions limit what they can reach. Add to that the separately kept master key with backup, key rotation, and emergency access, the HTTPS certificate, and retaining and reviewing the logs. The installer binds unencrypted HTTP to the local machine; keep it that way.
- What the service doesn't do: check the device. Whether it's managed, encrypted, or up to date on protection, the service doesn't know, because it has no connection to device management. Whatever a device has received stays there; saving, copying, and printing are up to device management and the application. Nor is there built-in protection of the logs against later changes.
The data service's security model · Operations: backup, keys, emergency access · What the service doesn't control
Where to start
- From an empty server to the first querySet up the service, connect a database, give one person read access, and read the first rows—in about an hour.
- Load data into Excel or Power BIThrough Power Query, filtered on the service; no ODBC driver needed.
- Link tables in AccessWith the OData ODBC driver, and edit them in datasheets and forms. Do I need the driver?
- Call it from a programThe native HTTP API with JSON or XML.
- Administer the data serviceMaintain data sources, people, keys, and rules in WKL Database Gateway Manager.
- Documentation overviewGetting started, how-tos, concepts, reference, troubleshooting, and operations.
Want to know what comes with the data service and what your server needs for it? The product page lists features, requirements, and limits.
See the product page