Connect a data source

A data source is a database that the data service publishes under a name. Users can reach its tables and views only after you've created the data source and a rule names it.

In the Datenquellen (Data sources) area, you create a data source: a name, the Provider for the kind of database, and the connection in the fields that provider defines, such as server, database, and sign-in for SQL Server. You don't write a connection string; the provider builds and checks it. Prüfen und speichern (Check and save) saves the data source and tests the connection right away. If the test fails, the app tells you the cause, the next step, and, where the database administrator has to set something up, the script for it.

The service applies every change without a restart. It stores the connection encrypted and never shows a password again.

1. In the navigation bar, open Datenquellen.

2. In the command bar, select Neu (New) and fill in the form on the right:

   Field	What to enter
   Name	The name users will use for the database, for example fachdaten. The same rules as an ordinary SQL Server identifier apply: it starts with a letter or _, followed by letters, digits, _, @, #, or $, up to 128 characters; no spaces and no hyphens. Letters from any script are allowed. Names aren't case-sensitive: Fachdaten and fachdaten are the same name. The form checks exactly the service's rule (Service configuration (reference), subsection “Naming rule for the alias” (data service documentation)); the service rejects a name that breaks it with “'<Name>' ist kein zulaessiger Aliasname” (… is not a valid alias name)
   Provider	The kind of database, for example Microsoft SQL Server. The list offers only the providers the service has installed and can use. The connection fields appear below it
   Verbindung (Connection)	The fields of the selected provider, in groups; “Erweitert” (Advanced) starts collapsed. Below the fields, a line explains the field the cursor is in. An empty field shows the default that applies in gray. For SQL Server, see the next table
   Kennwort (Password)	Only if the service signs in with a SQL login. What you type stays masked; the password is stored sealed and never shown again
   Aktiv (Active)	Selected. A data source that's turned off keeps all its rules but doesn't exist as far as users are concerned; this is meant for maintenance and migrations
   Personen (People)	Unter der Anmeldung der Datenquelle (Service) (Under the data source's login): requests run under the login of the connection. Unter der eigenen Anmeldung der Person (Impersonate) (Under the person's own login): requests from a person who has their own SQL Server login run under that login. Below the list, a line explains what the selected option does
   Durchreiche (Pass-through)	Select this only if users should send their own SQL statements (pass-through queries); their rule also needs the sql right
   Anmeldung mit SQL-Name und Kennwort erlauben (Allow sign-in with SQL name and password)	Select this only if people should sign in to this data source with the name and password of a SQL login

   The connection to SQL Server:

   Field (group)	What to enter
   Server (Server)	The SQL Server, with an instance (sql01\prod) or a port (sql01,1433); required
   Datenbank (Database) (Server)	The database the service publishes under the name, for example Fachdaten; required
   Windows-Anmeldung (Windows sign-in) (Anmeldung und Sicherheit, Sign-in and security)	Selected: the service signs in with its own Windows account (see Notes). Otherwise, enter the Benutzer (User) of a SQL login and its Kennwort (Password) below the fields
   Verschlüsselung (Encryption) (Anmeldung und Sicherheit)	True (default), False, or Strict
   Serverzertifikat vertrauen (Trust server certificate) (Anmeldung und Sicherheit)	Select this only in test environments: the server's certificate is then accepted without checking it
   Zweck der Verbindung (Application intent), Failover-Partner (Failover partner), Mehrere Subnetze (Multi-subnet failover) (Server)	For readable replicas (ReadOnly), database mirroring, and availability groups
   Zeitlimit Verbindung (s), Zeitlimit Anweisung (s), Anwendungsname (Connect timeout, command timeout, application name) (Erweitert)	Rarely needed; empty means the defaults apply

   The login of the connection determines what the service can do in the database. Give that login only the permissions the data source's rules need together (for example, reading and writing the published tables) and no server roles such as sysadmin: whatever the login can do, anyone could reach through a mistake in a rule.

