Microsoft SQL Server Integration Guide

This guide covers how to connect a Microsoft SQL Server database (including Azure SQL Database and Amazon RDS for SQL Server) to BurnLedger, configure query templates, set up a read-only database user, and choose the right hash scope for your compliance needs.

Connection Configuration

BurnLedger connects to SQL Server using a DSN (Data Source Name) connection string in URL form, passed through to the go-mssqldb driver exactly as you supply it.

Format:

sqlserver://<user>:<password>@<host>:<port>?database=<database>&encrypt=true

The database is selected with the database query parameter, not with a path segment. If you omit it, the login's default database is used — always set it explicitly so the query template resolves against the database you expect.

Percent-encode special characters in the password. The DSN is parsed as a URI, both by the driver and by BurnLedger's host-validation step, so characters such as @ : / ? # must be escaped (@ → %40, : → %3A, / → %2F, ? → %3F, # → %23). An unescaped @ in the password makes the parser read the wrong hostname, and registration fails as targeting an invalid or blocked host — not as a bad password. If you control the credential, the simplest option is a password limited to letters, digits, - and _ (still complex enough for the default SQL Server password policy).

Example:

sqlserver://burnledger_ro:REPLACE-WITH-YOUR-PASSWORD@sql.example.com:1433?database=myapp&encrypt=true

REPLACE-WITH-YOUR-PASSWORD is a placeholder, not a suggestion: choose your own password when you create the user below, and substitute it wherever the placeholder appears in this guide.

Configuration fields:

The SQL Server connector config accepts string fields only. Do not add a read_only flag: unlike the connectors that cannot introspect privileges (S3, Redis, MongoDB, HBase, …), SQL Server can, so BurnLedger determines read-only status itself from the engine's permission catalog and there is no read_only assertion to make. A non-string value in the config makes the config fail to parse before any connection is attempted.

When registering the system via the SDK:

TypeScript

const system = await bl.registerSystem({
  name: "users-mssql",
  connectorType: "sqlserver",
  connectionConfig: {
    dsn: "sqlserver://burnledger_ro:REPLACE-WITH-YOUR-PASSWORD@sql.example.com:1433?database=myapp&encrypt=true",
  },
  subjectQuery: "SELECT id, email, name FROM dbo.users WHERE email = @p1",
});

Python

system = bl.register_system(
    name="users-mssql",
    connector_type="sqlserver",
    connection_config={
        "dsn": "sqlserver://burnledger_ro:REPLACE-WITH-YOUR-PASSWORD@sql.example.com:1433?database=myapp&encrypt=true",
    },
    subject_query="SELECT id, email, name FROM dbo.users WHERE email = @p1",
)

Connection requirements:

Private CA (ca_cert)

encrypt=true verifies the server certificate against the system trust store. An internal CA (common for on-prem SQL Server, and for any server behind a re-signing proxy) fails that check with x509: certificate signed by unknown authority. Supply the root in the connection config as ca_cert — base64 of the PEM bundle, snake_case in both SDKs:

base64 -w0 ca.pem      # macOS: base64 -i ca.pem | tr -d '\n'

{
  "dsn": "sqlserver://burnledger_ro:pw@sql.example.com:1433?database=myapp&encrypt=true",
  "ca_cert": "LS0tLS1CRUdJTiBDRVJUSUZJQ0FURS0tLS0t..."
}

ca_cert does not change the transport classification: a custom trust root is still verification, so the config stays verified and is admitted by the ADR-012 floor. It also never turns TLS on — supplying it alongside a DSN that puts no TLS on the data path is refused when the connector is built, rather than connecting with a trust root that does nothing.

encrypt=true or encrypt=strict is required for the root to mean anything: a DSN with no encrypt key TLS-wraps only the login packet and then reverts to the bare socket, so a ca_cert supplied there is refused rather than protecting the password and nothing else. TrustServerCertificate=true is refused on its own grounds — it verifies nothing.

Query Template Format

A query template is a SELECT statement with @p1 as the placeholder for the data subject identifier. @p1 is the ordinal parameter name the SQL Server driver binds; BurnLedger passes the subject value as a bound parameter, never by string interpolation.

