# S4-B1 — verify on the host, change nothing

**Purpose.** Find out whether the Data Protection certificate and key ring are already provisioned
on the target host. The *code* shipped on 2026-07-17 (`5f679a5`); this answers whether the two host
artefacts and two config values exist. It also collects the **real runtime identity names**, which
`docs/deployment/izibizi-activation.md` currently only guesses at.

**This is read-only.** Nothing below creates a certificate, grants an ACL, edits configuration,
starts or stops a service, or writes to the database. If something is missing, **report it — do not
provision it in the same session.** Provisioning is a separate, owned task.

> **This has been run. The result is recorded below under "Result — measured on the host,
> 2026-08-22"**, and it supersedes the earlier line here saying none of it had been run anywhere.
> The checks stay in this document because they are how the result is reproduced and how the next
> host is verified. If a command errors because a path or module differs on another host, record
> the error and move on — a failed command is a finding, not a blocker.

---

## Fill these in first

```powershell
# These are the deployed roots as measured on this host. On another host, adjust and say so.
$DeployPaths = @('C:\SibylaApps\Sibyla.Api','C:\SibylaApps\Sibyla','C:\SibylaApps\Sibyla.Worker')
$ExpectedKeyRing = 'D:\SibylaData\Keys\Cegid'   # from cegid-integration.md; may differ here
```

## 1 — Configuration, on every host that runs API, Web or Worker

```powershell
'--- machine environment ---'
'Cegid__Enabled','Cegid__SecretProtection__KeyRingPath','Cegid__SecretProtection__CertificateThumbprint',
'ExcelCommit__MicrosoftGraph__Enabled' | ForEach-Object {
  '{0} = {1}' -f $_, ([Environment]::GetEnvironmentVariable($_,'Machine'))
}

'--- appsettings ---'
foreach ($p in $DeployPaths) {
  Get-ChildItem $p -Filter 'appsettings*.json' -ErrorAction SilentlyContinue | ForEach-Object {
    "== $($_.FullName)"
    (Get-Content $_.FullName -Raw | ConvertFrom-Json).Cegid | ConvertTo-Json -Depth 4
  }
}
```

Report the two `SecretProtection` values verbatim, and whether `Cegid:Enabled` is `true` or `false`.
Do **not** paste any client secret — there should not be one in these files, and if there is, that
itself is the finding.

## 2 — Certificate

```powershell
Get-ChildItem Cert:\LocalMachine\My |
  Select-Object Thumbprint, Subject, NotAfter, HasPrivateKey |
  Format-Table -AutoSize
```

Is there a certificate whose thumbprint matches the configured one, and does it have
`HasPrivateKey = True`? A matching thumbprint **without** a private key is the failure mode that
makes `ConfigureCegidDataProtection` throw at startup.

## 3 — Key ring

```powershell
$kr = [Environment]::GetEnvironmentVariable('Cegid__SecretProtection__KeyRingPath','Machine')
if (-not $kr) { $kr = $ExpectedKeyRing }
"key ring path: $kr"
Test-Path $kr
Get-ChildItem $kr -Filter 'key-*.xml' -ErrorAction SilentlyContinue |
  Select-Object Name, Length, LastWriteTime
(Get-Acl $kr).Access | Select-Object IdentityReference, FileSystemRights | Format-Table -AutoSize
```

An existing directory with **zero** `key-*.xml` files means nothing has been encrypted yet — the
provisioning may be half done. Files present mean the ring is live and **must be backed up before
anything else happens to it**.

## 4 — Who actually runs the three processes

This is needed regardless of the outcome: the activation runbook guesses the identity names, and
the grants must target the real ones.

```powershell
Import-Module WebAdministration -ErrorAction SilentlyContinue
Get-ChildItem IIS:\AppPools -ErrorAction SilentlyContinue |
  Select-Object Name, @{n='Identity';e={$_.processModel.identityType}},
                      @{n='User';e={$_.processModel.userName}} | Format-Table -AutoSize

Get-CimInstance Win32_Service |
  Where-Object { $_.Name -like '*Sibyla*' -or $_.DisplayName -like '*Sibyla*' } |
  Select-Object Name, State, StartName | Format-Table -AutoSize
```

## 5 — Private-key ACLs, only if a matching certificate exists

On this host the private key is **CNG** (`RSACng`), so its key file lives under
`%ProgramData%\Microsoft\Crypto\Keys` — *not* under the legacy CSP `RSA\MachineKeys` root. The
block below picks the provider by inspecting the key object and falls back to the CSP root, so it
also works on a host provisioned with a legacy CSP key.

