SQL Servers#

Customer stages live on more than one SQL Server (today PROD and UAT). CommandCenter keeps a registry of them, Platform:SqlServers:{name} in App Configuration (label platform). A stage on a registered server is reached with the host's own Entra identity: the managed identity of the VM, or the alternate identity set in Configuration:Identity. No password is stored anywhere.

A stage on a server that is not registered yet is reached as before, with the CommandCenter database's own login. That fallback is how the change rolls out without breaking anything, and it goes away once PROD and UAT are registered and tested.

The pieces#

flowchart LR
    subgraph host ["WebApi / main API (one environment)"]
        Factory["IDbFactory / HypermasterWorker"]
        Stages["SqlStageConnections"]
        Directory["SqlServerDirectory"]
    end
    AppConfig["App Configuration<br/>Platform:SqlServers (label platform)"]
    Entra["Entra ID<br/>(managed identity)"]
    Prod["SQL Server PROD<br/>registered"]
    Other["SQL Server not registered"]
    CC["CommandCenter database"]

    AppConfig -. "read on every refresh" .-> Directory
    Factory --> Stages --> Directory
    Stages -- "token for database.windows.net" --> Entra
    Stages -- "Entra token, no password" --> Prod
    Stages -- "CommandCenter login (fallback)" --> Other
    Factory -- "CommandCenter login" --> CC

Hold "Alt" / "Option" to enable pan & zoom

A registry entry#

"Platform": {
  "SqlServers": {
    "prod": {
      "Host": "sqlprod01.bcs.local",
      "Aliases": [ "SQLPROD01", "sql-old.bcs.local" ],
      "Environments": [ "prod" ],
      "Purpose": [ "commandcenter", "customer-stages" ],
      "AcceptsNewStages": true,
      "DefaultFor": [ "prod" ],
      "Encrypt": true,
      "TrustServerCertificate": false,
      "Notes": "Primary production server"
    }
  }
}

Field
`Host`, `Port`	Where the hosts connect: a host name (with `\instance` for a named instance), and a port when it is not 1433.
`Aliases`	Other spellings the stage rows use for this server (`TblCustomerStage.Instance`). A stage on any of them is reached through this entry. Case and `tcp:` / `,1433` do not matter. An alias may only spell the host differently: its `\instance` and `,port` must be the entry's. `SQLPROD01\BM` belongs on an entry for the instance `BM`, not on the default instance, which is another SQL Server. Any other alias is refused and logged (EventId 933). A stage row that leaves out a non-default port does not match either: without the port it means 1433.
`Environments`, `Purpose`	Which environments use it, and what it holds.
`AcceptsNewStages`, `DefaultFor`	Whether new stages may be created on it, and for which environments it is the default. (New stages are still created by the legacy CommandCenter; the Database servers page shows the default per environment.)
`Encrypt`, `TrustServerCertificate`	Encrypted with a checked certificate unless switched off. The registry decides, not the CommandCenter connection string.

There is no user or password field: every host signs in as itself.

How a stage connection is made#

sequenceDiagram
    participant Code as WebApi (analytics, clone, export…)
    participant S as SqlStageConnections
    participant D as SqlServerDirectory
    participant Id as Host identity
    participant SQL as Stage's SQL Server
    Code->>S: stage (Instance, Database)
    S->>D: Instance registered? (host, host,port or alias)
    alt registered
        S->>Id: token for https://database.windows.net/.default
        S->>SQL: connect with the token (no user, no password)
    else not registered, or localhost
        S->>SQL: connect with the CommandCenter login (logged once: EventId 932)
    end

Hold "Alt" / "Option" to enable pan & zoom

A loopback instance (localhost, ., (local)) means the CommandCenter server, as the legacy CommandCenter wrote it. It always takes the CommandCenter login path, and development works unchanged.
GET /api/sql-servers (permission config.read) lists the registry and every server this environment's stages are on, with how each is reached (entra or commandCenterLogin). The unregistered ones come first, so they are the next to register.

Registering a server#

journey
    title An operator registers the PROD SQL Server
    section On the server (once)
      SQL Server 2022 with Azure Arc and Entra enabled: 2: DBA
    section Register
      /config/database-servers, Register on the PROD row: 5: Operator
      Check host, port, aliases; Save: 5: Operator
    section Check
      Test from this environment: 4: Operator
      DBA runs the CREATE LOGIN statements the test shows: 3: DBA
      Test again: signed in, databases found: 5: Operator
      The stage row now says Entra: 5: Operator

