# DB Settings Guide — Development vs. Release (SqlWorkbench) **Status as of 2026-09-07.** Practical settings reference companion to [Version-Management-And-Release-Flow.md](Version-Management-And-Release-Flow.md) — that doc explains the *model*; this one gives the actual rows/config keys to set, for a developer setting up a dev database against SqlWorkbench, and a Releaser cutting a real release. Everything here was directly verified against live GB5DEMO data during a 2026-09-05 debugging session, including several settings gaps that were live bugs at the time (called out in §5). --- ## 1. Two Connection-Resolution Surfaces There are two separate settings surfaces that both need to agree, and it's easy to configure one without the other: | Surface | Owns | Used by | |---|---|---| | **Framework connection resolution** | `MSERVER` + `MSERVERCONFIG` (Gb5System DB) | Every `AllowAnonymous()` endpoint's `Login.DatabaseName` → `MSERVERCONFIG.CONNECTIONNAME` lookup (`BaseEndpoint.GetLoginDTOFromRequest`); `IServerConfigCache`/`ReportConnectionResolver` | | **SqlWorkbench's own tenant registry** | `SW.MSWDBSERVER` / `SW.MSWDBMODEL` / `SW.MSWCLIENTDATABASE` (Gb5System DB, `SW` schema) | SqlWorkbench's own DDL/DML lifecycle — `ClientDatabaseProvisioner`, `ChangeRequestBLL`, `ProvisioningBLL` | `SW.MSWCLIENTDATABASE.SERVERCONFIGID` is the bridge between the two — it's how `VersionSyncBLL`/`ReportConnectionResolver` resolve a SqlWorkbench-known `ClientDbId` back to an actual `MSERVERCONFIG` row once a database is registered into the Framework side. --- ## 2. Development Settings ### 2.1 `MSERVER` — the physical SQL Server instance ```sql INSERT INTO MSERVER (SERVERID, SERVERNAME, SERVERIP, SERVERMACHINENAME, STATUS, ...) VALUES (@ServerId, 'My Dev SQL', ',', ..., 1); ``` **The one gotcha that will cost you the most time**: `SERVERIP` must be a genuinely reachable `host,port` pair *from the process that will connect* (usually `127.0.0.1,1433` from on-box, or the box's real external IP from elsewhere). A leftover test row with a placeholder/tunnel address (e.g. `127.0.0.1,15433` pointing at a tunnel that no longer exists) will silently make every connection through it fail with "Cannot reach the database server for '...' within 15s" — found live on GB5DEMO, where `Gb5System`'s own `MSERVERCONFIG` row pointed at exactly this kind of stale server entry. **Before wiring anything up to a `MSERVER` row you didn't just create, `nc -zv ` it first.** ### 2.2 `MSERVERCONFIG` — the named connection ```sql INSERT INTO MSERVERCONFIG (SERVERCONFIGID, CONNECTIONNAME, SERVERID, DATABASETYPE, DATABASENAME, STATUS, ...) VALUES (@ServerConfigId, 'MYPROJECT-DEV', @ServerId, 0 --[SqlServer], 'MyProjectDev', 1); ``` `CONNECTIONNAME` is what `Login.DatabaseName` in an API request resolves against — it does **not** have to match `DATABASENAME`. `DATABASETYPE`: 0=SqlServer, 1=Oracle, 2=Postgres, 3=MySQL (per `GB5Shared/GB5Constant/Constant.cs`). ### 2.3 `SW.MSWDBSERVER` / `SW.MSWDBMODEL` — SqlWorkbench's own registry ```sql -- A server usable by many tenants: TENANTID = -1 (shared platform sentinel) INSERT INTO SW.MSWDBSERVER (DBSERVERID, HOSTNAME, PORT, DBTYPE, DBUSERNAME, DBPASSWORD, STATUS, TENANTID, ...) VALUES (@DbServerId, '', 1433, 0, '', '', 1, -1); INSERT INTO SW.MSWDBMODEL (DBMODELID, DBMODELCODE, DBMODELNAME, DBSERVERID, DATABASENAME, STATUS, TENANTID, ...) VALUES (@DbModelId, 'MYPROJECT_BASELINE', 'My Project Baseline', @DbServerId, 'template-db', 1, -1); ``` **`TENANTID = -1` on these two tables is the shared/platform sentinel, not a mistake or a placeholder to fill in later.** `ClientDatabaseQB.GET_BY_ID`'s join explicitly does `(s.TENANTID = c.TENANTID OR s.TENANTID = -1)` — a `DBSERVER`/`DBMODEL` row is normally a single physical server/baseline shared across every tenant's own `MSWCLIENTDATABASE` row, not one row per tenant. **Do not set a real tenant's `TENANTID` here** unless you genuinely have a tenant-dedicated server — a live bug this session (`MSWDBMODEL.TENANTID` accidentally set to `1` instead of `-1`) silently broke `ClientDatabaseDAL.GetById` for every tenant except tenant `1`. `DBTYPE`: 0=SqlServer (only one actually implemented end-to-end today — Postgres/MySQL/Oracle values exist in the enum but aren't wired through `TargetDbExecutor`/`ClientDatabaseProvisioner` yet). `DBPASSWORD` here is stored encrypted (`Encryption:DbPasswordKey` config), unlike `MSWCLIENTDATABASE` below which never stores a raw/encrypted password at all. ### 2.4 `SW.MSWCLIENTDATABASE` — the dev-designated tenant row Per the standing convention documented in `GB5Solution/CLAUDE.md` §"SqlWorkbench — Dev vs. Live Database Practice": register a **dedicated non-production row** under the **same `DBMODELID`** as the project's real live database, and develop/test exclusively against it. ```sql INSERT INTO SW.MSWCLIENTDATABASE (CLIENTDBID, CLIENTDBCODE, CLIENTDBNAME, DBSERVERID, DATABASENAME, DBMODELID, CLIENTDBSTATUS, DATABASEROLE, STATUS, TENANTID, ...) VALUES (@ClientDbId, 'MYPROJECT-DEV', 'My Project Dev', @DbServerId, 'MyProjectDevDb', @DbModelId, 1, 0 --[Main], 1, @RealTenantId); ``` Unlike `MSWDBSERVER`/`MSWDBMODEL`, **this row's `TENANTID` is the real, specific owning tenant** — it is *not* a shared row, it's one tenant's one physical database. `DATABASEROLE`: 0=Main, 1=Report, 2=Archive (per `DatabaseRole` enum). Note this table genuinely has **no** `DBUSERNAME`/`DBPASSWORD` columns — all credentials for it are Vault-path-based (§2.5), never a raw stored string. After creating the row, two follow-up writes complete it (both via SqlWorkbench's own endpoints, not raw SQL, since they touch Vault and cross-reference `MSERVERCONFIG`): - `UPDATE_SERVER_CONFIG_ID` — links this `ClientDbId` to a real `MSERVERCONFIG.SERVERCONFIGID` (via `ServerConfigBLL.RegisterDatabaseAsync`), so `VersionSyncBLL`/`ReportConnectionResolver` can resolve it later. - `UPDATE_CREDENTIAL_VAULT_PATHS` — set once `ClientDatabaseProvisioner` creates the three contained SQL users (§2.5), never hand-written. ### 2.5 Contained SQL users + Vault Provisioning a fresh client database (`ClientDatabaseProvisioner.CreateContainedUsersAndStoreCredentialsAsync`) creates **three contained database users** (not server logins — a credential for one client's DB cannot authenticate against a different client's DB at all, by construction): | Role (`ClientDbLoginRole`) | Login name | DB role membership | |---|---|---| | `Dba` (0) | `{ClientDbCode}_dba` | `db_owner` | | `App` (1) | `{ClientDbCode}_app` | `db_datareader` + `db_datawriter` | | `ReadOnly` (2) | `{ClientDbCode}_readonly` | `db_datareader` only | Each password (24 random bytes, Base64) is written to Vault at: ``` sqlworkbench/clientdb/{ClientDbId}/dba-password sqlworkbench/clientdb/{ClientDbId}/app-password sqlworkbench/clientdb/{ClientDbId}/readonly-password ``` — KV v2, mount point `secret`, single key `value` holding **only the password**; the username is never stored in Vault, it's always reconstructed as `{ClientDbCode}_{roleSuffix}` at connection time. This is why `ClientDbCode` should be treated as effectively immutable once a database is provisioned — renaming it orphans the Vault-derived username. **Vault config** (`appsettings.json`, section `Vault`): `Address`, `Token` are the two that matter in practice (`MountPoint`, retry/cache settings, AppRole/Kubernetes auth exist in `GB5Shared.Vault.VaultOptions` but SqlWorkbench's own `SwModule.cs`/`Program.cs` construct the `VaultSharp` client directly, bypassing `AddGB5Vault` entirely — so those extra settings are silently inert for SqlWorkbench specifically, only the two-key path applies): ```json "Vault": { "Address": "http://127.0.0.1:8200", "Token": "root" } ``` **A sealed Vault fails every DDL/DML `ChangeRequest.Execute()` call** with `"Vault is sealed"` — confirmed live this session. `vault status` (with `VAULT_ADDR` matching the listener's real scheme/port, e.g. `http://127.0.0.1:8200` if `tls_disable = 1`) shows `Sealed: true/false` and `Threshold`/`Total Shares`. Unsealing requires the actual unseal key(s) from whoever initialized that Vault instance — there is no way around this, and it is **not something to attempt to work around** (guess, brute-force, or bypass) without the real key holder. ### 2.6 `Integration:SqlWorkbenchBaseUrl` — per-process, not shared Every process that calls SqlWorkbench's HTTP endpoints (not the direct-DB-write paths) needs its **own** copy of this config key — it is not inherited automatically just because another process has it set: ```json "Integration": { "SqlWorkbenchBaseUrl": "http://127.0.0.1:5112" } ``` Confirmed live consumers: `EntitlementSL` (`GB5Solution/Entitlement/EntitlementSL/appsettings.json`, pointing at `http://localhost:5238` in that module's own dev setup) and `FrameworkSL` (`GB5Framework/FrameworkSL/appsettings.json`, added this session for `VersionSyncBLL` → `SqlWorkbenchVersionClient` → SqlWorkbench's `GetCurrentVersion`/`GetClientDatabaseById`). On GB5DEMO, SqlWorkbench itself is hosted inside `PlatformHost` on port `5112` — point any new caller's `Integration:SqlWorkbenchBaseUrl` at that, not at a guessed port. **Do not assume this key is populated just because it's committed in-repo** — it ships as `""` by default in every module's checked-in `appsettings.json`; a real value is an environment-level override that has to be set independently per deployed process. ### 2.7 Dev-time script workflow — `MSWSCRIPTBRANCH` / `MSWDDLSCRIPT` Authoring a schema/data change day-to-day: ```sql -- One feature branch per initiative, scoped to your DbModel INSERT INTO SW.MSWSCRIPTBRANCH (BRANCHID, BRANCHCODE, BRANCHNAME, BRANCHTYPE, BRANCHSTATUS, DBMODELID, TENANTID, ...) VALUES (@BranchId, 'feature/my-change', 'My Change', 2 --[Feature], 0 --[Active], @DbModelId, @TenantId); -- Script, Draft, on that branch — test only against the dev-designated MSWCLIENTDATABASE (§2.4) INSERT INTO SW.MSWDDLSCRIPT (DDLSCRIPTID, DBMODELID, BRANCHID, OBJECTNAME, SQLSCRIPT, SCRIPTSTATUS, TENANTID, ...) VALUES (@ScriptId, @DbModelId, @BranchId, 'MyTable', '', 0 --[Draft], @TenantId); ``` `BRANCHTYPE`: Main=0, Develop=1, Feature=2, Release=3, Hotfix=4. `SCRIPTSTATUS` (`DdlScriptStatus`): Draft=0 → Reviewed=1 → Approved=2 → Deployed=3. Note: `DdlScriptQB.UPDATE` resets `SCRIPTSTATUS` back to `0` (and clears `APPROVEDBYID`) on **every** content edit — any change to an already-reviewed/approved script forces it back through review, by design. --- ## 3. Release Settings ### 3.1 `SW.MSWUPGRADEPACKAGE` / `SW.MSWUPGRADEPACKAGEDDL` — same `-1` sentinel rule **These two tables follow the identical shared-ownership convention as `MSWDBSERVER`/ `MSWDBMODEL` (§2.3) — `TENANTID = -1` for a real release package, not a specific tenant's ID.** A package/its DDL lines are a *template* applied to potentially many tenants' `MSWCLIENTDATABASE` rows under the same `DBMODELID` — they are not owned by whichever tenant happens to apply them first. Two live bugs this session were exactly this: `GET_MOST_RECENTLY_APPLIED_PACKAGE` and `GET_LINEAGE` both filtered these tables by the *querying* tenant's ID instead of allowing the `-1` sentinel, silently making every real, fully-applied package invisible to `ProvisioningBLL.GetCurrentVersion` for any tenant other than whichever one the package happened to be tagged with. ```sql INSERT INTO SW.MSWUPGRADEPACKAGE (PACKAGEID, DBMODELID, PACKAGENAME, PACKAGECODE, PKGSTATUS, SEQUENCEINCHAIN, SUPERSEDESPACKAGEID, SCOPETYPE, RELEASEVERSION, ROLLOUTTYPE, ROLLOUTPERCENT, PACKAGECONTENTTYPE, STATUS, TENANTID, ...) VALUES (@PackageId, @DbModelId, 'v4.3.0 Release', 'REL_4_3_0_PKG', 0 --[Draft], @NextSequence, @PriorPackageId, 0 --[Universal], NULL --[set at release boundary only], 0 --[Global], 100, 0 --[Schema], 1, -1); INSERT INTO SW.MSWUPGRADEPACKAGEDDL (PACKAGEDDLID, PACKAGEID, DDLSCRIPTID, SEQUENCE, TENANTID) VALUES (@LineId, @PackageId, @ScriptId, @Sequence, -1); ``` Column notes: - `PKGSTATUS` (`PkgStatus`): Draft=0 → Testing=1 → Released=2, Deprecated=3. - `SEQUENCEINCHAIN` / `SUPERSEDESPACKAGEID`: place this package in its `DBMODELID`'s ordered chain — this is what lets a tenant several releases behind upgrade as one consistent operation (§8 of the Version-Management doc). - `RELEASEVERSION`: **nullable, set only on an actual release-boundary package** — most packages in a chain (a hotfix, one client's interim feature bundle) leave this `NULL`. - `SCOPETYPE`/`SCOPEVALUE` (039): Universal=0/PartnerProduct=1/Industry=2/Country=3/Client=4 — `SCOPEVALUE` is `NULL` only when `Universal`. - `ROLLOUTTYPE`/`ROLLOUTPERCENT` (042): Global=0/Beta=1/SelClients=2/Percentage=4 (3 is deliberately skipped — mirrors `MENTITLEMENTFEATUREFLAG.RolloutType`). - `PACKAGECONTENTTYPE`/`REQUIREDFEATURECODE` (050): Schema=0 (always delivered in full, never feature-gated) vs. MetadataOrStandardData=1 (subject to feature-flag gating via `REQUIREDFEATURECODE`). ### 3.2 `ChangeRequest` execution — role selection is automatic, not configured `ChangeRequestBLL.Execute()` picks the connection role from `QueryType` (`RoleForQueryType`): `DDL` → `Dba`; `Insert`/`Update`/`Delete` → `App`; `Select`/`Other` → `ReadOnly` — capped (never loosened) by the acting user's own `SW.MSWUSERRESOURCEROLE` grant if one is configured for that user+`DbServerId`/`ClientDbId`. **No grant configured at all is fail-open** (this module is currently `AllowAnonymous()` everywhere) — don't rely on `MSWUSERRESOURCEROLE` as an access-control boundary unless every relevant user already has an explicit row. ### 3.3 `MAppVersion` — the App-release row, always manual ```sql INSERT INTO MAppVersion (AppVersionId, VersionNumber, ServiceBaseURL, FEBaseURL, IsApplicable, ...) VALUES (@Id, '4.3.0', 'https://api.example.com', 'https://app.example.com', 0 --[applicable]); ``` Use the **same version string** as the DB package's `RELEASEVERSION` whenever they're meant to be consumed as a matched pair — that's what `VersionCheck`'s `CompareVersionStrings` compares. This row is never written by any automated process; it's the Releaser's own deliberate step, same as it always has been. ### 3.4 `MDBLevelSetting.BUILDVERSION` — never hand-write this Once `feature/gb5-db-version-subscriber` is deployed (merged to `dev` 2026-09-05), this column is written **automatically**, event-driven, the moment a `ChangeRequest` for `SchemaChange`/ `DataMigration`/etc. successfully executes against a tenant — see [Version-Management-And-Release-Flow.md §4.4](Version-Management-And-Release-Flow.md#44-keeping-mdblevelsettingbuildversion-in-sync-automatically). Don't script a manual `UPDATE MDBLevelSetting SET BUILDVERSION = ...` as part of a release runbook — if it's not updating automatically, the subscriber/wiring is broken and that's what needs fixing, not a manual workaround. One real constraint to know if you ever do touch this table directly for test purposes: `BUILDNUMBER` has a live `CHECK` constraint (`CK_MDBLEVELSETTING_BUILDNUMBER`) requiring `BUILDNUMBER = CONVERT(INT, REPLACE(BUILDVERSION, '.', ''))` — e.g. `'4.1.6.48.1'` ↔ `416481` — enforced on every `UPDATE`, not just insert. --- ## 4. Quick Verification Snippets ```sql -- Is a MSERVER row actually reachable? (run from the box that will connect) -- nc -zv -- Confirm a MSWCLIENTDATABASE row is fully wired (ServerConfigId + Vault paths set) SELECT CLIENTDBID, CLIENTDBCODE, TENANTID, SERVERCONFIGID, DBAVAULTPATH, APPVAULTPATH, READONLYVAULTPATH FROM SW.MSWCLIENTDATABASE WHERE CLIENTDBID = @ClientDbId; -- Confirm a package chain resolves a real version for a tenant -- (walks SupersedesPackageId to the nearest RELEASEVERSION) -- GET /Provisioning/GetCurrentVersion?ClientDbId= (Login: DatabaseName=Gb5System) -- Confirm the read side sees it -- GET /Version/VersionCheck?ConnectionName= ``` ## 5. Settings Gaps Found Live on GB5DEMO (2026-09-05) — for context, already fixed | Gap | Symptom | Fix | |---|---|---| | `MSERVERCONFIG` for `Gb5System` pointed at a stale `MSERVER` row (`SERVERIP='127.0.0.1,15433'`, unreachable) | Every SqlWorkbench control-plane call failed: "Cannot reach the database server" | Repointed to the real, working server row | | `MSWDBMODEL.TENANTID = 1` instead of `-1` | `ClientDatabaseDAL.GetById` returned "not found" for every tenant except `1` | Set to `-1` | | `MSWUPGRADEPACKAGE.TENANTID = 1` instead of `-1` | Same class of failure in the lineage walk | Set to `-1` | | `Integration:SqlWorkbenchBaseUrl` empty in `FrameworkSL`'s deployed `appsettings.json` | `VersionSyncBLL`'s SqlWorkbench HttpClient had no `BaseAddress` | Set to PlatformHost's real address | | Pending migrations `051`/`052` (`MSWCHANGEREQUEST.EXECUTIONSTATUS`, `SW.MSWUSERRESOURCEROLE`) existed in-repo but had never been run against GB5DEMO | `Approve`/`Execute` on a real `ChangeRequest` failed with "Invalid column/object name" | Applied both migrations |