3. At the bottom of the form, select Prüfen und speichern (Check and save). If a required field is empty, the form names it and doesn't save. Otherwise, the service checks everything you entered; if something is invalid, it rejects the entry with a message and nothing is saved. The app then tests the connection right away. If the data source is reachable, a short message shows how long it took. When a data source changes, the service reloads the database's structure (its catalog) by itself.
4. If the test fails, the Verbindung prüfen (Test connection) window opens; the data source is saved anyway. It says in plain words what's missing and what to do next; the server's own message is below, under Einzelheiten (Details). If the database administrator has to set something up, the window shows the script for it (see Notes): pass it on with Skript kopieren (Copy script), Als .sql speichern … (Save as .sql …), or Per Mail senden … (Send by mail …). Once it has run, select Erneut prüfen (Test again); you don't have to fill in the form again.
5. Later, you can test a data source without changing it by selecting Verbindung prüfen in the command bar. The message shows the duration, the SQL Server version, the database, and the login, or it opens the Verbindung prüfen window as above.
6. Give users access through a rule; see Create a rule. The rule names the data source in its database attribute.

Result: The overview lists the data source and whether it's reachable. Users with a matching rule can reach it under its name.

• The service's Windows sign-in: With Windows-Anmeldung, the service signs in under its service account, for example NT SERVICE\Wkl.Data.Gateway; a SQL Server on another computer sees the computer account of the service's machine instead (FIRMA\SRV-DATEN$). Until SQL Server has a login for that account and a user in the database, the test fails. The script in the Verbindung prüfen window names the account the service itself reports, and only the permissions the data source's rules need: reading always, writing if a rule writes, EXECUTE if a rule allows procedures or functions, and IMPERSONATE if people work under their own login. It only creates what's missing, so it can run more than once. Background: Install the data service, section “Notes” (data service documentation).

• What the test reports:

  Cause	Next step
  SQL Server doesn't know the login or rejects it	With Windows sign-in, send the script to the database administrator; with a SQL login, check the user and password
  The database is missing or not accessible to the login	Check the database name; if it's right, send the script to the database administrator
  A permission is missing in the database	Send the script to the database administrator
  The server can't be reached	Check the server name, instance or port, that the SQL Server service is running, and the firewall
  No answer in time	Have the server's load checked, or increase Zeitlimit Verbindung (s) (Connect timeout)
  The server can't read the database	A fault on the database server: check the drive, restart SQL Server (database administrator)
  The settings can't be used	Correct the data source's provider and connection

  The service classifies the cause, independently of the server's language; see HTTP: administration (reference), section “Connection test and service account” (data service documentation).

• Changing the connection: Change only the fields concerned. The password field stays empty when you edit; if you leave it empty, the service keeps the saved password. If you don't touch the connection, for example when you only turn Aktiv off, it stays as it is.
• Erweitert (Advanced): This group holds the Verbindungszeichenfolge (nur schreiben) (Connection string, write-only), for a provider without fields of its own or a setting the fields don't offer (a value then replaces the whole connection), and the Dialekt (Dialect) of older data sources. A data source with the dialect mssql shows the fields of Microsoft SQL Server and can be saved unchanged.
• New tables or columns in the database: The service reads a database's structure on first access and caches it for 5 minutes. To make a change visible right away, select the row and choose Weitere (More) > Katalog neu laden (Reload catalog).
• Table settings (arrow at the start of the row, tab “Tabellen-Einstellungen” (Table settings), Neu (New)): you pick the setting from a list. Gleichzeitige Änderungen erkennen (row_stamp) (Detect concurrent changes) makes tables without a row version editable in a way that lets the service detect a concurrent change by someone else and reject it instead of overwriting it (see Make tables without a row version editable (data service documentation)). Tabellen ohne Schlüssel bearbeitbar machen (keyless_write) (Make tables without a key editable) allows writing to tables without a key. Tabellen (Muster) (Tables (pattern)) names the tables: *, dbo.*, or dbo.Table. Then relink the tables in Access.
• Impersonation block list (tab in the Identitäten (Identities) area): logins and server roles the service never switches to, such as sysadmin; see Database identities.
• Removing a data source: First change the rules that name it, then delete the data source. The service won't delete a data source as long as a rule names it.

• Create a rule
• Set up database identities
• Reference: Connection properties of a provider (reference) (data service documentation), Administration store and master key (data service documentation), Make tables without a row version editable (data service documentation), Sign-in and identity pass-through, section “Identity pass-through to SQL Server” (data service documentation)