@p1 is the placeholder for this connector, and it is not optional. There is no universal placeholder across BurnLedger connectors ($1 for PostgreSQL, ? for MySQL, {identifier} for object stores). A SQL Server template with neither @p1 nor ? is rejected at registration with INVALID_QUERY_TEMPLATE — and a ? template, though it passes registration, fails at execution (see the note at the end of this section), so @p1 is the only placeholder that works end to end. This is deliberate: a template that never references the subject would match rows unrelated to the subject, or none at all, and BurnLedger would issue a valid, signed verification record asserting that no records exist for that person while their data sits untouched.

Rules:

• Must begin with SELECT (or WITH — but see the derived-table limits below).
• Must contain @p1. It may appear more than once; every occurrence binds to the same single subject identifier value.
• Must contain a WHERE clause. A template without one is rejected — it would scan the whole table.
• Must be a single statement: an embedded ; is rejected at registration. A trailing ; passes registration but breaks at execution — BurnLedger wraps your template in a subquery, and a semicolon inside that subquery is a syntax error — so leave it off.
• Must not contain data-modifying or session-changing keywords anywhere, including inside a CTE: INSERT, UPDATE, DELETE, MERGE, TRUNCATE, DROP, CREATE, ALTER, GRANT, REVOKE, INTO, EXEC/EXECUTE, SET, DECLARE, BEGIN/COMMIT/ROLLBACK, GO, and similar. The check is deliberately strict and default-deny. (Comments and single-quoted string literals are stripped before this scan, so a keyword appearing only inside one of those does not trip it.)
• Must not use FOR UPDATE / FOR SHARE style locking clauses.

Derived-table limits. BurnLedger executes your template wrapped in a bounded outer query (SELECT TOP <limit> * FROM (<your template>) AS _dp_inner), which imposes three T-SQL constraints the validator cannot catch for you:

• No ORDER BY — T-SQL rejects ORDER BY in a derived table unless TOP/OFFSET is also present. Row order does not affect the hash, so just drop it.
• Every output column must have a unique name. SELECT * across a join of two tables that both have id fails with "the column 'id' was specified multiple times". List columns explicitly and alias collisions.
• Every output column must be named. Alias computed expressions (CONCAT(a, b) AS full_name).
• Avoid top-level CTEs. A WITH … SELECT template passes registration but fails at execution, because T-SQL does not allow a CTE to begin inside a derived table. Rewrite the CTE as an inline subquery or a join.

Examples:

-- Single table, subject identified by email
SELECT id, email, name, created_at FROM dbo.users WHERE email = @p1

-- Subject identified by external ID
SELECT id, name, email, phone FROM dbo.customers WHERE external_id = @p1

-- Join across tables: explicit columns, collisions aliased
SELECT u.id AS user_id, u.email, p.street, p.city, p.country
FROM dbo.users u
LEFT JOIN dbo.profiles p ON p.user_id = u.id
WHERE u.email = @p1

-- The same parameter referenced twice
SELECT o.id, o.total, o.placed_at
FROM dbo.orders o
WHERE o.billing_email = @p1 OR o.shipping_email = @p1

Important: Design your query to return all records associated with the data subject that you need to prove exist (or have been deleted), including soft-deleted rows. If a subject's data spans several tables with no clean join, register multiple systems instead of one wide query.

A ? placeholder is not a substitute. Registration currently accepts ? as well as @p1, but the sqlserver driver does not translate ? into a bound parameter — the query fails at attestation time with a T-SQL syntax error surfaced as CONNECTION_FAILED. Use @p1.

Temporal tables (system-versioned)

A deletion from a system-versioned temporal table cannot be certified while the table is system-versioned.

A DELETE against a table declared WITH (SYSTEM_VERSIONING = ON) removes the row from the current table and writes it into the history table SQL Server keeps beside it. Nothing ages it out unless a HISTORY_RETENTION_PERIOD is set, and even then the row stays for the whole period. Getting it back is one statement your own credential can run — SELECT … FOR SYSTEM_TIME ALL, or INSERT … SELECT from the history table — with no operator and no restore job. The SELECT in your query template reads the current table and sees none of this: a deleted subject produces a clean zero.

So when a subject query returns no rows, BurnLedger asks the engine two questions before it will report that zero:

