Set up sign-in with a SQL Server login

If you've connected to SQL Server with a name and password so far, you sign in to the service with the same login. SQL Server checks the password itself, and the statements run under this login—permissions, column defaults, and triggers apply as they do with a direct connection.

Three things belong together: the method in the configuration file (AuthMode with SqlLogin), the switch on the data source (SQL-Anmeldungen (SQL Server logins) or AllowSqlLogins), and a mapping of logins to rules. The order depends on where data sources and mappings are kept:

• With an administration store, turn on the method and restart first, then set the switch and the mapping in the administration. The administration accepts neither until the method is on.
• Without an administration store, put all three in the configuration file, then restart once. If one of them is missing, the service doesn't start.

Rules and responses: Sign-in: SQL Server login.

1. In the configuration file, add SqlLogin to AuthMode, and keep the methods that are already listed there. Example with an administration store:

   "Security": { "AuthMode": "ApiKey, PersonalKey, SqlLogin" }

   Without an administration store there are no personal keys, and with PersonalKey the service doesn't start. There, the example is "ApiKey, SqlLogin".

   • With an administration store: Restart the service now. It starts and logs that SQL Server sign-in isn't usable yet. Until step 2 is done, a sign-in with name and password ends with 401 without the service contacting SQL Server. Between steps 2 and 3, SQL Server checks the password, but the request is refused because no rule is mapped to the login yet.
   • Without an administration store: Don't restart yet; steps 2 and 3 go into the same file.
2. Enable the data source.
   • With an administration store: Datenquellen (Data sources), select the data source, turn on SQL-Anmeldungen in the form, switch back to the list. The change takes effect immediately.
   • Without an administration store: "AllowSqlLogins": true on the data source's entry.
3. Map logins to rules (* stands for every login). Each rule you name must already exist.
   • With an administration store: Zuordnungen (Assignments), tab SQL-Anmeldungen; see “Assign SQL logins” (documentation of the administration app). The mapping takes effect immediately; no restart is needed.

   • Without an administration store: Add the mappings under Security, and restart the service. Client names the Id of a rule under Security:Clients; if it's missing, the service doesn't start:

     "Security": {
       "SqlLogins": [
         { "Login": "*",       "Client": "fachdaten-lesen"  },
         { "Login": "pflege1", "Client": "fachdaten-pflege" }
       ]
     }

4. Connect the client with name and password—the ODBC driver with UID and PWD—over https or from the service's computer.
5. Check: a read with name and password returns 200, a wrong password 401. If pass-through is enabled for the login, a custom statement SELECT SUSER_SNAME() returns the login's name. The service limits failed attempts: at most 5 per login and alias and 20 per origin in one minute, then 429, even with the correct password—so when trying it out, wait a minute.

• Only over a protected connection: HTTPS, a trusted proxy with X-Forwarded-Proto: https, or a request from the local computer. Otherwise the service responds 403 without trying the password; see Troubleshooting.
• SQL Server needs mixed-mode authentication. An expired password results in 401; it can't be changed through the service.
• Every login has its own connection pool; many people signed in at the same time occupy correspondingly many connections.
• A login that several people use with one password isn't recommended—that's what “Set up database identities” (documentation of the administration app) is for.

• Sign-in and identity pass-through