Hold "Alt" / "Option" to enable pan & zoom

On the server, Entra sign-in must be possible: on-premises that is SQL Server 2022 with Azure Arc. Older versions stay on the CommandCenter login until they are upgraded.
Register it on /config/database-servers. Servers this environment's stages use lists every stage instance; Register on an unregistered row fills in its host and port. Add every other spelling the stage rows use as an alias. The hosts pick it up on their next refresh; no restart is needed.
Test from this environment. The backend of the environment the page is on signs in to master with its own Entra identity, exactly as a stage connection would, and reports:
- who it signed in as, the version and edition;
- whether it may create databases (dbcreator or sysadmin; stage clones need it where new stages are created);
- how many of this environment's stage databases on that server are there, and which are not (a wrong alias, or a stage row that names the wrong server);
- the statements to run when something is missing, for the DBA:
```
CREATE LOGIN [cc-webapi-prod] FROM EXTERNAL PROVIDER WITH OBJECT_ID = '<the identity object id>';
ALTER SERVER ROLE [dbcreator] ADD MEMBER [cc-webapi-prod];
-- then the same database users and roles the CommandCenter login has in the stage databases,
-- and VIEW DEFINITION for the schema endpoint
```
The login is made by the identity's object id, read from the token this host got, so the name is free to choose. Test from every environment that uses the server (switch environment and test again): each environment's backend has its own identity. 4. TLS is the likely first failure. A registered server is reached encrypted, with a checked certificate. An on-premises server with a self-signed certificate, or one from a CA the host does not trust, fails with The certificate chain was issued by an authority that is not trusted. Give the server a certificate from the BCS CA (the runtime image trusts it), or tick accept the server certificate unchecked until that is arranged. 5. Remove takes a server out of the registry: its stages are reached with the CommandCenter login again while a problem is fixed. Nothing on the server changes.

The Database servers page (`/config/database-servers`)#

sequenceDiagram
    participant Op as Operator
    participant SPA as /config/database-servers
    participant API as WebApi (this environment)
    participant AC as App Configuration
    participant Id as Host identity
    participant SQL as SQL Server
    Op->>SPA: Register / Change, Save
    SPA->>API: PUT /api/sql-servers/registry/{name}, the version read
    API->>API: the directory's rules, and against the other entries (400)
    API->>AC: still the version read? (else 409); OPA sqlservers.write
    API->>AC: Platform:SqlServers:{name} = one JSON value, then Settings:PlatformSentinel
    Op->>SPA: Test from this environment
    SPA->>API: POST /api/sql-servers/registry/{name}/test
    API->>Id: token for database.windows.net
    API->>SQL: sign in to master (entry's encryption and certificate check), read metadata
    API-->>SPA: signed in as, dbcreator, stage databases found, statements to run

Hold "Alt" / "Option" to enable pan & zoom

The directory's own rules (SqlServerDefinitions, read back through SqlServerDirectory, so what the page shows is what every host uses): a host name without the port, never a loopback name (every host would reach another machine); an alias may only spell the host differently; purposes commandcenter, customer-stages; a default only for an environment that uses the server and only where new stages are allowed.
Against the other entries: a spelling that already names another server (the directory would use the first and log 931) and a second default for one environment are refused.
One version per entry over all its keys; stored as one JSON value, hand-written keys replaced.
config.read to see; sqlservers.write to register, change, remove and test. Audit 3185 (registered, changed, removed) and 3186 (tested, with the outcome). The host that serves the page needs App Configuration Data Owner.
The test reads server metadata only (SUSER_SNAME(), SERVERPROPERTY, IS_SRVROLEMEMBER, sys.databases), never a stage's data, within 15 seconds. It runs from this environment's backend; another environment's backend is tested from that environment's page.

Not yet#

The CommandCenter database connection itself still signs in with its SQL login.
BMO's own stage access (bmo/) is unchanged.
New stages are still created by the legacy CommandCenter, which does not read DefaultFor.
Not yet run against a real Entra-enabled SQL Server.
A test covers the environment whose page it is run from; one test for every environment at once waits for the platform host.

SQL Servers#

The pieces#

A registry entry#

How a stage connection is made#

Registering a server#

The Database servers page (/config/database-servers)#

Not yet#

The Database servers page (`/config/database-servers`)#