• Which tables does the template read? sys.dm_exec_describe_first_result_set in browse mode returns the engine's own list — resolved through views and derived tables, and including every keyed table in a JOIN whether or not a column of it is selected. A regex over FROM cannot see through a view; the engine can.
• Is any of them system-versioned? sys.tables.temporal_type says so, and the row is visible to any login holding SELECT on the table — db_datareader or an explicit GRANT SELECT ON dbo.users alike. No grant beyond the read-only user is needed.

The outcomes:

• A table the template reads is system-versioned → refused, error code S3_VERSIONING_CONFLICT, with mechanism: temporal tables and the history table named in the detail.
• Every table it reads is a plain table → the zero is reported and can be certified.
• The question cannot be answered — the login is denied SELECT on sys.tables, the template reads a table in another database or on a linked server, or the template has more than one SELECT (an EXISTS/IN subquery, a CTE, a UNION) so the engine's table list would be incomplete → refused with CONNECTION_FAILED naming what to change. BurnLedger never treats an unanswered question as a "no".

The check is per table, because that is what SQL Server retains: the history table holds every row ever deleted from the current table, and nothing available to a read-only login can say whether a particular subject's rows are among them. A system-versioned table is therefore refused for every subject, including subjects who were never in it.

To certify a deletion from a system-versioned table: switch system versioning off (ALTER TABLE … SET (SYSTEM_VERSIONING = OFF)), remove the subject's rows from the history table, and re-run the attestation. Nothing is checked when the query finds records — a non-zero count fails the deletion claim on its own. Reading the history table directly (FROM dbo.users_history) or with FOR SYSTEM_TIME ALL is not a workaround: the first counts only superseded and deleted versions, and the second hides the base table from the check and is refused.

Read-Only User Setup

BurnLedger requires a dedicated read-only login. It checks the credential's privileges during connection validation and refuses the connection outright if the credential can write. This is a security property of the product: a deletion-attestation service must never be able to modify the data it attests to.

What BurnLedger checks. Immediately after connecting, it introspects the effective permissions of the connecting principal at two granularities, because SQL Server reports them separately and neither one sees the other's grants.

1. Scope permissions, held on the DATABASE and SERVER securables themselves:

SELECT permission_name FROM sys.fn_my_permissions(NULL, 'DATABASE')
UNION ALL
SELECT permission_name FROM sys.fn_my_permissions(NULL, 'SERVER')

The credential is rejected with WRITE_ACCESS_DETECTED if any returned permission is write-capable:

• INSERT, UPDATE, DELETE
• CONTROL, CONTROL SERVER, TAKE OWNERSHIP
• EXECUTE, IMPERSONATE
• ADMINISTER BULK OPERATIONS, ADMINISTER DATABASE BULK OPERATIONS
• BACKUP DATABASE, BACKUP LOG
• any permission beginning with CREATE, ALTER, or DROP (CREATE PROCEDURE, ALTER ANY SCHEMA, DROP ANY DATABASE, …)

sys.fn_my_permissions returns permissions granted directly, inherited through role membership, and expanded from fixed server roles — so a sysadmin credential surfaces CONTROL SERVER and is rejected.

2. Object permissions, held on individual tables and views. sys.fn_my_permissions(NULL, 'DATABASE') reports what the principal holds on the database, not on the tables inside it, so a GRANT INSERT ON dbo.orders appears nowhere in the query above. BurnLedger asks the engine about each table and view separately:

SELECT TOP 1 1
FROM sys.objects AS o
JOIN sys.schemas AS s ON s.schema_id = o.schema_id
WHERE o.type IN ('U', 'V')
  AND ISNULL(OBJECTPROPERTY(o.object_id, 'TableTemporalType'), 0) <> 1
  AND (HAS_PERMS_BY_NAME(QUOTENAME(s.name) + '.' + QUOTENAME(o.name), 'OBJECT', 'INSERT') = 1
    OR HAS_PERMS_BY_NAME(QUOTENAME(s.name) + '.' + QUOTENAME(o.name), 'OBJECT', 'UPDATE') = 1
    OR HAS_PERMS_BY_NAME(QUOTENAME(s.name) + '.' + QUOTENAME(o.name), 'OBJECT', 'DELETE') = 1)

