Skip to content

OP-01: Get SQL Server connection string

This operation composes a SQL Server connection string for the requested server, instance and user, proves that it connects by running a constant probe query, caches it for the lifetime of the process, and returns it to the caller. A repeat request for the same key still reads the Environment API template, but is then answered from the cache without retrieving the secure credential or contacting the database.

Field Value
Operation ID OP-01
Trigger HTTP request from an authorized caller
Entry endpoint GET /v1/Connections/SQL/{server}/{instance}/{username}
Owning component ConnectionsController.GetSqlConnectionString, delegating to ConnectionManager.GetSqlConnectionString
Inputs Route values server, instance, username (inferred route binding); Environment API template sqlconnectiontemplate and a secure credential for the composed key
Outputs ActionResult<string> holding the credential-bearing connection string
Failure response 404 for a missing secure setting, 500 for probe or credential failures, produced from ConnectionManagerException by package middleware† (see Error handling)

Steps

  1. Authorize the caller against resource DBConnAPIRetrieveResource (BR-DBC-025)
  2. On failure: a denial produced by the package authorization handler†; the exact status is not visible in source**
  3. Write the audit log entry with the key parts, the caller's UserId, the AuthenticationScheme and the time (BR-DBC-022)
  4. Build the cache key SQL.<server>.<instance>.<user>, which is case-sensitive (BR-DBC-002)
  5. Build the lower-cased secure-setting key sql_<server>_<instance>_<user> (BR-DBC-005)
  6. Read the template sqlconnectiontemplate from group dbconnectionapi on every request, before the cache check, and replace {SERVER} and {INSTANCE} (BR-DBC-007)
  7. A missing template raises an unhandled null reference; the status is not visible in source**
  8. Look the key up in the in-memory cache (BR-DBC-001)
  9. On a hit: return the cached string with status 200 and stop. Only the secure-credential retrieval, validation, the probe and cache insertion are skipped; the template lookup in step 5 already happened
  10. On a miss, check that the secure setting exists (BR-DBC-009)
  11. On failure: ConnectionManagerException with status 404, rendered by package middleware†
  12. Verify the backoff configuration (BR-DBC-011)
  13. Decrypt the secure setting and split it at the first pipe into user and password (BR-DBC-010)
  14. A missing pipe raises ArgumentOutOfRangeException outside the retry loop; the status is not visible in source**
  15. Substitute {USER} and {PW} into the template (BR-DBC-008)
  16. Probe the composed string, up to four attempts with 2 s, 3 s and 5 s waits (BR-DBC-012)
    • Each attempt decodes the legacy token for the string under test only (BR-DBC-019)
    • The probe opens a unit of work through IUnitOfWorkFactory and runs SELECT 1; a non-null result means valid (BR-DBC-018)
    • A false result counts as a failure with status 500 (BR-DBC-014)
    • Permanent errors (for example Login failed) stop the loop at once (BR-DBC-013)
  17. After the final failure, map the error to a status (BR-DBC-016, BR-DBC-017)
    • A typed exception keeps its status; a TNS alias error maps to 400 (BR-DBC-015) and is possible here only by inference*
  18. On success, log the validation and add the string to the cache under the lock (BR-DBC-003, BR-DBC-004)
  19. Return the connection string with status 200

Origin: Steps 1 and 12 depend on SAIF.NetCore.ServiceExtensions† (authorization handler and DynamicErrorHandler), and step 11 uses IUnitOfWorkFactory from SAIF.NetCore.NHibernate†, both retrieved via the Legends CLI RAG service and executed within this operation.

Process flow

The following diagram visualizes this operation's control flow.

flowchart TD
    A["Authorize DBConnAPIRetrieveResource"] -->|"Denied"| Z["Authorization denial, status not visible in source"]
    A --> B["Audit log, build cache key and secure-setting key"]
    B --> T["Read Environment API template (every request)"]
    T -->|"Template missing"| X["Unhandled/package error, status not visible in source"]
    T --> C{"Key in cache?"}
    C -->|"Yes"| R["Return cached string, 200"]
    C -->|"No"| D{"Secure setting exists?"}
    D -->|"No"| E["404 ConnectionManagerException"]
    D -->|"Yes"| F["Decrypt credential, split at first pipe"]
    F -->|"No pipe"| X2["Unhandled/package error, status not visible in source"]
    F --> G["Compose string"]
    G --> H["Probe SELECT 1, up to 4 attempts"]
    H -->|"Valid"| I["Add to cache and return 200"]
    H -->|"Permanent error or attempts exhausted"| J["Map to 400* (inferred) or 500"]

Transaction and side effects

Aspect Finding
Unit of work IUnitOfWorkFactory (SAIF.NetCore.NHibernate), a throw-away unit of work per probe attempt; Begin() is never called, so there is no explicit transaction
Commit point None
Records written None; the probe reads a constant (data model)
Downstream calls SAIF Environment API through IEnvironmentClient (template, existence check, secure read); the caller-named SQL Server through the probe
Repeat-call behavior A repeat call for an identical key reads the template again and is then served from the cache, skipping the secure read and probe; the cache is never invalidated (BR-DBC-004). Each distinct key leaves one session factory in memory for the process lifetime†

Logging

Surface Written content
ISaifLogService (Info) Audit entry per request: key parts, UserId, AuthenticationScheme, timestamp (BR-DBC-022); a validation success entry with the attempt count
ISaifLogService (Error) Each failed probe attempt with the exception and message

Error handling

  • Secure setting missing: 404 from ConnectionManagerException, rendered by package middleware (DynamicErrorHandler†); the message names the key and track
  • Invalid credentials, database unavailable, cursor exhaustion, network interruption, probe returned false or any other failure: 500 from ConnectionManagerException through the same middleware; the message can include the raw provider text
  • Missing pipe in the credential or missing template: unhandled exceptions handled by package middleware; the status is not visible in source**
  • Authorization failure: produced by the package authorization handler†; the status is not visible in source**
  • There are no [ProducesResponseType] attributes, so 400, 404 and 500 are undeclared in the OpenAPI document

Note: Statements marked with † are derived from dependency library documentation via the Legends CLI. Statements marked with * were inferred from code structure or contextual evidence. Statements marked with ** could not be confirmed; treat as unknown until verified.

Sources

  • DBConnectionAPI/DBConnectionAPI/Controllers/ConnectionsController.cs
  • DBConnectionAPI/DBConnectionAPI/Services/ConnectionManager.cs
  • DBConnectionAPI/DBConnectionAPI/Services/IConnectionManager.cs
  • DBConnectionAPI/DBConnectionAPI/Exceptions/ConnectionManagerException.cs
  • DBConnectionAPI/DBConnectionAPI/EnvironmentKeys.cs