An evidence file is produced for every request: who asked, which rule applied, who approved, what ran and which exceptions occurred. This page explains how the record is protected, what the auditor receives and how the file is verified without the product.
In short
The steps mean little without knowing what they rest on. Each layer works as follows.
| Layer | What it does | Against whom |
|---|---|---|
| Ledger table | Audit records live in a SQL Server 2022 append-only ledger table. The database engine refuses updates and deletes for everyone. Records are written through a single procedure, and that procedure sets the event time. | Anyone who wants to alter or delete a record afterwards, the database administrator included |
| Signed ledger digest | The ledger digest is taken every hour by default. The API produces it, signs it with the organisation's RSA key where a signing key is configured and, where an external output folder is configured, writes it as a file to the organisation's immutable store (ledger-digest-...json and .sig). Without that folder no external file is written. The Worker only starts the cycle and has no access to the signing key. | Someone who edits the database file directly or rolls back to an older backup. The digest was written out earlier, so the difference shows in verification |
| Evidence write boundary | The product checks, by default every 15 minutes, that the evidence table has no trigger, that the write procedure matches the published version and that the evidence schema holds no unexpected object. If the boundary breaks, change operations stop. | Someone who tries to alter the write path to produce false or missing records |
| Signed packages | Where a signing key is configured, every package that goes to the auditor is signed together with the SHA-256 list of its files. | Someone who changes a file after delivery |
An audit usually goes like this: the auditor picks a change at random and asks for its story. Who asked for it, under which rule it was approved, who ran it, what the script was on the day.
The answer exports as a single file. It carries the identity block, separation of duties and the rules in effect that day, the timeline, the approval evidence, the script fingerprint, the risk breakdown, the sandbox result, the execution result, the ITSM checks, the session details of the decision makers, query delivery metadata, exceptions and the raw audit trail.
Part of the information in the file is read from the product's regular tables. While the file is produced, each item is compared field by field with its state in the ledger, and the result is written as a separate section: PASS, MISMATCH, MISSING, UNEXPECTED or INTEGRITY_FAILURE. The time of every event is also compared with its commit time in the ledger, and the writing identity with the product's own accounts. If anything disagrees, the verdict at the top of the file is red. That verdict is the product's own assessment; independent verification stays with the auditor.
The file carries its own seal, and that seal is also written into the audit record. Comparing the two is how you tell whether the file changed after it was produced.
What is delivered is a single zip. Alongside the data it holds an identity file and a verification script and, where a signing key is configured, the RSA signature over that file and the public key that checks the signature. The auditor can run that verification on their own machine without ever connecting to the product: how an evidence package is verified.
Two rules. For a query request the result itself does not enter the file; only who received it and which column was approved unmasked, otherwise the file would become a new leak channel. Second, exporting the file is itself an event and is written to the audit record.
The sample was produced from a change request run end to end on a test installation (one requester, two approvers, a separate executor) and has not been modified; it is signed with a trial key. You can download the whole package (zip) and check it yourself with the Verify.ps1 inside.
When a request is saved, the rule set and effective settings that shaped it are frozen into a single audit event. Six months later, even if the rules have changed, you can still read which rules the request was judged by.
Turning a rule off or lowering its tier is written to the audit record by the rule update event (SqlStandard.Update) with its old and new value, and goes to the SIEM as a control change regardless of the severity threshold. A separate event name is used only when the application configuration is relaxed (App.ControlRelaxed) or a server control field is relaxed (for example Server.MaskingRelaxed). That makes "which controls were relaxed before the audit" an answerable question.
With four eyes switched on, a change on a settings screen is not written to the live record straight away; you switch it on for all settings or for control settings only, and it is off at installation. It waits as a pending entry for another user to approve. No setting lets a person approve their own settings change request.
Cases such as a requester executing their own request appear in the file as exceptions. Governance is not measured by the absence of violations but by whether they surface.
Three separate packages exist because they answer three separate questions. Their wrapper is identical, so the auditor learns one procedure. The ledger digest files are not a package; they are collected separately from the organisation's immutable store.
| Package | The question it answers | Data files |
|---|---|---|
| Evidence dossier SQLChangeGuard-Evidence |
The end to end story of the single request you sampled: who asked for it, under which rule it was approved, who ran it, what the script was that day. While the file is produced, every piece of information read from the tables is compared field by field with its state in the ledger, and the result is written into the file. | Dossier.json Dossier.pdf |
| Period package SQLChangeGuard-PeriodPackage |
"Did I see every request?" The period's requests are collected from two separate sources (the request table and the audit record) and reconciled row by row. Control health events and the exports made during the period are included too. | Population.json ControlHealth.json Exports.json |
| Audit records package SQLChangeGuard-AuditRecords |
The raw audit rows for the record number range you choose, to examine in your own tools. The package fixes the content at delivery time under signature; that the records have not changed since is shown by the ledger, not by the package. | Package.json |
Every export is itself an event. When a package is produced an event is written to the audit record, and its number is kept in Manifest.json. The product's Verify Evidence screen takes that number and the package digest and tells you whether the package in your hand really came out of this installation at that moment. This is a convenience; independent verification is the steps below.
| File | What it is for |
|---|---|
| The data files | The ones listed per package in the table above. Plain text JSON, readable by eye. The evidence dossier also carries a readable PDF. |
| Manifest.json | The package identity block: installation id, product version, scope, and the SHA-256 digest of every file in the package. Removing a file from the package is caught by this list. |
| Manifest.sig | The RSA signature over the bytes of Manifest.json. The signature is over the bytes that were delivered, not over a recomputed copy. |
| PublicKey.pem | The public half of the key that signed the manifest. On its own it establishes no trust: its fingerprint must be compared with the value the organisation publishes outside the package. |
| Verify.ps1 | A script that checks the file digests, the signature and the approval chain (that the save, approval and execution events carry the same content fingerprint) for you. It runs on Windows PowerShell 5.1 and needs no installation. Its own digest is listed in the manifest, so a modified copy is caught. It is a convenience, not the evidence. |
Signature parameters: RSA, SHA-256, PKCS#1 v1.5, PEM key. A key length of 3072 bits is recommended; a shorter key still signs, but the product writes a warning to the log. Signing switches on when a signing key is configured at installation; the code default is off. On an unsigned package Verify.ps1 still checks the file digests but reports UNSIGNED, and the origin of the package is not proven. On a server defined as Production, execution does not start while signing is off. When timestamping is on, the evidence dossier also carries Manifest.tsr.
The steps do not substitute for each other. One passing does not make another pass.
You compute the fingerprint of the public key and compare it with the value the organisation published outside the packages. Skip this step and the signature check proves nothing: anyone who ships a package with a key of their own also passes it.
The question it answers: is the signing key the key the organisation declared?
Manifest.sig is checked against PublicKey.pem; then the SHA-256 digest of every file is recomputed and compared with the list in the manifest. If both pass, the package came from the installation holding the matching private key and has not changed by a single byte since.
The question it answers: are the files in front of me the ones the organisation signed?
The .sig signature of every ledger-digest file you collected from the immutable store is checked with the same public key. The value inside a digest file is the digest produced by the database engine: database name, block number, hash and last commit time.
The question it answers: is the witness I am about to use the one that left the organisation at that hour?
The first three steps check the wrapper and the witness. The fourth looks at the records themselves: a database backup is restored to a test server in the organisation's own environment and SQL Server's own procedure, sys.sp_verify_database_ledger, is run against the digests from the immutable store. The backup does not leave the organisation; the auditor runs the command or watches it being run. The procedure is read only; a copy is recommended so as not to load the production server.
The question it answers: could the records have been rewritten since the digest was written out?
The restored copy stays in the organisation's environment; the auditor runs queries there without touching the live system. The organisation gives the auditor a read only account on the copy, or the commands are run under the auditor's supervision. All the checks below run on that copy.
The digest value from the period's last digest file is copied verbatim and passed to sys.sp_verify_database_ledger. The last digest verifies every block up to it; earlier digests can be added, separated by commas. The expected result is a message saying verification succeeded up to block N. An error means the records in the copy do not match a digest that was written out, and the message names the first block that fails.
The strength of this check comes from the digest having been written before, to a place the organisation's database administrator cannot delete. The immutable store is provided by the organisation (an object store with a retention lock, or a WORM share); the product does not provide that lock itself.
If the database was restored during the period, a Ledger.RestoreDetected or Ledger.ForkDetected event appears in the period package's control health file. The periods before and after the restore are then verified separately with their own digest sets, and the organisation is asked for the record of the restore.
The ledger itself keeps the database identity that wrote each record and its commit time, and that information is protected by the ledger too. The sampled request's events are joined with it: only the product's API and Worker accounts should appear as writer. Any other account means a direct write that bypassed the write procedure.
Because the event time is set inside the write procedure and the event commits in the same transaction as the work, the commit time cannot precede the event time and follows it closely. A large gap is the sign of a backdated write.
Every request row in the period package carries a reconciliation result: Match (both sources agree), NotInAuditTrail (the request is in the table, its creation event is not in the record), NotInTable (the event exists, the request row does not), StatusMismatch (the final states differ) or InProgress (it was running when the package was taken). You can repeat the count on the copy yourself with two simple queries.
The package commands run inside the unzipped folder; nothing connects to the product and nothing goes over the network. The SQL commands run on the restored copy in the organisation's environment.
The script is not part of the evidence. Verify.ps1 ships inside the package it checks. An auditor who wants independence should run the commands by hand at least once. Every step, with its expected result, is written out in the Auditor Kit document, available on request.
With these items in hand the whole verification can be done.
This is the difference between tamper evident and tamper proof. We do not promise a record that can never be altered under any circumstances; we produce one where alteration shows. The second can be verified, the first can only be asserted.
A signature shows where a package came from, not when. The organisation writes the package date from its own server clock.
For this, the evidence dossier supports RFC 3161 timestamping. When it is on, the digest of the manifest is sent to an external timestamp authority and the response is placed in the package as Manifest.tsr. That the file existed at that moment is then stated by a third party rather than by the organisation.
The only thing that leaves the network is the digest. The record itself is not sent to the timestamp authority. When the feature is off, no network call is made and the product keeps working without internet access.
The code default is off, but the configuration that ships with the product has the feature on and points at a free public authority, so an installation can be seen working end to end on day one. In production that address should be replaced with the organisation's own contracted or internal authority: a public service makes no commitment.
If the authority cannot be reached the work does not stop, the file is produced without a stamp and the gap is not hidden: Manifest.tsr is omitted and a warning is written to the log. On such a package the auditor can see that the date is the organisation's own assertion. That the records have not changed since is shown by the ledger, not by the timestamp.
| Group | The question it answers |
|---|---|
| Change Governance | Which requests were opened, how long they waited, who approved them, what ran and what was rejected. |
| Object & Structure | Who touched which object, how often each object changes, where the collisions are. |
| Access & Authorization | Who created accounts, who changed permissions, what privileged accounts did. |
| Data & Privacy | Who accessed production data, which columns were masked, which requests modified data directly. |
| Configuration & Compliance | Which settings changed, which control exceptions occurred, how the approval trail progressed. |
Some reports are limited to specific roles and that limit is enforced on the server; the rest are open to everyone with the report view permission. Report output is in English.
SQL Change Guard is not certified against any standard and we do not claim it is. Compliance comes from your own scope, policies and audit. The only thing software can do is produce the record that shows a control operates. That is all this page describes: which record answers which expectation.
ISO, COBIT, ITIL and local regulation use different vocabularies, but at the database layer they all arrive at the same four questions. Most organisations prepare separately for each framework, when producing the answer to these four once covers most of what the frameworks ask of the database layer.
Even with a certificate, the database layer being in scope is something you have to demonstrate separately. The four controls below are the ones that stand out at the database layer.
| Control | Expectation | Record produced |
|---|---|---|
| 8.2 Privileged access rights |
Elevated permissions must be limited, allocated and monitored, and their use reviewed. | Permission change records, the permission based access table, privileged operations tied to a request record and the audit trail of role assignments. |
| 8.15 Logging |
Activity records must be produced, retained, protected and reviewed. | An audit record in a SQL Server ledger table where the database engine rejects updates and deletes, a ledger digest taken every hour by default and, where a signing key and an external output folder are configured, signed and written to the organisation's immutable store, and the Ledger Status screen. The auditor can also verify personally that the record is protected: signed packages and SQL Server ledger verification on a copy restored in the organisation's environment (how). |
| 8.32 Change management |
Changes to information processing facilities must follow a controlled procedure. | The request, the rule assessment, the risk band, the approval chain, the execution record and the digest comparison, including rejected requests. |
| 8.33 Test information |
Test information must be selected with care, protected and controlled. | The request record for data leaving production, the masking decision and the download reason. Limit: the product does not govern bulk copying between environments; it records people taking data out of production. |
In depth: ISO 27001 and the database: what four controls mean.
Both frameworks are written to be technology neutral, so neither addresses the database separately. The expectation covers it, but the process was usually designed around an application release, and that is where the break starts.
| Expectation | Record produced |
|---|---|
| BAI06 Changes are logged, categorised, assessed for impact and risk, authorised and planned. | A rule based risk band on every request with the list of findings that fired. Because the assessment is written rules rather than human judgement, the same script always gets the same result under the same rule set and reproducibility can be demonstrated. |
| BAI06.03 A system tracks and reports status, and rejected changes are documented as well. | A rejection is written into the request's own history with its reason and appears in reports. This is the evidence organisations most often lack: a record showing only approvals does not demonstrate that the control filters anything. |
| Emergency changes follow a controlled route and are verified afterwards as appropriately assessed (COBIT); a post implementation review is carried out (ITIL). | An emergency record is opened with a stated reason, and an incident number can be made mandatory by setting, and the post hoc review stays on an open list until closed. Whether the emergency route has become a shortcut becomes visible as a number. |
| An ITIL 4 change authority can be a person, a team or an automated mechanism, and authority should be distributed by risk. | Approval policies can be defined per risk band: a low band can pass with one approval or pre authorisation, a high band with multiple approvals and a stated reason. The board sees only work that genuinely needs assessment. |
| MEA Continuous monitoring of internal control and of compliance with external requirements. | Control exceptions are visible on the dashboard during the day and thirty four built in reports across five groups can be run regularly. Evidence does not have to be assembled by hand once a year. |
In depth: COBIT BAI06 and ITIL 4: five break points at the database layer.
At the database layer, personal data risk concentrates in reads rather than changes. Even in organisations with mature change management, extracting data from production usually goes ungoverned.
| Principle | Record produced |
|---|---|
| Record of the processing activity | Every request that pulls data from production: who asked, on what stated basis, from which server, which columns, who downloaded the result and why. |
| Data minimisation | Sensitive columns are masked by default. Unmasked access is a separate decision requiring a reason and an extra approval, and unmasked column requests can be switched off organisation wide. |
| Data security measures | The result is delivered as an encrypted package, the package password is not emailed, and access traces are kept in an append-only audit record. Detail: security architecture. |
| Joint responsibility with a processor | Vendors and consultants work through the request flow rather than holding a standing server password. What each of them did stays recorded on your side. |
In depth: preparing the database side for a data protection audit and real data in test environments.
What stalls an audit is not missing records but records that do not answer the question. When the four questions and four kinds of evidence (request, assessment, approval, execution) come together in one file, the evidence for a sample is taken as a single file.
The database side of a regulatory audit: four questions, four kinds of evidence
Every account, including administrative and vendor accounts, is expected to carry a unique ID for traceability. At the database layer this is often breached because operations appear under shared service accounts. Shared accounts should be the exception and their justification written down. Where a connection uses a shared account, recording the person who started the action separately preserves attribution; it complements the standard's unique ID expectation rather than replacing it.
Verify the sample evidence file on your own computer, or let us walk through your own control list together.