One row back means WRITE_ACCESS_DETECTED. HAS_PERMS_BY_NAME reports the caller's own effective permission, so a db_datawriter membership, a schema-level GRANT and a per-table GRANT all count the same way and a DENY subtracts — and it requires no privilege of its own, so this check asks nothing of the credential that connecting did not.

The TableTemporalType exclusion is not a loophole. SQL Server reports INSERT on the history table of a live system-versioned table even for a db_datareader, while refusing the insert itself with error 13559 ("Cannot insert rows in a temporal history table") rather than the permission error 229 — so counting it would reject every read-only credential on every database containing a temporal table. TableTemporalType is 1 for exactly the state in which the engine blocks direct DML, and drops to 0 the instant SET (SYSTEM_VERSIONING = OFF) makes the table writable.

A db_datareader-only credential surfaces CONNECT plus SELECT/VIEW-style permissions at scope, no write permission on any object, and is accepted. Note that BurnLedger performs no write of any kind to test either of these — no temp table, no probe row.

Create the login and user

Connect as an administrator (sa, a sysadmin, or on Azure SQL the server admin). Create the login on the server, then the database user:

-- 1. On the server (in Azure SQL Database, connect to the `master` database)
CREATE LOGIN burnledger_ro WITH PASSWORD = 'REPLACE-WITH-YOUR-PASSWORD';

-- 2. In the database you want BurnLedger to read (connect to `myapp`)
CREATE USER burnledger_ro FOR LOGIN burnledger_ro;

-- 3. Grant read-only access
ALTER ROLE db_datareader ADD MEMBER burnledger_ro;

db_datareader grants SELECT on every table and view in the database and nothing else. If you prefer to scope it to specific tables, skip step 3 and grant explicitly instead:

GRANT SELECT ON dbo.users TO burnledger_ro;
GRANT SELECT ON dbo.profiles TO burnledger_ro;

Verify the permissions

Connect as burnledger_ro and confirm what the credential actually holds — this is the same view BurnLedger uses:

-- Should list only CONNECT and SELECT/VIEW-style permissions.
-- Any CREATE/ALTER/DROP/INSERT/UPDATE/DELETE/EXECUTE/CONTROL row means rejection.
SELECT permission_name FROM sys.fn_my_permissions(NULL, 'DATABASE')
UNION ALL
SELECT permission_name FROM sys.fn_my_permissions(NULL, 'SERVER');

-- Should return NO rows. Every row is a table or view this credential can
-- write, which the query above cannot see and which means rejection.
SELECT s.name AS [schema], o.name AS [object],
       HAS_PERMS_BY_NAME(QUOTENAME(s.name) + '.' + QUOTENAME(o.name), 'OBJECT', 'INSERT') AS can_insert,
       HAS_PERMS_BY_NAME(QUOTENAME(s.name) + '.' + QUOTENAME(o.name), 'OBJECT', 'UPDATE') AS can_update,
       HAS_PERMS_BY_NAME(QUOTENAME(s.name) + '.' + QUOTENAME(o.name), 'OBJECT', 'DELETE') AS can_delete
FROM sys.objects AS o
JOIN sys.schemas AS s ON s.schema_id = o.schema_id
WHERE o.type IN ('U', 'V')
  AND ISNULL(OBJECTPROPERTY(o.object_id, 'TableTemporalType'), 0) <> 1
  AND (HAS_PERMS_BY_NAME(QUOTENAME(s.name) + '.' + QUOTENAME(o.name), 'OBJECT', 'INSERT') = 1
    OR HAS_PERMS_BY_NAME(QUOTENAME(s.name) + '.' + QUOTENAME(o.name), 'OBJECT', 'UPDATE') = 1
    OR HAS_PERMS_BY_NAME(QUOTENAME(s.name) + '.' + QUOTENAME(o.name), 'OBJECT', 'DELETE') = 1);

-- This should succeed
SELECT TOP 1 id, email FROM dbo.users;

-- These should all fail with "permission denied"
INSERT INTO dbo.users (email) VALUES ('test@example.com');
UPDATE dbo.users SET email = 'changed@example.com' WHERE id = 1;
DELETE FROM dbo.users WHERE id = 1;