```powershell
$tp = [Environment]::GetEnvironmentVariable('Cegid__SecretProtection__CertificateThumbprint','Machine')
if (-not $tp) { 'no thumbprint configured' }
else {
  $cert = Get-Item "Cert:\LocalMachine\My\$($tp -replace '\s','')" -ErrorAction SilentlyContinue
  if (-not ($cert -and $cert.HasPrivateKey)) {
    'no certificate with a private key for the configured thumbprint'
  } else {
    $k = [System.Security.Cryptography.X509Certificates.RSACertificateExtensions]::GetRSAPrivateKey($cert)

    # CNG exposes .Key.UniqueName; legacy CSP exposes .CspKeyContainerInfo.UniqueKeyContainerName.
    $kind = $null; $name = $null
    if ($k.PSObject.Properties['Key'] -and $k.Key.UniqueName) {
      $kind = 'CNG'; $name = $k.Key.UniqueName
    } elseif ($k.PSObject.Properties['CspKeyContainerInfo']) {
      $kind = 'CSP'; $name = $k.CspKeyContainerInfo.UniqueKeyContainerName
    }
    "provider: $kind"

    $roots = @(
      "$env:ProgramData\Microsoft\Crypto\Keys",            # CNG machine keys — this host
      "$env:ProgramData\Microsoft\Crypto\RSA\MachineKeys"  # legacy CSP fallback
    )
    $path = $null
    if ($name) {
      if (Test-Path -LiteralPath $name) {
        $path = $name                                      # some providers return a full path
      } else {
        $leaf = Split-Path $name -Leaf
        $path = $roots | ForEach-Object { Join-Path $_ $leaf } |
                Where-Object { Test-Path -LiteralPath $_ } | Select-Object -First 1
      }
    }

    if ($path) {
      "private key file: $path"
      (Get-Acl $path).Access | Select-Object IdentityReference, FileSystemRights | Format-Table -AutoSize
    } else {
      'private key file not found under either root — record that and move on'
    }
  }
}
```

## 6 — Whether any secret has actually been stored

Read-only SQL against the Sibyla database. `SELECT` only.

These are the columns the live table actually has. The query is an **aggregate**: it returns
booleans and counts only, so no ciphertext, no secret length, no client id, no endpoint URL and no
company or connection identifier can appear in the output even by accident.

```sql
SELECT
  count(*)                                                          AS connection_rows,
  count(*) FILTER (WHERE "Enabled")                                 AS enabled_rows,
  count(*) FILTER (WHERE "ClientId" IS NOT NULL AND "ClientId" <> '')
                                                                    AS rows_with_client_id,
  count(*) FILTER (WHERE "ProtectedClientSecret" IS NOT NULL)       AS rows_with_protected_secret,
  count(*) FILTER (WHERE "ApiBaseUrl"    LIKE 'https://%'
                    AND  "UploadBaseUrl" LIKE 'https://%'
                    AND  "OAuthBaseUrl"  LIKE 'https://%')          AS rows_all_endpoints_https,
  count(*) FILTER (WHERE "ValidatedConfigurationVersion" IS NOT NULL
                    AND  "ValidatedConfigurationVersion" = "ConfigurationVersion")
                                                                    AS rows_configuration_validated,
  count(*) FILTER (WHERE "LastTestSucceeded")                       AS rows_last_test_succeeded,
  max("LastTestedAt")                                               AS latest_test_at,
  max("LastTestFailureCode")                                        AS latest_failure_code
FROM "CegidCompanyConnections";

SELECT count(*) AS fiscal_year_mapping_rows FROM "CegidFiscalYearMappings";
```

`latest_failure_code` is a fixed reason code, not an identifier, and is safe to report verbatim.
With more than one connection row the two `max(...)` values are aggregates across rows, not a
single row's pair — say so if the row count is above one.

If a table does not exist under those names, list what does:

```sql
SELECT table_name FROM information_schema.tables
WHERE table_schema = 'public' AND table_name ILIKE '%egid%';
```

**Never print the ciphertext, and never print its length either** — the boolean is enough. A row
with a stored protected secret is the strongest possible evidence that B1 was provisioned and
worked, because the secret could not have been written without a usable key ring.

---

## Report back as this table

| # | Check | Result |
|---|---|---|
| 1 | `Cegid:Enabled` | |
| 1 | `KeyRingPath` configured | |
| 1 | `CertificateThumbprint` configured | |
| 2 | Certificate present with private key | |
| 3 | Key-ring directory exists | |
| 3 | `key-*.xml` files present (how many) | |
| 4 | API / Web / Worker identity names | |
| 5 | Private-key ACL grants those identities | |
| 6 | Company connection rows, and any with a stored secret | |

Plus anything that errored, verbatim.

---

## Result — measured on the host, 2026-08-22

