Driver connection string (reference)
The connection string – or the values of the same name in a data source (DSN) – determines which OData service the driver contacts and how it signs in. This page lists every keyword with its default and effect; you need it when you create a link by code, deploy a data source, or establish a connection in .NET or VBA.
Syntax
DRIVER={Wkl OData};URL=<service-root>[;<keyword>=<value>]…
DSN=<data-source-name>[;<keyword>=<value>]…
Keywords are case-insensitive. A value containing a semicolon goes in braces, as with any ODBC
driver. The form of the string and the roles of DRIVER, DSN, UID, and PWD are defined by the
ODBC specification (Microsoft:
SQLDriverConnect);
all other keywords are this driver's.
Only URL is required (or a data source that names it).
Parameters
| Keyword | Default | Meaning |
|---|---|---|
DRIVER | – | Registered name of the driver, {Wkl OData}. Required if no DSN is specified |
DSN | – | Data source from which the driver reads the other values |
FILEDSN | – | File data source; the Driver Manager reads it and passes its values on |
DESCRIPTION | – | Description of a data source in the setup dialog; no effect on the connection |
URL | – | Root address of the service under which $metadata is located, http or https. Required; if it's missing, the connection fails with 08001 |
DATABASE | value of the data source, otherwise the service root | Separates the connections in Access; the driver supplies it itself, see Return value. The setup dialog writes it into every data source |
AUTH | – | Sign-in method: WINDOWS, BASIC, or KEY. Any other value prevents the connection |
UID | – | Name of a login with a password (HTTP Basic), for example a SQL Server login that the service checks |
PWD | empty | Password for UID; with AUTH=KEY, the personal key |
SAVEPWD | – | Written only by the setup dialog: 1 if “Kennwort speichern” (Save password) or “Schlüssel speichern” (Save key) was selected. The value itself goes into Windows Credential Manager, never into the data source |
APIKEY | – | Service key in plain text. Not for links in Access, see remarks |
APIKEYENV | – | Name of an environment variable that holds the service key. If it's empty, the connection fails with 28000 |
APIKEYHEADER | X-Api-Key | Header in which the service key and personal key travel |
TOTP | – | Code of a second factor for the service's first challenge, for applications without a dialog. Valid once and for 30 seconds |
SESSION | – | Second-factor session that the application opened itself. Only over https or to your own computer |
ROLE | – | Database identity under which the service executes the statements if it offers several. At most 128 characters; not a secret |
APP | – | Application and its version as <product>/<version>, for example APP=CustomerManager/2.4.1. The driver sends it with every request in the header X-Wkl-Application; a service with database updates uses it to hold back an update that breaks older versions while such a version is still asking. Product of letters, digits, ., - and _, at most 64 characters; version of two to four numbers. A value of another form, such as APP=Microsoft Office from a SQL Server link, is not sent and the connection still succeeds (a line in the trace). A statement by the application, not a proof |
PASSTHROUGH | as specified by the service | Address for statements that can't be translated into OData; absolute or relative to the service root. If it's missing, the address that the service names in $metadata applies (Store.PassThrough) – but only with the same scheme, host, and port as URL, so that a schema document can't redirect the sign-in elsewhere. OFF turns both off |
TIMEOUT | 60 | Seconds per request if the application sets no time limit for the statement; 0 means no limit. A negative number or text: the connection fails with 08001. A time limit set by the application (SQL_ATTR_QUERY_TIMEOUT, in ADO.NET CommandTimeout) takes precedence; if the application sets 0 there, no limit applies |
RETRY | ON | OFF reports a dropped connection immediately as 08S01 instead of retrying the request; for a service that doesn't honor Idempotency-Key. Any other value: 08001 |
MAXURLLENGTH | 2000 | Longest address of a read request. A longer one goes as POST …/$query with the options in the body; 0 sends every read request with options that way. Not a number: the connection fails with 08001 |
TRACEFILE | – | File for the driver's tracing, see Tracing |
Environment variables that the driver also reads:
| Variable | Effect |
|---|---|
WKL_ODATA_ODBC_TRACE | File for tracing if the connection names no TRACEFILE |
WKL_ODBC_DIALOG_THEME | light or dark: appearance of the dialogs regardless of the Windows app mode |
Return value
SQLDriverConnect returns the connection string as it came in, with four exceptions. When linking,
Access stores this returned string in the linked table; a string that code later sets itself
(TableDef.Connect with RefreshLink) stays as set.
TOTP and SESSION are omitted. The string that Access stores for a linked table doesn't
contain them, and the trace doesn't show their values.
ROLE is added if the user chose an identity in the dialog.
After the sign-in dialog, the driver returns what the user left in the dialog, such as AUTH and
UID. It omits APIKEY unless the application passed it itself, the password if the user saved it
in Credential Manager, and values that are already stored unchanged in the data source.
DATABASE is added. If the string names no DATABASE, the driver appends one: the value that
the data source stores, otherwise the service root (DATABASE=<service-root>). As a result, links to
different service roots carry different values, and Access separates them – also through data
sources. A string that Access adopts without connecting, or that
code sets with TableDef.Connect, doesn't reach the driver; if you deploy such strings, set
DATABASE yourself, different per service root.
Remarks
Precedence. Values of the connection string take precedence over those of the data source. A
name and password that an application passes to SQLConnect count as UID and PWD and take
precedence over both.
Which sign-in applies.
| Settings | Sign-in |
|---|---|
none of AUTH, UID, PWD | service key from APIKEY/APIKEYENV, otherwise none |
AUTH=WINDOWS | Windows account of the application (HTTP Negotiate: Kerberos, otherwise NTLM) |
UID (with or without AUTH=BASIC) | name and password (HTTP Basic) |
AUTH=KEY | personal key from PWD, otherwise from Credential Manager |
The methods are those of HTTP: Basic according to RFC 7617
(name and password, encoded as UTF-8 by the driver), Negotiate according to
RFC 4559 (Kerberos or NTLM through the Windows sign-in). A
key travels in a header that no standard defines; its name is configurable (APIKEYHEADER). The
second-factor codes are time-based one-time passwords according to
RFC 6238, as generated by an authenticator app; the driver
only passes the code on, and the service checks it.
The data service Wkl.Data.Gateway also accepts a personal key as an HTTP Basic password and doesn't
check the name in that case. Its documentation recommends the name pkuser for this
(UID=pkuser;PWD=wkl_…). With this driver, AUTH=KEY is the intended route; with it, the driver also
retrieves the key from Credential Manager.
Password, key, and session only when protected. The driver sends a password, a personal key,
and a session only over https or to your own computer – to every address of the connection,
including PASSTHROUGH. Otherwise the connection fails with 28000; if a service asks for a second
factor over http, the driver sends neither the code nor the session. Only the addresses of the
connection receive the Windows sign-in (AUTH=WINDOWS). The driver doesn't follow an HTTP redirect
to another host, port, or scheme; see
Security model.
Saved password. If no setting names a password, the driver uses the one the user saved in the
dialog. It's stored in Windows Credential Manager under the target
Wkl.Data.OData.Odbc:<Name>@<service-root>, a personal key under
Wkl.Data.OData.Odbc:(persönlicher Schlüssel)@<service-root>. A data source never contains a
password.
No secret in an Access link. Access stores the connection string of a linked table in the
database file; every copy of the file would carry APIKEY or PWD with it. Use AUTH=WINDOWS,
AUTH=KEY with a saved key, or APIKEYENV. Access saves a password only if the link is explicitly
meant to (dbAttachSavePWD).
Second factor. If the service requires a second factor (401 with the Wkl-Totp challenge), an
application with a dialog asks for the code (see the
how-to guide); one without a dialog uses TOTP or
SESSION or receives the rejection 28000 with the service's message. The session travels in the
X-Wkl-Session header and applies to all connections of the process with the same sign-in. The
address that the challenge names must have the same origin as URL; otherwise the driver doesn't
send the sign-in there.
Database identity (ROLE). If the service offers a person several identities (for example the
shared logins vertrieb and marketing), the driver sends the name from ROLE with every request
(header X-Wkl-Role, percent-encoded). If the choice is missing or isn't allowed, the service
answers 403 with the list of possible ones (X-Wkl-Roles):
| Application | Behavior |
|---|---|
with a dialog (window handle passed, not SQL_DRIVER_NOPROMPT) | selection list Identität wählen (Choose identity) over the application window; the request then goes again with the choice – even a writing one, because the service refuses before it executes anything. The driver returns the choice as ROLE=; from then on, Access opens the link with it without asking |
| without a dialog (Power BI, Excel, scripts, ADO.NET) | rejection 28000 with the service's message, which names the possible identities; the application passes ROLE= |
The selection list is a separate dialog after sign-in, not part of the sign-in form: which identities the service offers depends on who signed in.
Access shares connections by DATABASE. The rule for setup is under
Several service roots in one file;
here is how Access behaves in detail. For all links of a process whose DATABASE is the same, ACE
opens one driver connection – the one opened first – and uses it for all of them, regardless of
what the other keywords say. For two links to the same table from two service roots:
| The two connection strings differ in … | Connections | Result |
|---|---|---|
URL and DATABASE (including the one supplied by the driver) | 2 | correct: each link reads its own service root |
only URL, DATABASE the same | 1 | wrong: both read the first service root; Access even rewrites the stored connection of the second link to the first URL |
only DATABASE, URL the same | 2 | a separate connection each |
only TIMEOUT | 1 | the TIMEOUT of the first connection applies |
only ROLE | 1 | both run under the identity of the first connection, even when writing |
only PASSTHROUGH | 1 | see Set up pass-through queries |
Consequences: Two different services or service roots must never carry the same DATABASE
value. A second identity (ROLE) in the same Access session needs its own DATABASE value, for
example DATABASE=fachdaten-vertrieb; otherwise the driver doesn't evaluate DATABASE. The driver
learns nothing about the sharing and can't prevent it.
Data sources (DSN, FILEDSN). Through a data source, the driver returns that data source's
DATABASE, otherwise the service root, and the setup dialog writes the service root into every new
or changed data source. Two data sources pointing to two service roots therefore carry different
values.
Time limits. The driver reports an exceeded limit as HYT00, and likewise the service's time
limit (504). An expired time limit isn't retried: the service may still be working.
Interrupted connection. If the connection drops without a response, the driver retries after 1
and 3 seconds: reads always; adding, changing, deleting, and a custom statement read as a whole with
Idempotency-Key – the service answers the retry with the stored first response, so there's no
second record and no write conflict for a change that went through. A streamed custom statement never:
rows already supplied can't be taken back; the application receives 08S01 instead of a silently
truncated result. It doesn't retry the commit of a transaction either. With RETRY=OFF, it doesn't
retry at all. Which commitment applies to which service is described under
Commitments per kind of service.
Example
Link without a secret in the file, with Windows sign-in and the service's pass-through:
ODBC;DRIVER={Wkl OData};URL=https://daten.example.com/odata/v4/fachdaten/dbo/;AUTH=WINDOWS
With a personal key that the user saved in the dialog:
ODBC;DRIVER={Wkl OData};URL=https://daten.example.com/odata/v4/fachdaten/dbo/;AUTH=KEY
Script without a dialog, service key from the environment, pass-through explicitly turned off:
DRIVER={Wkl OData};URL=https://daten.example.com/odata/v4/fachdaten/dbo/;APIKEYENV=FACHDATEN_SCHLUESSEL;PASSTHROUGH=OFF;TIMEOUT=120
Applies to
Driver Wkl OData in both bitnesses (x86, x64); any ODBC application. The environments the driver
supports are listed under
Prerequisites.
Exceptions
| SQLSTATE | Condition |
|---|---|
08001 | The connection string isn't suitable for connecting: URL is missing or isn't an http/https address; PASSTHROUGH, MAXURLLENGTH, TIMEOUT, or RETRY is invalid; ROLE is longer than 128 characters. The message names the keyword |
28000 | The sign-in settings of the connection can't be used as given: APIKEYENV names an empty variable; password, personal key, or SESSION to an unprotected address; contradictory sign-in settings (below); unknown value of AUTH. Nothing has been sent |
28000 | The service refuses the sign-in, and the application allows no dialog – for example because of a missing second factor or a missing identity choice; the message is the service's |
08S01 | The service isn't reachable, including when the TLS connection fails because of the certificate (see 08S01 "UntrustedRoot"), or it redirects to another origin |
HYT00 | Time limit exceeded, in the driver or at the service (504) |
IM002 is reported only by the Driver Manager: it doesn't find the driver or the data source for the
bitness of the application (see IM002: The driver isn't found).
The states 08001 (“Client unable to establish connection”) and 28000 (“Invalid authorization
specification”) are the ones that the ODBC specification provides for SQLDriverConnect (Microsoft:
SQLDriverConnect, Diagnostics).
Rejected with 28000 instead of being resolved by a driver rule:
PWD without UID (except with AUTH=KEY), UID together with AUTH=WINDOWS or AUTH=KEY,
AUTH=BASIC without UID, a name with a colon (HTTP Basic separates name and password at it).
See also
- How-to guide: Sign in with a personal key and a second factor
- How-to guide: Set up pass-through queries
- Concept: How the driver translates statements
- Tutorial: Link a table in Access