Remove write privileges if they exist

Most rejections come from inherited role membership rather than a direct grant. Drop the offending roles and re-check:

ALTER ROLE db_owner        DROP MEMBER burnledger_ro;
ALTER ROLE db_datawriter   DROP MEMBER burnledger_ro;
ALTER ROLE db_ddladmin     DROP MEMBER burnledger_ro;
ALTER ROLE db_securityadmin DROP MEMBER burnledger_ro;

REVOKE INSERT, UPDATE, DELETE, EXECUTE, ALTER, CONTROL ON SCHEMA::dbo FROM burnledger_ro;

-- On the server side (connect to master), ensure no sysadmin/serveradmin membership:
ALTER SERVER ROLE sysadmin DROP MEMBER burnledger_ro;

Azure SQL Database Notes

These Azure-specific behaviors account for most first-connection failures.

Serverless tiers auto-pause. An idle Azure SQL Serverless database pauses, and the first connection after the idle window fails while it resumes, with an error like:

Database 'myapp' on server 'sql-example' is not currently available. Please retry the connection later.

This is not a credential or firewall problem. Wait for the resume (typically under a minute) and retry the registration or attestation. If your attestation schedule is sparse, consider disabling auto-pause or keeping the database warm so attestations do not fail on a cold start.

The firewall rule goes on the SQL server, not the database. In the Azure portal, open the SQL server resource → Networking → Firewall rules, and add the BurnLedger egress IPs there. The database blade has no firewall of its own, and a rule added anywhere else has no effect. Symptom of a missing rule: CONNECTION_FAILED with "Cannot open server … requested by the login. Client with IP address … is not allowed to access the server."

encrypt=true is mandatory. Azure SQL rejects unencrypted logins. Include &encrypt=true in the DSN.

Hostnames need no special handling. Azure SQL servers are always addressed by FQDN (<server>.database.windows.net); BurnLedger hands the hostname to its dialer directly, so there is nothing for you to configure.

Hash Scope Options

The hashScope parameter is recorded on the system and carried into every verification record, so a verifier can see what the hashes are meant to cover. If you omit it, it defaults to existence.

full (recommended for most cases)

Declares that the attestation covers the complete content of every row returned by the query. When row hashes are actually computed (proof mode merkle — see below), each row is canonicalized as name<sep>value pairs sorted by column name, with a distinct sentinel for SQL NULL, then hashed with SHA-256. Two consequences worth knowing:

• Changing the order of columns in your SELECT does not change the hash.
• Changing a column's name or alias does change it — so keep aliases stable across attestation and verification.

const system = await bl.registerSystem({
  // ...
});

Use when: You need to prove the exact data that existed, or prove that data was deleted and not merely modified (nullifying PII fields is not the same as deletion under some interpretations of GDPR).

existence (default when omitted)

Declares that the attestation is about whether records exist and how many, not about their content.

const system = await bl.registerSystem({
  // ...
  hashScope: "existence",
});

Use when: You only need to prove that records existed and were later removed, and you do not need to prove what was in those records.

How it interacts with proof mode

For SQL Server, what BurnLedger actually executes is chosen by the attestation's proof mode, not by the hash scope:

Row hashing under merkle always covers the full canonicalized row for SQL connectors — setting hashScope: "existence" does not make the SQL Server connector hash less. If you do not want row content to influence any hash, use proof mode count.

Comparison

"Detects" here is bounded by what a merkle certificate actually carries: a signed Merkle root over the row hashes and nothing else — BurnLedger serves no per-record inclusion proof, so a changed value is demonstrable only to someone who already holds every matching row and rebuilds the tree, and from format 7.0 the leaves are keyed inside the enclave so no party, BurnLedger included, can open the root at all (see Certificate Scope §4).

End-to-End Example

This example walks through the full process: creating a read-only login, registering the system, creating an attestation before deletion, performing the deletion, verifying, and generating a verification record.

1. Database setup (run once)

-- On the server (Azure SQL: connect to `master`), as an administrator
CREATE LOGIN burnledger_ro WITH PASSWORD = 'REPLACE-WITH-YOUR-PASSWORD';