**Read-only. Nothing was created, granted, edited, enabled, started, stopped or written.** No
secret, ciphertext, client id, thumbprint value, endpoint URL or company identifier is reproduced
here; only the facts the checks above ask for.

| # | Check | Result |
|---|---|---|
| 1 | `Cegid:Enabled` | **false** |
| 1 | `KeyRingPath` configured | **yes** — `D:\SibylaData\Keys\Cegid`, the path this document expected |
| 1 | `CertificateThumbprint` configured | **yes** |
| 2 | Certificate present with private key | **yes** — the configured thumbprint matches a `LocalMachine\My` certificate, subject `CN=GOTT Sibyla Cegid Data Protection`, **`HasPrivateKey` true**, **expires 2031-07-21** |
| 3 | Key-ring directory exists | **yes** |
| 3 | `key-*.xml` files present (how many) | **1** — the ring is **live** and holds real key material |
| 4 | API / Web / Worker identity names | **`IIS APPPOOL\Sibyla.Api`**, **`IIS APPPOOL\Sibyla.Web`**, worker **`.\GottSibylaDocumental`** — these are the real names; `izibizi-activation.md` should be read against these rather than its guessed ones |
| 5 | Private-key ACL grants those identities | **yes** — all three runtime identities hold **Read** on the private CNG key; SYSTEM and Administrators hold FullControl. The key-ring directory ACL grants the same three **Modify**, SYSTEM and Administrators FullControl |
| 6 | Company connection rows, and any with a stored secret | **1** `CegidCompanyConnection`, **`Enabled` = false**. Client id **present**; protected client secret **present**; **all 3 endpoints HTTPS**; **1** fiscal-year mapping; configuration **not validated**; **no successful last test**; latest test **2026-08-11T12:34:52Z**, **failed**, reason `cegid_fiscal_year_2026_rejected` |

Nothing errored.

### What this result means for B1 and B2

**B1 — provisioned, with one named closeout.** A stored protected secret plus live key material is
the strongest evidence this document names, because the secret could not have been written without
a usable key ring. Provisioning was done on **2026-07-21** and is **reconfirmed live on
2026-08-22**. **Do not reprovision.** One thing is genuinely missing: a `.pfx` backup exists under
`D:\fileStorage\SibylaBackups\CegidSecretProtection` (ACL: SYSTEM and Administrators only, which is
correct), but **no `key-*.xml` backup exists under that backup root**. The certificate is
recoverable; the key ring is not. **Closeout: back up the live key-ring XML before disaster
recovery is declared complete.** That is a backup task, not a reprovisioning task.

**B2 — credentials are set, but B2 is not operationally closed.** The documents that describe B2 as
outstanding generation-and-handover are wrong: the connection row exists with its client id and a
protected secret. What is *not* done is the part that proves it works — the connection is
**disabled**, the configuration is **not validated**, there is **no successful test**, and the
latest attempt failed on the fiscal year. **Outstanding: a successful save/reload/test.** Until
then B2 stays open. **Do not enable the connection and do not call Cegid** — pointing a company's
configuration at a live tenant is stop-list work.

### Provenance

The live checks above are the **primary proof**. Two earlier agent sessions **corroborate** them
and are recorded for traceability only, never as a substitute for the measurement:

- **Session `20260721_103836_79ed98` (2026-07-21)** provisioned the certificate, the key ring and
  the ACLs, and the user confirmed the `.pfx` export at the time.
- **Session `20260810_214029_b70519` (2026-08-10/11)** independently observed the company
  connection and its stored secret, and observed the missing key-ring backup.

Session history is corroboration. Where it and the live host disagree, the host wins.

## What the answers mean

> **On this host the first branch applies** — rows with a stored secret **and** key files present,
> so B1 is done. The branches below are kept for verifying a different host.

**Rows in `CegidCompanyConnections` with a stored secret, plus key files present** — B1 is done.
Close it, confirm the `.pfx` backup exists **and that the live `key-*.xml` is backed up too** —
they are separate artefacts and the certificate backup does not recover the ring — and the only
remaining prerequisite is B2.

**Certificate and key ring present, no key files, no rows** — provisioned but never exercised.
B1 is done pending a save-and-reload test once B2 lands.

**Thumbprint configured but the certificate is missing or has no private key** — worse than not
provisioned: the three services will throw at startup as soon as `Cegid:Enabled` becomes `true`.
Report this loudly.

**Nothing configured** — B1 is genuinely outstanding. Follow
`docs/deployment/izibizi-activation.md`, using the identity names from check 4 rather than the
guessed ones.

**Key files present but no rows, and the key-ring path recently changed** — stop and report before
anything is enabled. Ciphertext written under an old ring is unreadable under a new one.