Skip to content

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; these fields are read by the upcoming Database servers page.)
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
      CREATE LOGIN for each host identity FROM EXTERNAL PROVIDER: 3: DBA
      Grant the same rights the CommandCenter login has: 3: DBA
    section Register
      Add Platform:SqlServers:prod with its aliases: 4: Operator
      Bump Settings:PlatformSentinel: 4: Operator
    section Check
      GET /api/sql-servers shows prod stages as entra: 5: Operator
      Run a stage query; nothing asks for a password: 5: Operator
Hold "Alt" / "Option" to enable pan & zoom

  1. On the server, for every environment identity that reaches it (each environment's WebApi and main API):

    CREATE LOGIN [id-cc-webapi-prod] FROM EXTERNAL PROVIDER;
-- then the same server roles and database users the CommandCenter login has today,
-- VIEW DEFINITION for the schema endpoint, dbcreator for stage clones

    On-premises SQL Server accepts Entra sign-in from SQL Server 2022 with Azure Arc. Older versions stay on the CommandCenter login until they are upgraded.

  2. Register the entry above in App Configuration with label platform, including every spelling the stage rows use as an alias, and bump Settings:PlatformSentinel. The hosts pick it up on their next refresh; no restart is needed.

  3. Check GET /api/sql-servers: the server's stages now say entra. If a stage query fails with Login failed for user '<token-identified principal>', the login from step 1 is missing. Remove the entry to fall back while it is fixed.
  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 set TrustServerCertificate: true on the entry until that is arranged.

The Database servers page on /config will do steps 2 and 3, test each server from every environment, and show the CREATE LOGIN statements to run.

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.