-- In the target database (`myapp`)
CREATE USER burnledger_ro FOR LOGIN burnledger_ro;
ALTER ROLE db_datareader ADD MEMBER burnledger_ro;

2. Application code

TypeScript

import { BurnLedger } from "burnledger";
import fs from "node:fs/promises";

const bl = new BurnLedger({
  apiKey: process.env.BURNLEDGER_API_KEY,
});

// Register the system (run once, store the system ID)
const system = await bl.registerSystem({
  name: "myapp-users-mssql",
  connectorType: "sqlserver",
  connectionConfig: {
    dsn: "sqlserver://burnledger_ro:REPLACE-WITH-YOUR-PASSWORD@sql.example.com:1433?database=myapp&encrypt=true",
  },
  subjectQuery: "SELECT u.id AS user_id, u.email, u.name, p.phone, p.address FROM dbo.users u LEFT JOIN dbo.profiles p ON p.user_id = u.id WHERE u.email = @p1",
});

// --- When a deletion request comes in ---

const subjectEmail = "jane.doe@example.com";

// Step 1: Attest that the data currently exists
const attestation = await bl.attest(subjectEmail, {
  systemIds: [system.id],
  proofMode: "merkle",
});

for (const s of attestation.systems) {
  console.log(`${s.systemName}: ${s.recordCount} records found`);
}

// Step 2: Delete the data in your application
// await pool.request().input("email", subjectEmail)
//   .query("DELETE FROM dbo.profiles WHERE user_id IN (SELECT id FROM dbo.users WHERE email = @email)");
// await pool.request().input("email", subjectEmail)
//   .query("DELETE FROM dbo.users WHERE email = @email");

// Step 3: Verify the deletion
// The certificate is ISSUED BY verify() once the data is gone -- there is
// no separate "create certificate" step.
const result = await bl.verify(attestation.id, subjectEmail, { timeout: 60 });

for (const s of result.systems) {
  console.log(`${s.systemName}: now ${s.recordCount} records`);
}

if (result.certificate) {
  await bl.savePdf(result.certificate.id, `erasure-cert-${subjectEmail}.pdf`);
  console.log(`Certificate issued: ${result.certificate.id}`);
  console.log(`Transparency status: ${result.certificate.transparencyStatus}`);
} else {
  console.log(`Not certified: status ${result.status}`);
}

Python

import os
from burnledger import BurnLedger

bl = BurnLedger(api_key=os.environ["BURNLEDGER_API_KEY"])

# Register the system (run once, store the system ID)
system = bl.register_system(
    name="myapp-users-mssql",
    connector_type="sqlserver",
    connection_config={
        "dsn": "sqlserver://burnledger_ro:REPLACE-WITH-YOUR-PASSWORD@sql.example.com:1433?database=myapp&encrypt=true",
    },
    subject_query=(
        "SELECT u.id AS user_id, u.email, u.name, p.phone, p.address "
        "FROM dbo.users u LEFT JOIN dbo.profiles p ON p.user_id = u.id "
        "WHERE u.email = @p1"
    ),
)

# --- When a deletion request comes in ---

subject_email = "jane.doe@example.com"

# Step 1: Attest that the data currently exists
attestation = bl.attest(
    subject_email,
    system_ids=[system.id],
    proof_mode="merkle",
)

for s in attestation.systems:
    print(f"{s.system_name}: {s.record_count} records found")

# Step 2: Delete the data in your application
# cursor.execute("DELETE FROM dbo.profiles WHERE user_id IN (SELECT id FROM dbo.users WHERE email = ?)", subject_email)
# cursor.execute("DELETE FROM dbo.users WHERE email = ?", subject_email)
# connection.commit()

# Step 3: Verify the deletion
# The certificate is ISSUED BY verify() once the data is gone -- there is
# no separate "create certificate" step.
result = bl.verify(attestation.id, subject_email, timeout=60)

for s in result.systems:
    print(f"{s.system_name}: now {s.record_count} records")

if result.certificate:
    bl.save_pdf(result.certificate.id, f"erasure-cert-{subject_email}.pdf")
    print(f"Certificate issued: {result.certificate.id}")
    print(f"Transparency status: {result.certificate.transparency_status.value}")
else:
    print(f"Not certified: status {result.status.value}")

Troubleshooting