# Testing Against a Real Live Database From a Sandboxed Session

## Why this exists

A sandboxed dev/agent session typically has no direct network route to the shared dev/QC SQL
Server box (`217.216.78.142:1433`) — a plain `nc`/direct-connect attempt just times out. The only
way in is an SSH tunnel through a real login on that box. That much is simple. What's *not*
obvious — and cost a full debugging pass to discover — is that GB5 has **two independent
connection-resolution paths**, and only one of them respects a locally-tunneled connection string:

1. **`Gb5SystemDTO:Gb5System`** (appsettings.json) — read directly as a literal ADO.NET connection
   string by `GB5Shared.Validation.Validation` and as the seed value `ApplicationConnection`
   decrypts its own system-DB password from. Point this at `localhost,<tunnel-port>` and it works
   immediately — this path is fully config-driven, nothing else involved.
2. **Everything else** (any `LoginDTO.DatabaseName` resolved through `IQueryExecutor`/
   `ApplicationConnection.DatabaseConnectionObjectConnectionName`) — looked up in `MSERVERCONFIG`
   by `CONNECTIONNAME`, which points at an `MSERVER` row, whose `SERVERIP` column is what actually
   gets used to build the real connection string. If that `MSERVER` row's IP is the real
   `217.216.78.142`, the app will try to connect there **directly**, ignoring your local tunnel
   entirely — and hang until the connection string's own `Connection Timeout` (120s by default)
   expires. A bounded `dotnet run` check with a short sleep window will look like it "succeeded
   silently" when it's actually just still hanging — don't trust silence as success; either wait
   out the real timeout or (much better) prove it with a real request (see below).

## The fix: route through the existing `TUNNEL` MSERVER row

Someone already solved this before — `MSERVER` has a row for exactly this purpose:

```
SERVERID = -777800, SERVERNAME = 'TUNNEL', SERVERIP = '127.0.0.1,15433'
```

A sibling `MSERVERCONFIG` row (`GB5DEMO_TUNNEL`) already points at it. To make any new
`CONNECTIONNAME` reachable from a sandboxed session, point its `MSERVERCONFIG.SERVERID` at
`-777800` instead of a real server's own `MSERVER` row:

```sql
UPDATE MSERVERCONFIG SET SERVERID = -777800 WHERE CONNECTIONNAME = '<YourConnectionName>';
```

Then open the SSH tunnel with **two** local forwards — one for whatever port your own
`Gb5SystemDTO:Gb5System` uses, one for `15433` (matching the `TUNNEL` row exactly), both to the
remote box's real `1433`:

```bash
sshpass -p '<ssh-password>' ssh -o StrictHostKeyChecking=accept-new -o ConnectTimeout=10 \
  -M -S /tmp/<unique-control-socket-name> -fN \
  -L 14330:localhost:1433 \
  -L 15433:localhost:1433 \
  <ssh-user>@217.216.78.142
```

Close it when done: `ssh -S /tmp/<unique-control-socket-name> -O exit <ssh-user>@217.216.78.142`.
Use a **uniquely named** control socket per session — concurrent sessions on the same machine can
otherwise collide on the same socket path/local ports.

## The other gotcha: password encryption format

`MSERVERCONFIG.DATABASEPASSWORD` (and `Gb5SystemDTO:Gb5System`'s own `Password=` segment, when
read by `ApplicationConnection` rather than `Validation`) must be **AES-256-GCM encrypted**, not
plaintext:

- Key: `SHA256("GB5")` (32 bytes)
- IV: 12 random bytes, generated fresh per encryption
- Tag: 16 bytes (128-bit), appended immediately after the ciphertext
- Wire format: `Base64(IV ‖ ciphertext ‖ tag)`
- Implementation: `GB5Shared.Connection.ApplicationConnection.Encrypt(plaintext, key = "GB5")`
  (BouncyCastle `GcmBlockCipher`) — but `System.Security.Cryptography.AesGcm` (built into .NET)
  produces byte-identical, fully interoperable output using the same key/IV/tag construction, so a
  throwaway console snippet is enough to produce a working ciphertext without needing to reference
  GB5Shared directly:

  ```csharp
  using System.Security.Cryptography;
  using System.Text;

  byte[] keyBytes = SHA256.HashData(Encoding.UTF8.GetBytes("GB5"));
  byte[] iv = new byte[12];
  RandomNumberGenerator.Fill(iv);
  byte[] plaintextBytes = Encoding.UTF8.GetBytes("<real password>");
  byte[] ciphertext = new byte[plaintextBytes.Length];
  byte[] tag = new byte[16];
  using (var aesGcm = new AesGcm(keyBytes, 16))
      aesGcm.Encrypt(iv, plaintextBytes, ciphertext, tag);
  byte[] result = [.. iv, .. ciphertext, .. tag];
  Console.WriteLine(Convert.ToBase64String(result));
  ```

  Always round-trip-verify (decrypt what you just encrypted) before trusting it in a real config
  value — a subtle IV/tag-length mismatch produces a ciphertext that looks plausible but fails
  silently at decrypt time.

**RESOLVED** (tracker §25): `Validation.cs` previously read `Gb5SystemDTO:Gb5System`'s password as a
literal, unencrypted string, while `ApplicationConnection.Gb5SystemConnectionInfo()` reading the
same config key expected it AES-GCM-encrypted. Fixed by injecting `IApplicationConnection` into
`Validation` and calling `Gb5SystemConnectionString()` (the same decrypt path `ApplicationConnection`
already uses) at both call sites that open a connection (`GetColumnMaxLengthAsync`,
`LogExceptionToDatabaseAsync`), instead of `Validation` building its own raw connection string. Both
consumers now agree: the password segment must always be AES-GCM-encrypted, never plaintext. This
note is left here (rather than deleted) so a reader who only skims this doc doesn't mistake the
still-real gotcha above (two independent *routing* paths) for a still-open password-format bug —
that part is closed.

## Don't trust "it started" or "it went silent" as proof

FastEndpoints validates the whole DI graph at `MapFastEndpoints()` time, so a clean
`Application started.` genuinely proves the DI graph resolves — that part *is* trustworthy. But it
proves nothing about whether a *specific* `LoginDTO.DatabaseName` actually round-trips to real
data. The only real proof is an actual request:

```bash
LOGIN_JSON='{"DatabaseName":"<YourConnectionName>","ClientId":-1,"UserId":-1,"DatabaseType":0,"ServerId":-1,"ServerConfigId":-1,"ServerConfigOffset":0,"RoleId":-1,"UserName":"test","LanguageId":"1"}'
curl -sS --max-time 30 -w "\nHTTP_STATUS:%{http_code}\nTIME:%{time_total}s\n" \
  -H "Login: ${LOGIN_JSON}" \
  "http://localhost:<port>/<some-AllowAnonymous-GET-route>"
```

Pick a target endpoint that's genuinely `AllowAnonymous()` with **no** `[MenuRights]` attribute (a
`[MenuRights]`-gated endpoint will 403/500 without a real `MROLEVSMENU` grant for whatever
`RoleId` you send — that's a separate, legitimate gap from DB connectivity itself, don't conflate
the two when interpreting a failed test). A fast `200` with a real (even empty) body is the only
trustworthy signal; a hang past a few seconds means the connection is still resolving through the
wrong path — check `MSERVERCONFIG.SERVERID` first, per this doc, before assuming anything else is
wrong.