Set-up guide: from an empty server to data in Access, Excel, and Power BI
This set-up guide takes you in eight steps from an empty Windows server to a user who reads data from your SQL Server database in Access, Excel, or Power BI. Each step says who does it and where, what's true afterwards, and where to find the details. The certificate usually takes the most time if you don't use an existing one.
Who does what
There are usually three roles, often held by three different people:
| Role | Where | Steps |
|---|---|---|
| IT (runs the server) | on the Windows server, with administrator rights | 2, 3 |
| Gateway administrator | at their own workstation with Access | 2, 4, 5, 6, 7 |
| User | at their own workstation with Access, Excel, or Power BI | 6 (collect the key, connect) |
| everyone | – | 1, 8 |
If one person holds several roles, the order stays the same.
The steps at a glance
| Step | Who, where | Result |
|---|---|---|
| 1. Understand what it's for | everyone | you know which parts you need |
| 2. Checklist | IT and Gateway administrator | everything is ready |
| 3. Install the data service | IT on the server | the service runs, and the first administrator has their key |
| 4. Set up the administration app | Gateway administrator at their workstation | sign-in with a second factor works |
| 5. Connect the first data source | Gateway administrator | the service reaches your database |
| 6. Set up the first user | Gateway administrator, then the user | the user reads data in Access, Excel, or Power BI |
| 7. Secure it | Gateway administrator | sign-in by location and device is in place |
| 8. Next | everyone | you know where everyday tasks, troubleshooting, and evidence are |
1. Understand what it's for
Who, where: everyone involved, before buying or before installing.
The data service makes the tables and views of a SQL Server database available over HTTPS. Users work in Access, Excel, or Power BI and don't need their own connection to SQL Server or access to the server's network; on every request, the service checks who is calling, from where, and what the person may do.
Result: You know which parts you need. For details, see What is the data service? and Components of Database Gateway.
2. Checklist
Who, where: IT for the server and the database, the Gateway administrator for the workstations.
- a Windows server or computer with administrator rights
- a SQL Server database and a login that can read it, or the service account with integrated security
- a server certificate for the address at which users reach the service, or a Let's Encrypt certificate that the installation obtains
- for the administration app: Microsoft Access, full version or Runtime, 32-bit or 64-bit
- this delivery: the edition for 32-bit Office; for 64-bit Office, ask the manufacturer for the
dbgwmgr_x64edition - for the users: Access with the „Wkl OData“ ODBC driver, Excel with Power Query, or Power BI Desktop
- an authenticator app on the Gateway administrator's phone; administration requires a second factor
- the delivery package:
Wkl.Data.Gateway-Setup-<version>.exe, the administration app and, if users work in Access, the ODBC driver
Result: Everything is ready. For details, see Prerequisites.
3. Install the data service
Who, where: IT on the server.
Run Wkl.Data.Gateway-Setup-<version>.exe and follow the pages. The defaults are fine to start with:
standalone Windows service, HTTPS on port 8433. Leave Ersten Administrator jetzt anlegen (Create the
first administrator now) selected: at the end, the console asks for the Gateway administrator's name and
shows their personal key (wkl_…) exactly once. Pass it on to the Gateway administrator securely.
Result: The service runs, and the first administrator has their key. For details, with every page of the installation and every setting, see Install the data service and Create the first administrator.
4. Set up the administration app and sign in
Who, where: Gateway administrator at their workstation.
Set up the administration app from its delivery and start it. Enter the service address
(https://<server>:8433) and your key from step 3. The first time, the app shows a QR code: scan it with
the authenticator app and enter the first code.
Result: You're signed in to administration with a second factor. For details, see „Set up the administration app on a workstation“ and „Sign in to the administration app“ (documentation of the administration app).
5. Connect the first data source
Who, where: Gateway administrator in the administration app.
Open Datenquellen (Data sources) and select Neue Datenquelle (New data source) in the command bar:
enter a name such as fachdaten, choose the provider Microsoft SQL Server, fill in the server and
database below it, then select Prüfen und speichern (Check and save). If the data source is reachable,
a short message shows how long the connection took; otherwise the Verbindung prüfen (Test connection)
window opens with the cause.
Result: The service reaches your database. For details, see „Connect a data source“ (documentation of the administration app).
6. Set up the first user
Who, where: first the Gateway administrator in the administration app, then the user at their workstation.
- A person with read access (Gateway administrator): Open Personen und Schlüssel (People and keys) and select Neue Person … (New person …). In the card, enter a name; under „Was darf die Person?“ (What may the person do?) choose the data source
fachdatenand Lesen (Excel, Power BI, Berichte) (Read); under „Wie meldet sich die Person an?“ (How does the person sign in?) choose Mit persönlichem Schlüssel (With a personal key); then select Anlegen (Create). The card grants the right itself: it creates the rulefachdaten-lesenif it doesn't exist yet. Finer rights (single tables, several data sources) are possible but not needed for the first user: „Create a rule“. If the user works in Excel or Power BI and the sign-in policy requires a second factor, issue them a key without a second factor right away (see step 7). - Hand over the key (Gateway administrator): The card shows a one-time link the user opens to collect their key. Pass it on or send it by email: „Send an invitation by email“.
- Collect the key (user): The user opens the link and keeps the key safe, for example in a password manager; they need it as soon as they connect. The link shows the key exactly once.
- Choose the program (user): Access needs the ODBC driver, Excel and Power BI don't; see Do I need the ODBC driver?. Then one of three ways:
- Access: set up the driver („Deploy the driver to workstations“) and link a table („Link a table in Access“, both documentation of the ODBC driver)
- Power BI: Load data into Power BI Desktop
Result: The user reads data from your database without a connection of their own to SQL Server; the database filters, not the program.
7. Secure it
Who, where: Gateway administrator in the administration app.
Decide where people may sign in from and with what proof: the sign-in policy (for example a second factor from outside), the people's second factor, and access by location. For details, see „Define the sign-in policy“, „Set up or reset the second factor“, and „Set up a person's access by location“ (documentation of the administration app).
Keys for Excel and Power BI: Excel and Power BI can't ask for a second-factor code. If the sign-in policy requires one for a user from step 6, their connection fails from then on. Give such users a key with the purpose Arbeitsplatz (Workstation) and select Nur lesen (Read only) and Ohne zweiten Faktor (No second factor) in its form; limit it to networks if needed. For a single appointment, use the purpose Präsentation auf Abruf (Presentation on demand). See „Create a person and issue a key“ (documentation of the administration app).
Result: Sign-in by location and device is in place, and the users from step 6 keep working.
8. Next
Who, where: everyone, as needed.
- Everyday administration, troubleshooting, and reference: „Administrator guide for WKL Database Gateway Manager“ (documentation of the administration app)
- Running the service: Operations
- Evidence for audits (CRA, NIS2, ISO 27001): the „Security and evidence“ chapter of the administration app documentation
- For developers, not needed for setting up: Your first request over HTTP