OneLinersCommand workbench
Guides
Databases & Data / Security / Backup & Recovery

Secure SQL Server with TLS, least privilege, and verified backup restore

Configure SQL Server 2025 CU7 on Ubuntu 24.04 for strict validated TLS, replace broad application rights, create an encrypted checksum backup with off-host key escrow, and prove it through real restore and CHECKDB.

270 min16 stepsHigh-impact changeRevision 1
Save or explore
#sql-server#mssql#tls#tds-8#least-privilege#backup#restore#checkdb#ubuntu
Save to collectionCreate a collection in the sidebar first.
0 of 16 steps completed
Goal

Produce auditable evidence that client sessions validate identity, workloads have only required permissions, and an independently retained encrypted backup can actually restore within RPO/RTO.

Supported environments
  • SQL Server 2025 (17.x) CU7 or later supported 17.x CU
  • Ubuntu Server 24.04 LTS
Prerequisites
  • Supported patched engine SQL Server 2025 on Ubuntu 24.04 is on a current supported CU and the target database is healthy with PAGE_VERIFY CHECKSUM.
  • Strict-capable clients and PKI Every critical driver supports TDS 8.0 strict encryption, trusts the CA, and uses a DNS SAN; rollback is approved.
  • Separated identities Named security, backup, restore, and application owners have secret-manager access and reviewed permission matrices.
  • Independent recovery Off-host protected storage, separate key escrow, a SQL Server 2025 recovery host, capacity, manifest, and RPO/RTO window exist.
Operating boundary

OneLiners never runs these steps or stores secrets. Review placeholders, versions, current state, and change-control requirements before using a command.

Full guide

What you will build

System
  • A SQL Server 2025 CU7 on Ubuntu 24.04 endpoint using CA-validated Force Strict Encryption and behaviorally proven least-privilege application access.
  • An AES-256 encrypted checksum backup with off-host certificate/private-key escrow, media verification, actual isolated restore, CHECKDB, and deterministic application validation.
Observable outcome
  • Every accepted client session reports encryption and validates the SQL Server DNS identity without TrustServerCertificate.
  • The application succeeds only on approved schema operations and is denied DDL/backup/administrative access.
  • A protected off-host backup restores on the recovery host, passes CHECKDB and manifest checks, and meets measured RPO/RTO.

Architecture

How the parts fit together

SQL Server 2025 on Ubuntu exposes one strict TDS endpoint with a CA-signed network certificate. Named operator, backup, restore, and application principals have separate scopes. A dedicated certificate in master encrypts backup media; its private key and password are escrowed separately off-host. A recovery instance imports the key, verifies media, restores under isolated names, validates consistency/data, and removes the drill database.

Production SQL ServerHosts the application database, enforces strict encrypted sessions, and creates checksum/encrypted backups.
Network TLS certificateAuthenticates the SQL endpoint to current TDS clients; unrelated to backup decryption.
Backup encryption certificateEncrypts backup media and must be exported with its private key for off-host restore.
Least-privilege principalsSeparate application, backup, restore, and security administration responsibilities.
Recovery SQL ServerConsumes only off-host artifacts and materializes an isolated database for CHECKDB/application tests.
  1. A strict-capable client validates the CA/SAN, establishes encrypted TDS, authenticates, and is authorized for narrow schema operations.
  2. SQL Server reads pages with checksum validation and writes a compressed copy-only backup encrypted by the dedicated certificate.
  3. Backup, manifest, and separately protected certificate/private-key escrow move to independent retention.
  4. The recovery instance imports the key, runs VERIFYONLY, restores with MOVE under an isolated name, runs CHECKDB and deterministic queries, then cleans the drill.

Assumptions

  • SQL Server 2025 CU7 build 17.0.4065.4 runs on supported Ubuntu Server 24.04 with current Microsoft ODBC/sqlcmd tooling.
  • A production CA, stable DNS, current strict-capable clients, secret management, monitoring, and out-of-band host access exist.
  • The target is a standalone/client TDS endpoint. This guide does not claim TLS protection for Always On availability-group database-mirroring endpoints.
  • A protected independent backup destination, recovery SQL Server 2025 instance, sufficient capacity, and an approved RPO/RTO manifest exist.

Key concepts

Force Strict Encryption
SQL Server 2025 server policy requiring TDS 8.0 strict encrypted client connections with certificate validation.
Backup checksum
Checksum generated over backup content while validating available page checksums; later restore can require it.
COPY_ONLY
Full backup that does not become the differential base or disrupt the normal backup sequence.
Backup encryption certificate
Certificate in master whose private key is required to decrypt an encrypted backup on another instance.
RESTORE VERIFYONLY
Media completeness/readability check that does not restore or validate database structure.
DBCC CHECKDB
Physical/logical consistency validation executed on the actual restored database.

Before you copy

Values used in this guide

{{sqlHost}}

Production SQL Server DNS name covered by certificate SAN.

Example: orders-db.example.net
{{sqlRestoreHost}}

Dedicated recovery SQL Server DNS name.

Example: sql-restore.example.net
{{sqlPort}}

SQL Server TCP port.

Example: 1433
{{sqlCaFile}}

Client trusted CA bundle.

Example: /etc/ssl/certs/database-network-ca.pem
{{sqlTlsCertificate}}

CA-signed SQL Server PEM leaf certificate.

Example: /etc/ssl/certs/mssql.pem
{{sqlTlsKey}}secret

Restricted SQL Server TLS private key.

Example: /etc/ssl/private/mssql.key
{{sqlDatabase}}

Production source database.

Example: Orders
{{sqlRestoreDatabase}}

Unique isolated restore database.

Example: Orders_restore_20260728
{{sqlAdminLogin}}secret

Short-lived security/configuration administrator login; password is prompted or secret-managed.

Example: ops_admin
{{sqlBackupLogin}}secret

Principal limited to approved backup operations and metadata.

Example: backup_operator
{{sqlRestoreLogin}}secret

Recovery-host principal permitted to create/restore/drop only drill databases.

Example: restore_operator
{{sqlAppLogin}}

Application server login.

Example: orders_app
{{sqlAppPassword}}secret

Long generated application password entered only in the controlled secret-aware session.

Example: generated-by-secret-manager
{{sqlAppUser}}

Application database user.

Example: orders_app
{{sqlAppRole}}

Narrow application database role.

Example: orders_runtime
{{sqlReadSchema}}

Schema whose tables/views the application can read.

Example: reporting
{{sqlProcedureSchema}}

Schema whose stored procedures the application can execute.

Example: api
{{sqlValidationTable}}

Non-sensitive manifest validation table.

Example: restore_probe
{{sqlValidationKey}}

Unique key column in the validation table.

Example: order_id
{{sqlKnownKey}}

Synthetic known key captured in the manifest.

Example: synthetic-20260728
{{sqlMasterKeyPassword}}secret

Strong master-key backup/protection password stored outside SQL Server.

Example: separate-secret
{{sqlBackupCertificate}}

Dedicated backup-encryption certificate name in master.

Example: BackupCert_2026
{{sqlBackupCertificateSubject}}

Auditable certificate subject.

Example: SQL backup encryption 2026
{{sqlBackupCertificateExpiry}}

Explicit lifecycle date for the backup certificate.

Example: 2027-07-28
{{sqlBackupCertificateFile}}

Host path for exported public certificate.

Example: /srv/sql-escrow/BackupCert_2026.cer
{{sqlBackupPrivateKeyFile}}secret

Host path for encrypted exported private key.

Example: /srv/sql-escrow/BackupCert_2026.pvk
{{sqlBackupKeyPassword}}secret

Password encrypting the exported private key, stored through a separate channel.

Example: independent-secret
{{sqlBackupDirectory}}

Protected local staging directory writable by mssql.

Example: /srv/sql-backup
{{sqlBackupFile}}

Exact encrypted copy-only backup file available on the recovery host.

Example: /srv/sql-backup/orders-copyonly-20260728.bak
{{sqlLogicalDataName}}

Logical data filename returned by FILELISTONLY.

Example: Orders
{{sqlLogicalLogName}}

Logical log filename returned by FILELISTONLY.

Example: Orders_log
{{sqlRestoreDataFile}}

Unused isolated restored data path.

Example: /var/opt/mssql/data/Orders_restore_20260728.mdf
{{sqlRestoreLogFile}}

Unused isolated restored log path.

Example: /var/opt/mssql/data/Orders_restore_20260728_log.ldf

Security and production boundaries

  • Keep administrative endpoints on private networks, authenticate every operator, and record who approved the change window. TLS without identity validation protects confidentiality but not server authenticity.
  • Supply passwords and private-key passphrases through an approved secret manager or an interactive prompt. Never place them in shell history, process arguments, tutorial variables, logs, tickets, or screenshots.
  • Use a dedicated low-privilege application identity and a separate short-lived administrative identity. A successful superuser test does not prove the application permission boundary.
  • Treat replication, snapshots, and backups as different controls. Replication improves availability; only a protected, retained, independently restored copy establishes recoverability.

Stop before continuing if

  • Stop before a disruptive test when monitoring, a named incident commander, a current backup, or a tested recovery channel is missing.
  • Stop when nodes disagree about identity, time, version, topology, or trust anchors; do not use force, trust bypasses, or quorum overrides to conceal the inconsistency.
  • Stop when the application does not implement the retry, idempotency, timeout, and connection-discovery behavior required by the tested database.
  • Stop after any unexplained data divergence, failed integrity check, authentication bypass, certificate validation failure, or loss of a majority.
01

verification

Record SQL Server, database, and host state

read-only

Capture the exact SQL Server 2025 build, edition, database state/recovery model, current connections, host release, service health, and free capacity before modifying encryption or backups.

Why this step matters

Backup and TLS syntax is version-sensitive, and recovery expectations depend on database recovery model and page verification. The exact CU is part of the evidence.

What to understand

SQL Server 2025 CU7 build 17.0.4065.4 is the reviewed baseline dated 2026-07-16; revalidate the current supported CU before production use.

This guide targets SQL Server on Ubuntu 24.04 and client-to-server TLS. Microsoft documents that TLS is not supported for Always On availability-group database-mirroring endpoints.

System changes

  • Read-only server/database metadata queries; no SQL or host state changes.

Syntax explained

-N
Requests an encrypted sqlcmd connection; certificate validation still depends on client trust and hostname.
SERVERPROPERTY
Returns the engine's actual product version/level rather than an assumed package label.
Command
Fill variables0/4 ready

Values stay on this page and are never sent or saved.

sqlcmd -S 'tcp:{{sqlHost}},{{sqlPort}}' -U {{sqlAdminLogin}} -N -Q "SET NOCOUNT ON; SELECT SERVERPROPERTY('ProductVersion') AS product_version, SERVERPROPERTY('ProductLevel') AS product_level, SERVERPROPERTY('Edition') AS edition; SELECT name,state_desc,recovery_model_desc,page_verify_option_desc FROM sys.databases WHERE name=N'{{sqlDatabase}}';"
Example output / evidence
product_version  product_level  edition
---------------  -------------  -----------------
17.0.4065.4      CU7            Developer Edition (64-bit)
name       state_desc  recovery_model_desc  page_verify_option_desc
---------- ----------- -------------------- -----------------------
Orders     ONLINE      FULL                 CHECKSUM

Checkpoint: Checkpoint: Record SQL Server, database, and host state

Continue whenThe approved current 17.x CU runs, the target database is ONLINE with CHECKSUM page verification, and the host has healthy capacity.

Stop whenThe build is unsupported/unexpected, database is suspect/read-only unexpectedly, page verification is not CHECKSUM, or another backup/restore/incident is active.

If this step fails

The backup job succeeds but no usable off-host recovery point exists.

Likely causeFiles remain on the database host, retention/replication is not monitored, the encryption certificate/private key was not escrowed, or no actual restore drill occurred.

Safe checks
  • Compare local and off-host checksums/manifests.
  • Verify immutable retention and access logs at the independent destination.
  • Review the latest completed restore-drill evidence and RTO.

ResolutionTransfer the backup and certificate escrow through the approved secure channel, verify checksums, and complete an isolated restore before claiming recoverability.

Security notes

  • Keep administrative endpoints on private networks, authenticate every operator, and record who approved the change window. TLS without identity validation protects confidentiality but not server authenticity.
  • Supply passwords and private-key passphrases through an approved secret manager or an interactive prompt. Never place them in shell history, process arguments, tutorial variables, logs, tickets, or screenshots.
  • Use a dedicated low-privilege application identity and a separate short-lived administrative identity. A successful superuser test does not prove the application permission boundary.
  • Treat replication, snapshots, and backups as different controls. Replication improves availability; only a protected, retained, independently restored copy establishes recoverability.

Alternatives

  • Rehearse this step on a disposable environment with production-shaped data and topology before changing production.

Stop conditions

  • Stop when the observed state differs from the documented prerequisite or concrete expected evidence.
  • Stop when the independent recovery path is unavailable or an unrelated production incident is active.
02

verification

Validate the client-TLS certificate and key boundary

read-only

Verify the CA chain, SAN, expiry, and service readability of the PEM certificate/key. Keep this network certificate distinct from SQL backup-encryption certificates stored inside master.

Why this step matters

Strict encryption protects client sessions only when the client validates a certificate for the requested DNS identity. The private key needs narrow service access.

What to understand

Use a CA-signed production certificate; self-signed examples in vendor documentation are for demonstration and do not establish trustworthy identity.

The TLS certificate secures network transport. A separate SQL certificate and private key must accompany encrypted backups to a restore target.

System changes

  • Read-only certificate and permission checks.

Syntax explained

openssl verify
Validates the certificate chain using the explicit approved CA.
sudo -u mssql test -r
Confirms the service account can read material without granting broader access.
Command
Fill variables0/3 ready

Values stay on this page and are never sent or saved.

openssl verify -CAfile {{sqlCaFile}} {{sqlTlsCertificate}}; openssl x509 -in {{sqlTlsCertificate}} -noout -subject -issuer -dates -ext subjectAltName; sudo -u mssql test -r {{sqlTlsCertificate}} -a -r {{sqlTlsKey}} && echo 'mssql can read TLS material'
Example output / evidence
{{sqlTlsCertificate}}: OK
subject=CN=sql01.example.net
issuer=CN=Database Network CA
X509v3 Subject Alternative Name: DNS:sql01.example.net,DNS:orders-db.example.net
mssql can read TLS material

Checkpoint: Checkpoint: Validate the client-TLS certificate and key boundary

Continue whenChain/SAN/lifetime are valid and only the mssql service plus approved administrators can read the key.

Stop whenA SAN is missing, CA is untrusted, key permissions are broad, or rotation/rollback material is absent.

If this step fails

mssql-server does not start after TLS configuration.

Likely causeThe certificate/key path, PEM format, private-key permission, SAN, strict-encryption setting, or OpenSSL/SQL Server compatibility is invalid.

Safe checks
  • systemctl status mssql-server --no-pager
  • journalctl -u mssql-server -n 200 --no-pager
  • sudo -u mssql test -r {{sqlTlsCertificate}} -a -r {{sqlTlsKey}}

ResolutionRestore the saved mssql.conf and certificate paths, start the instance, then correct certificate material offline before another approved restart.

Security notes

  • Keep administrative endpoints on private networks, authenticate every operator, and record who approved the change window. TLS without identity validation protects confidentiality but not server authenticity.
  • Supply passwords and private-key passphrases through an approved secret manager or an interactive prompt. Never place them in shell history, process arguments, tutorial variables, logs, tickets, or screenshots.
  • Use a dedicated low-privilege application identity and a separate short-lived administrative identity. A successful superuser test does not prove the application permission boundary.
  • Treat replication, snapshots, and backups as different controls. Replication improves availability; only a protected, retained, independently restored copy establishes recoverability.

Alternatives

  • Rehearse this step on a disposable environment with production-shaped data and topology before changing production.

Stop conditions

  • Stop when the observed state differs from the documented prerequisite or concrete expected evidence.
  • Stop when the independent recovery path is unavailable or an unrelated production incident is active.
03

command

Enable SQL Server 2025 Force Strict Encryption

danger

Save the current configuration, configure the CA-signed certificate and key with mssql-conf, enable forceencryption and forcestrict, then restart during the approved client-compatible window.

Why this step matters

SQL Server 2025 Force Strict Encryption uses TDS 8.0 and validated modern TLS behavior. It intentionally rejects clients that cannot meet the security contract.

What to understand

Inventory every driver and job first. SQL Server Agent, linked servers, replication, log shipping, and tooling have component-specific secure-by-default behavior in 2025.

TLS 1.3 is enabled by default in SQL Server 2025 on supported Linux. Do not claim this setting encrypts Always On database-mirroring endpoints; Microsoft explicitly excludes them.

System changes

  • Changes network TLS certificate/key and forces encrypted strict client sessions, then restarts the database engine.

Syntax explained

network.forceencryption 1
Requires encrypted client connections.
network.forcestrict 1
Requires SQL Server 2025 strict encryption/TDS 8.0 semantics.
Command
Fill variables0/2 ready

Values stay on this page and are never sent or saved.

sudo cp --preserve=all /var/opt/mssql/mssql.conf /var/opt/mssql/mssql.conf.pre-wyd302-050; sudo /opt/mssql/bin/mssql-conf set network.tlscert {{sqlTlsCertificate}}; sudo /opt/mssql/bin/mssql-conf set network.tlskey {{sqlTlsKey}}; sudo /opt/mssql/bin/mssql-conf set network.forceencryption 1; sudo /opt/mssql/bin/mssql-conf set network.forcestrict 1; sudo systemctl restart mssql-server; systemctl is-active mssql-server
Example output / evidence
SQL Server needs to be restarted in order to apply this setting. Please run 'systemctl restart mssql-server.service'.
active

Checkpoint: Checkpoint: Enable SQL Server 2025 Force Strict Encryption

Continue whenThe service restarts, all approved clients connect with validated certificates, and no trust bypass is required.

Stop whenA critical supported client lacks strict support, service fails, SAN validation fails, or application SLO is exceeded.

If this step fails

A client fails after Force Strict Encryption is enabled.

Likely causeThe driver lacks TDS 8.0/strict support, does not trust the CA, uses a hostname absent from SAN, or still requests TrustServerCertificate.

Safe checks
  • openssl s_client -connect {{sqlHost}}:{{sqlPort}} -servername {{sqlHost}} -CAfile {{sqlCaFile}} -verify_return_error </dev/null
  • Review the driver's supported encryption matrix and effective connection string without credentials.
  • Query sys.dm_exec_connections from a known supported client.

ResolutionUpgrade/configure the supported driver, distribute the CA, and use the certificate DNS identity. If strict rollout must pause, restore the reviewed previous server setting rather than bypassing validation.

Security notes

  • Keep administrative endpoints on private networks, authenticate every operator, and record who approved the change window. TLS without identity validation protects confidentiality but not server authenticity.
  • Supply passwords and private-key passphrases through an approved secret manager or an interactive prompt. Never place them in shell history, process arguments, tutorial variables, logs, tickets, or screenshots.
  • Use a dedicated low-privilege application identity and a separate short-lived administrative identity. A successful superuser test does not prove the application permission boundary.
  • Treat replication, snapshots, and backups as different controls. Replication improves availability; only a protected, retained, independently restored copy establishes recoverability.

Alternatives

  • Stage mandatory validated TLS 1.2 with current drivers before enabling strict TDS 8.0 when a documented legacy dependency requires phased remediation.

Stop conditions

  • Stop before a disruptive test when monitoring, a named incident commander, a current backup, or a tested recovery channel is missing.
  • Stop when nodes disagree about identity, time, version, topology, or trust anchors; do not use force, trust bypasses, or quorum overrides to conceal the inconsistency.
  • Stop when the application does not implement the retry, idempotency, timeout, and connection-discovery behavior required by the tested database.
  • Stop after any unexplained data divergence, failed integrity check, authentication bypass, certificate validation failure, or loss of a majority.
04

verification

Prove encryption on the actual SQL session

read-only

Connect with a strict-capable, CA-trusting current driver using the DNS SAN, then query the connection DMV for encryption and protocol evidence. Do not use TrustServerCertificate.

Why this step matters

A restarted service and configured file do not prove the connection used by operators or applications is encrypted and authenticated as intended.

What to understand

Run an equivalent connection check from each driver family, scheduler, monitoring agent, and application pool.

Review the effective connection string for Encrypt=Strict and TrustServerCertificate=False semantics without logging credentials.

System changes

  • Read-only connection DMV query.

Syntax explained

encrypt_option
Reports whether the current TDS session is encrypted.
session_id=@@SPID
Scopes evidence to the exact test connection.
Command
Fill variables0/3 ready

Values stay on this page and are never sent or saved.

sqlcmd -S 'tcp:{{sqlHost}},{{sqlPort}}' -U {{sqlAdminLogin}} -N -Q "SET NOCOUNT ON; SELECT encrypt_option, net_transport, protocol_type, auth_scheme, client_net_address FROM sys.dm_exec_connections WHERE session_id=@@SPID;"
Example output / evidence
encrypt_option net_transport protocol_type auth_scheme client_net_address
-------------- ------------- ------------- ----------- ------------------
TRUE           TCP           TSQL          SQL         10.40.2.25

Checkpoint: Checkpoint: Prove encryption on the actual SQL session

Continue whenencrypt_option is TRUE through the intended TCP endpoint and every production client validates the certificate.

Stop whenAny client uses TrustServerCertificate, IP address outside SAN, plaintext, or an unsupported driver.

If this step fails

A client fails after Force Strict Encryption is enabled.

Likely causeThe driver lacks TDS 8.0/strict support, does not trust the CA, uses a hostname absent from SAN, or still requests TrustServerCertificate.

Safe checks
  • openssl s_client -connect {{sqlHost}}:{{sqlPort}} -servername {{sqlHost}} -CAfile {{sqlCaFile}} -verify_return_error </dev/null
  • Review the driver's supported encryption matrix and effective connection string without credentials.
  • Query sys.dm_exec_connections from a known supported client.

ResolutionUpgrade/configure the supported driver, distribute the CA, and use the certificate DNS identity. If strict rollout must pause, restore the reviewed previous server setting rather than bypassing validation.

Security notes

  • Keep administrative endpoints on private networks, authenticate every operator, and record who approved the change window. TLS without identity validation protects confidentiality but not server authenticity.
  • Supply passwords and private-key passphrases through an approved secret manager or an interactive prompt. Never place them in shell history, process arguments, tutorial variables, logs, tickets, or screenshots.
  • Use a dedicated low-privilege application identity and a separate short-lived administrative identity. A successful superuser test does not prove the application permission boundary.
  • Treat replication, snapshots, and backups as different controls. Replication improves availability; only a protected, retained, independently restored copy establishes recoverability.

Alternatives

  • Rehearse this step on a disposable environment with production-shaped data and topology before changing production.

Stop conditions

  • Stop when the observed state differs from the documented prerequisite or concrete expected evidence.
  • Stop when the independent recovery path is unavailable or an unrelated production incident is active.
05

verification

Audit logins, mappings, and fixed-role membership

read-only

List enabled SQL/Windows/External logins and server-role membership, then map the application's current database user and effective permissions before replacing excess access.

Why this step matters

Least privilege starts with observable current grants. SQL Server permissions can arrive through server roles, database roles, ownership, explicit grants, and execution context.

What to understand

Disable or rotate unused privileged logins only after proving ownership and recovery access.

Separate backup operator, security administrator, database owner, and application duties; fixed roles are often broader than needed.

System changes

  • Read-only catalog queries; no principal or permission changes.

Syntax explained

sys.server_role_members
Enumerates inherited fixed/user-defined server-role membership.
is_disabled
Distinguishes retained but disabled principals from active entry points.
Command
Fill variables0/3 ready

Values stay on this page and are never sent or saved.

sqlcmd -S 'tcp:{{sqlHost}},{{sqlPort}}' -U {{sqlAdminLogin}} -N -Q "SET NOCOUNT ON; SELECT name,type_desc,is_disabled FROM sys.server_principals WHERE type IN ('S','U','G','E','X') AND name NOT LIKE '##%'; SELECT r.name AS role_name,m.name AS member_name FROM sys.server_role_members rm JOIN sys.server_principals r ON r.principal_id=rm.role_principal_id JOIN sys.server_principals m ON m.principal_id=rm.member_principal_id ORDER BY r.name,m.name;"
Example output / evidence
name          type_desc  is_disabled
-------------  ---------  -----------
sa             SQL_LOGIN  1
ops_admin      SQL_LOGIN  0
orders_app     SQL_LOGIN  0
role_name  member_name
---------  -----------
securityadmin ops_admin

Checkpoint: Checkpoint: Audit logins, mappings, and fixed-role membership

Continue whenEvery enabled login has an owner/purpose, application identity is not sysadmin/securityadmin, and break-glass access is controlled.

Stop whenAn unknown privileged login exists, sa is unexpectedly active, or no independent recovery administrator remains.

If this step fails

The application can connect but receives permission denied.

Likely causeThe login is not mapped to the intended database user, schema ownership differs, a required stored procedure lacks permission, or the application previously depended on db_owner/sysadmin.

Safe checks
  • EXECUTE AS USER = '{{sqlAppUser}}'; SELECT USER_NAME(); REVERT;
  • SELECT * FROM fn_my_permissions(NULL, 'DATABASE');
  • Review failed audit events for the exact object and operation.

ResolutionGrant the smallest missing permission on the exact schema/object or signed module, then repeat positive and negative tests. Do not grant db_owner as a diagnostic shortcut.

Security notes

  • Keep administrative endpoints on private networks, authenticate every operator, and record who approved the change window. TLS without identity validation protects confidentiality but not server authenticity.
  • Supply passwords and private-key passphrases through an approved secret manager or an interactive prompt. Never place them in shell history, process arguments, tutorial variables, logs, tickets, or screenshots.
  • Use a dedicated low-privilege application identity and a separate short-lived administrative identity. A successful superuser test does not prove the application permission boundary.
  • Treat replication, snapshots, and backups as different controls. Replication improves availability; only a protected, retained, independently restored copy establishes recoverability.

Alternatives

  • Rehearse this step on a disposable environment with production-shaped data and topology before changing production.

Stop conditions

  • Stop when the observed state differs from the documented prerequisite or concrete expected evidence.
  • Stop when the independent recovery path is unavailable or an unrelated production incident is active.
06

config

Create a login and database user without broad fixed roles

caution

Execute this parameterized T-SQL only through the approved secret-aware editor/session. Create a login, map it to one database user, create a narrow role, and grant schema read plus selected procedure execution instead of db_owner.

Why this step matters

A workload should receive only the operations and schemas it needs. Password policy helps credential quality but does not replace secret rotation and network controls.

What to understand

Never save the rendered script containing the password. Prefer integrated Microsoft Entra/Active Directory identity when the deployment supports and operates it correctly.

Ownership chaining and signed stored procedures can expose controlled operations without granting direct table modification.

System changes

  • Creates one server login, one database user, one database role, and schema-scoped SELECT/EXECUTE grants.

Syntax explained

CHECK_POLICY / CHECK_EXPIRATION
Applies Windows password policy behavior supported by the SQL login environment.
GRANT ... ON SCHEMA
Scopes permissions to objects in one schema rather than the entire database.
File admin-session.sql
Configuration
Fill variables0/7 ready

Values stay on this page and are never sent or saved.

USE [master];
CREATE LOGIN [{{sqlAppLogin}}] WITH PASSWORD = N'{{sqlAppPassword}}', CHECK_POLICY = ON, CHECK_EXPIRATION = ON, DEFAULT_DATABASE = [{{sqlDatabase}}];
USE [{{sqlDatabase}}];
CREATE USER [{{sqlAppUser}}] FOR LOGIN [{{sqlAppLogin}}];
CREATE ROLE [{{sqlAppRole}}] AUTHORIZATION [dbo];
GRANT SELECT ON SCHEMA::[{{sqlReadSchema}}] TO [{{sqlAppRole}}];
GRANT EXECUTE ON SCHEMA::[{{sqlProcedureSchema}}] TO [{{sqlAppRole}}];
ALTER ROLE [{{sqlAppRole}}] ADD MEMBER [{{sqlAppUser}}];
Example output / evidence
Commands completed successfully.

Checkpoint: Checkpoint: Create a login and database user without broad fixed roles

Continue whenThe application can perform its approved read/procedure matrix and receives denial for ALTER, DROP, backup, and unrelated schemas.

Stop whenThe rendered secret is logged, the app requires db_owner/sysadmin, or schema ownership makes the grant broader than reviewed.

If this step fails

The application can connect but receives permission denied.

Likely causeThe login is not mapped to the intended database user, schema ownership differs, a required stored procedure lacks permission, or the application previously depended on db_owner/sysadmin.

Safe checks
  • EXECUTE AS USER = '{{sqlAppUser}}'; SELECT USER_NAME(); REVERT;
  • SELECT * FROM fn_my_permissions(NULL, 'DATABASE');
  • Review failed audit events for the exact object and operation.

ResolutionGrant the smallest missing permission on the exact schema/object or signed module, then repeat positive and negative tests. Do not grant db_owner as a diagnostic shortcut.

Security notes

  • Keep administrative endpoints on private networks, authenticate every operator, and record who approved the change window. TLS without identity validation protects confidentiality but not server authenticity.
  • Supply passwords and private-key passphrases through an approved secret manager or an interactive prompt. Never place them in shell history, process arguments, tutorial variables, logs, tickets, or screenshots.
  • Use a dedicated low-privilege application identity and a separate short-lived administrative identity. A successful superuser test does not prove the application permission boundary.
  • Treat replication, snapshots, and backups as different controls. Replication improves availability; only a protected, retained, independently restored copy establishes recoverability.

Alternatives

  • Rehearse this step on a disposable environment with production-shaped data and topology before changing production.

Stop conditions

  • Stop when the observed state differs from the documented prerequisite or concrete expected evidence.
  • Stop when the independent recovery path is unavailable or an unrelated production incident is active.
07

verification

Behaviorally test allowed and denied operations

read-only

Connect as the application login over strict TLS, run one approved query/procedure, and attempt a harmless forbidden metadata-changing operation inside a transaction that must be denied.

Why this step matters

Catalog inspection is not enough: the real application identity must succeed for required behavior and fail safely for an explicitly forbidden action.

What to understand

Use a forbidden statement that cannot commit because authorization should reject it before execution; keep the transaction rollback as an additional guard.

Capture audit evidence and verify no sensitive row values are copied into the change ticket.

System changes

  • Reads one approved validation row; the forbidden CREATE TABLE must not execute.

Syntax explained

-d {{sqlDatabase}}
Tests the identity in the exact application database context.
BEGIN TRAN / ROLLBACK
Adds a safety boundary but does not replace the required permission denial.
Command
Fill variables0/7 ready

Values stay on this page and are never sent or saved.

sqlcmd -S 'tcp:{{sqlHost}},{{sqlPort}}' -U {{sqlAppLogin}} -N -d {{sqlDatabase}} -Q "SET NOCOUNT ON; SELECT TOP (1) * FROM [{{sqlReadSchema}}].[{{sqlValidationTable}}] ORDER BY [{{sqlValidationKey}}]; BEGIN TRAN; CREATE TABLE dbo.permission_probe(id int); ROLLBACK;"
Example output / evidence
id  status
--  ------
1   ready
Msg 262, Level 14, State 1
CREATE TABLE permission denied in database 'Orders'.

Checkpoint: Checkpoint: Behaviorally test allowed and denied operations

Continue whenApproved access succeeds and CREATE TABLE returns permission denied with no object created.

Stop whenRequired access fails, forbidden DDL succeeds, or the login can access an unrelated database.

If this step fails

The application can connect but receives permission denied.

Likely causeThe login is not mapped to the intended database user, schema ownership differs, a required stored procedure lacks permission, or the application previously depended on db_owner/sysadmin.

Safe checks
  • EXECUTE AS USER = '{{sqlAppUser}}'; SELECT USER_NAME(); REVERT;
  • SELECT * FROM fn_my_permissions(NULL, 'DATABASE');
  • Review failed audit events for the exact object and operation.

ResolutionGrant the smallest missing permission on the exact schema/object or signed module, then repeat positive and negative tests. Do not grant db_owner as a diagnostic shortcut.

Security notes

  • Keep administrative endpoints on private networks, authenticate every operator, and record who approved the change window. TLS without identity validation protects confidentiality but not server authenticity.
  • Supply passwords and private-key passphrases through an approved secret manager or an interactive prompt. Never place them in shell history, process arguments, tutorial variables, logs, tickets, or screenshots.
  • Use a dedicated low-privilege application identity and a separate short-lived administrative identity. A successful superuser test does not prove the application permission boundary.
  • Treat replication, snapshots, and backups as different controls. Replication improves availability; only a protected, retained, independently restored copy establishes recoverability.

Alternatives

  • Rehearse this step on a disposable environment with production-shaped data and topology before changing production.

Stop conditions

  • Stop when the observed state differs from the documented prerequisite or concrete expected evidence.
  • Stop when the independent recovery path is unavailable or an unrelated production incident is active.
08

config

Create and escrow the backup-encryption certificate

caution

In master, create a database master key if policy requires one, create a dedicated backup certificate, and export its public certificate plus encrypted private key. Transfer both off-host with a separate secret.

Why this step matters

An encrypted backup is recoverable only with the matching certificate private key. Keeping that key only inside the source master database creates a circular recovery dependency.

What to understand

Use a certificate dedicated to backup encryption, track thumbprint/expiry, and rotate with retained old keys for every backup still inside retention.

Transfer the exported certificate/key and master-key recovery material to separately controlled off-host escrow; the encryption password travels through another channel.

System changes

  • May create a master database key and backup certificate, then writes certificate and encrypted private-key files on the SQL Server host.

Syntax explained

BACKUP CERTIFICATE ... WITH PRIVATE KEY
Exports the public certificate and password-encrypted private key required by a different restore instance.
EXPIRY_DATE
Creates an explicit lifecycle deadline; retained backups may still require expired historical certificates.
File backup-certificate.sql
Configuration
Fill variables0/7 ready

Values stay on this page and are never sent or saved.

USE [master];
IF NOT EXISTS (SELECT 1 FROM sys.symmetric_keys WHERE name = '##MS_DatabaseMasterKey##')
  CREATE MASTER KEY ENCRYPTION BY PASSWORD = N'{{sqlMasterKeyPassword}}';
IF NOT EXISTS (SELECT 1 FROM sys.certificates WHERE name = N'{{sqlBackupCertificate}}')
  CREATE CERTIFICATE [{{sqlBackupCertificate}}] WITH SUBJECT = N'{{sqlBackupCertificateSubject}}', EXPIRY_DATE = '{{sqlBackupCertificateExpiry}}';
BACKUP CERTIFICATE [{{sqlBackupCertificate}}]
  TO FILE = N'{{sqlBackupCertificateFile}}'
  WITH PRIVATE KEY (FILE = N'{{sqlBackupPrivateKeyFile}}', ENCRYPTION BY PASSWORD = N'{{sqlBackupKeyPassword}}');
Example output / evidence
The certificate, private key, and metadata were successfully written to file.

Checkpoint: Checkpoint: Create and escrow the backup-encryption certificate

Continue whenCertificate thumbprint is recorded, files are restricted, off-host escrow checksum matches, and the separate decryption password is recoverable.

Stop whenThe key exists only on the source host, files are broadly readable, password is stored beside them, or rotation would delete a key needed by retained backups.

If this step fails

RESTORE reports that the encrypted backup cannot find the server certificate.

Likely causeThe target master database lacks the backup-encryption certificate and private key, the wrong certificate was imported, or its private-key protection password is unavailable.

Safe checks
  • SELECT name, thumbprint, expiry_date FROM master.sys.certificates WHERE name = '{{sqlBackupCertificate}}';
  • Compare certificate thumbprint with the backup manifest.
  • RESTORE HEADERONLY FROM DISK = N'{{sqlBackupFile}}';

ResolutionImport the matching certificate and private key from the separately protected off-host escrow into master, then retry. Without it, the encrypted backup is unrecoverable.

Security notes

  • Keep administrative endpoints on private networks, authenticate every operator, and record who approved the change window. TLS without identity validation protects confidentiality but not server authenticity.
  • Supply passwords and private-key passphrases through an approved secret manager or an interactive prompt. Never place them in shell history, process arguments, tutorial variables, logs, tickets, or screenshots.
  • Use a dedicated low-privilege application identity and a separate short-lived administrative identity. A successful superuser test does not prove the application permission boundary.
  • Treat replication, snapshots, and backups as different controls. Replication improves availability; only a protected, retained, independently restored copy establishes recoverability.

Alternatives

  • Rehearse this step on a disposable environment with production-shaped data and topology before changing production.

Stop conditions

  • Stop when the observed state differs from the documented prerequisite or concrete expected evidence.
  • Stop when the independent recovery path is unavailable or an unrelated production incident is active.
09

command

Create an encrypted checksum copy-only backup

caution

Take a full COPY_ONLY backup for this drill so the normal differential baseline is unchanged. Refuse an existing destination before using FORMAT/INIT, and require AES-256 backup encryption, backup checksums, compression, and useful progress evidence.

Why this step matters

COPY_ONLY creates a realistic full restore point without changing the normal differential base. Encryption protects stolen media, and CHECKSUM adds corruption detection.

What to understand

The scheduled backup system should use its reviewed normal full/differential/log sequence; COPY_ONLY here is intentionally isolated from that chain.

SQL Server Express and Web edition limitations must be checked before relying on backup encryption.

FORMAT/INIT would overwrite media headers, so the host-side nonexistence check and a unique approved path are mandatory. Never reuse a retained backup filename.

System changes

  • Reads the live database and creates one new explicitly named encrypted backup file; the command refuses an existing destination and consumes I/O and storage.

Syntax explained

COPY_ONLY
Does not become the differential base or disturb the normal backup sequence.
CHECKSUM
Validates available page checksums while reading and writes a backup checksum.
ENCRYPTION AES_256
Encrypts backup media with a dedicated server certificate.
Command
Fill variables0/6 ready

Values stay on this page and are never sent or saved.

sudo -u mssql test ! -e {{sqlBackupFile}} && sqlcmd -S 'tcp:{{sqlHost}},{{sqlPort}}' -U {{sqlBackupLogin}} -N -Q "BACKUP DATABASE [{{sqlDatabase}}] TO DISK = N'{{sqlBackupFile}}' WITH COPY_ONLY, FORMAT, INIT, COMPRESSION, CHECKSUM, ENCRYPTION (ALGORITHM = AES_256, SERVER CERTIFICATE = [{{sqlBackupCertificate}}]), STATS = 10;"
Example output / evidence
10 percent processed.
50 percent processed.
100 percent processed.
Processed 12800 pages for database 'Orders', file 'Orders' on file 1.
BACKUP DATABASE successfully processed 12800 pages in 4.212 seconds (23.740 MB/sec).

Checkpoint: Checkpoint: Create an encrypted checksum copy-only backup

Continue whenBACKUP DATABASE reports success, msdb records checksum/encryption metadata, and workload latency stays inside the approved budget.

Stop whenThe destination already exists, any checksum/page error occurs, storage fills, backup is unencrypted, or production latency exceeds the abort threshold.

If this step fails

BACKUP DATABASE fails with operating system error 5 or path not found.

Likely causeThe path is evaluated on the SQL Server host, the mssql service cannot write it, the mount is absent, or policy forbids the destination.

Safe checks
  • sudo -u mssql test -d {{sqlBackupDirectory}} -a -w {{sqlBackupDirectory}}
  • findmnt {{sqlBackupDirectory}}
  • journalctl -u mssql-server -n 100 --no-pager

ResolutionCreate/mount the approved host-side backup directory with least privilege and sufficient capacity. Do not grant broad filesystem permissions.

Security notes

  • Keep administrative endpoints on private networks, authenticate every operator, and record who approved the change window. TLS without identity validation protects confidentiality but not server authenticity.
  • Supply passwords and private-key passphrases through an approved secret manager or an interactive prompt. Never place them in shell history, process arguments, tutorial variables, logs, tickets, or screenshots.
  • Use a dedicated low-privilege application identity and a separate short-lived administrative identity. A successful superuser test does not prove the application permission boundary.
  • Treat replication, snapshots, and backups as different controls. Replication improves availability; only a protected, retained, independently restored copy establishes recoverability.

Alternatives

  • Rehearse this step on a disposable environment with production-shaped data and topology before changing production.

Stop conditions

  • Stop when the observed state differs from the documented prerequisite or concrete expected evidence.
  • Stop when the independent recovery path is unavailable or an unrelated production incident is active.
10

verification

Create a protected off-host backup manifest

read-only

Record backup set metadata, checksum/encryption state, certificate thumbprint, file hash/size, database validation counts, and RPO timestamp, then transfer backup and escrow material independently off-host.

Why this step matters

A manifest binds the artifact to its intended database, recovery point, encryption key, and validation expectations. A filename alone is not trustworthy recovery evidence.

What to understand

Transfer through the approved channel and compare hashes at the independent destination.

Store certificate/private key escrow separately from backup media and audit who can combine them.

System changes

  • Reads msdb metadata and the backup file to calculate a hash; off-host transfer creates an independent retained copy.

Syntax explained

has_backup_checksums
Proves the backup was created with checksums required by later CHECKSUM verification.
encryptor_thumbprint
Identifies the certificate whose private key is required for restore.
Command
Fill variables0/5 ready

Values stay on this page and are never sent or saved.

sqlcmd -S 'tcp:{{sqlHost}},{{sqlPort}}' -U {{sqlBackupLogin}} -N -Q "SELECT TOP (1) database_name,backup_start_date,backup_finish_date,type,is_copy_only,has_backup_checksums,key_algorithm,encryptor_thumbprint,backup_size,compressed_backup_size FROM msdb.dbo.backupset WHERE database_name=N'{{sqlDatabase}}' ORDER BY backup_finish_date DESC;"; sha256sum {{sqlBackupFile}}
Example output / evidence
database_name backup_finish_date type is_copy_only has_backup_checksums key_algorithm
------------- ------------------ ---- ------------ -------------------- -------------
Orders        2026-07-28 12:40:14 D    1            1                    AES_256
62a2e946c4a3d97a425d6fb4e36b090f4470780fd0451c6fe4a8156270377b58  /srv/sql-backup/orders-copyonly-20260728.bak

Checkpoint: Checkpoint: Create a protected off-host backup manifest

Continue whenManifest reports copy_only=1, checksums=1, AES_256, expected thumbprint, and matching local/off-host SHA-256.

Stop whenMetadata is missing, hash changes, off-host retention is mutable/uncontrolled, or certificate escrow is incomplete.

If this step fails

The backup job succeeds but no usable off-host recovery point exists.

Likely causeFiles remain on the database host, retention/replication is not monitored, the encryption certificate/private key was not escrowed, or no actual restore drill occurred.

Safe checks
  • Compare local and off-host checksums/manifests.
  • Verify immutable retention and access logs at the independent destination.
  • Review the latest completed restore-drill evidence and RTO.

ResolutionTransfer the backup and certificate escrow through the approved secure channel, verify checksums, and complete an isolated restore before claiming recoverability.

Security notes

  • Keep administrative endpoints on private networks, authenticate every operator, and record who approved the change window. TLS without identity validation protects confidentiality but not server authenticity.
  • Supply passwords and private-key passphrases through an approved secret manager or an interactive prompt. Never place them in shell history, process arguments, tutorial variables, logs, tickets, or screenshots.
  • Use a dedicated low-privilege application identity and a separate short-lived administrative identity. A successful superuser test does not prove the application permission boundary.
  • Treat replication, snapshots, and backups as different controls. Replication improves availability; only a protected, retained, independently restored copy establishes recoverability.

Alternatives

  • Rehearse this step on a disposable environment with production-shaped data and topology before changing production.

Stop conditions

  • Stop when the observed state differs from the documented prerequisite or concrete expected evidence.
  • Stop when the independent recovery path is unavailable or an unrelated production incident is active.
11

verification

Run RESTORE VERIFYONLY with checksum—and state its limit

read-only

Verify that the selected backup set is complete/readable and its backup checksums validate. This is an early media check, not a restore test and not structural database validation.

Why this step matters

VERIFYONLY quickly detects unreadable or checksum-invalid media and ensures the target can decrypt the backup, but Microsoft states it does not restore or verify the data structure.

What to understand

Run on the actual recovery host after importing the matching backup certificate/private key into master.

CHECKSUM causes verification to fail if checksums are absent instead of silently proceeding without them.

System changes

  • Reads and decrypts the backup stream; may record backup history metadata but does not create the target database.

Syntax explained

RESTORE VERIFYONLY
Checks backup-set completeness and media readability without restoring.
CHECKSUM, STOP_ON_ERROR
Requires checksum validation and halts on the first detected error.
Command
Fill variables0/4 ready

Values stay on this page and are never sent or saved.

sqlcmd -S 'tcp:{{sqlRestoreHost}},{{sqlPort}}' -U {{sqlRestoreLogin}} -N -Q "RESTORE VERIFYONLY FROM DISK = N'{{sqlBackupFile}}' WITH CHECKSUM, STOP_ON_ERROR;"
Example output / evidence
The backup set on file 1 is valid.

Checkpoint: Checkpoint: Run RESTORE VERIFYONLY with checksum—and state its limit

Continue whenThe exact off-host copy reports 'backup set ... is valid' with CHECKSUM required.

Stop whenCertificate is missing, checksum is absent/fails, file hash differs, or anyone proposes VERIFYONLY as the final recovery proof.

If this step fails

RESTORE VERIFYONLY fails with a checksum or media error.

Likely causeThe backup is truncated/corrupt, storage changed it, the wrong file was selected, or required checksum/encryption metadata is unavailable.

Safe checks
  • sha256sum {{sqlBackupFile}}
  • RESTORE HEADERONLY FROM DISK = N'{{sqlBackupFile}}';
  • Inspect msdb.dbo.backupset for has_backup_checksums and encryption metadata.

ResolutionQuarantine the artifact, select an earlier known-good protected backup, investigate storage transfer/integrity, and never use CONTINUE_AFTER_ERROR for normal recovery acceptance.

Security notes

  • Keep administrative endpoints on private networks, authenticate every operator, and record who approved the change window. TLS without identity validation protects confidentiality but not server authenticity.
  • Supply passwords and private-key passphrases through an approved secret manager or an interactive prompt. Never place them in shell history, process arguments, tutorial variables, logs, tickets, or screenshots.
  • Use a dedicated low-privilege application identity and a separate short-lived administrative identity. A successful superuser test does not prove the application permission boundary.
  • Treat replication, snapshots, and backups as different controls. Replication improves availability; only a protected, retained, independently restored copy establishes recoverability.

Alternatives

  • Rehearse this step on a disposable environment with production-shaped data and topology before changing production.

Stop conditions

  • Stop when the observed state differs from the documented prerequisite or concrete expected evidence.
  • Stop when the independent recovery path is unavailable or an unrelated production incident is active.
12

verification

Inspect logical files and reserve isolated destinations

read-only

Use FILELISTONLY to obtain exact logical data/log names, confirm the isolated database does not exist, and reserve unused paths with sufficient capacity before RESTORE MOVE.

Why this step matters

MOVE uses logical names stored inside the backup. Guessing them or reusing live paths can collide with another database; REPLACE removes safeguards and is inappropriate for a drill.

What to understand

Choose a unique restore database and data/log filenames on a capacity-approved recovery volume.

Enumerate sys.master_files and host files. An empty DB_ID check alone cannot detect an unrelated filesystem collision.

System changes

  • Read-only backup metadata and target catalog inspection.

Syntax explained

RESTORE FILELISTONLY
Returns logical and physical file metadata required to build safe MOVE clauses.
no REPLACE
Retains SQL Server safeguards against overwriting a different database or file.
Command
Fill variables0/5 ready

Values stay on this page and are never sent or saved.

sqlcmd -S 'tcp:{{sqlRestoreHost}},{{sqlPort}}' -U {{sqlRestoreLogin}} -N -Q "RESTORE FILELISTONLY FROM DISK = N'{{sqlBackupFile}}'; SELECT name,physical_name FROM sys.master_files WHERE database_id=DB_ID(N'{{sqlRestoreDatabase}}');"
Example output / evidence
LogicalName PhysicalName                      Type
----------- --------------------------------- ----
Orders      /var/opt/mssql/data/Orders.mdf    D
Orders_log  /var/opt/mssql/data/Orders_log.ldf L
name physical_name
---- -------------
(0 rows affected)

Checkpoint: Checkpoint: Inspect logical files and reserve isolated destinations

Continue whenLogical names are recorded, isolated DB is absent, target files are unused, and capacity exceeds restored size plus working headroom.

Stop whenAny target name/path exists, logical names differ from the plan, or REPLACE is present.

If this step fails

RESTORE DATABASE would overwrite or collide with existing files.

Likely causeLogical file names or MOVE destinations are wrong, the isolated database already exists, or an unsafe REPLACE option was introduced.

Safe checks
  • RESTORE FILELISTONLY FROM DISK = N'{{sqlBackupFile}}';
  • SELECT name, physical_name FROM sys.master_files;
  • test -e {{sqlRestoreDataFile}} || true; test -e {{sqlRestoreLogFile}} || true

ResolutionChoose a new isolated database name and unused data/log paths derived from FILELISTONLY. Never add REPLACE merely to bypass collision safeguards.

Security notes

  • Keep administrative endpoints on private networks, authenticate every operator, and record who approved the change window. TLS without identity validation protects confidentiality but not server authenticity.
  • Supply passwords and private-key passphrases through an approved secret manager or an interactive prompt. Never place them in shell history, process arguments, tutorial variables, logs, tickets, or screenshots.
  • Use a dedicated low-privilege application identity and a separate short-lived administrative identity. A successful superuser test does not prove the application permission boundary.
  • Treat replication, snapshots, and backups as different controls. Replication improves availability; only a protected, retained, independently restored copy establishes recoverability.

Alternatives

  • Rehearse this step on a disposable environment with production-shaped data and topology before changing production.

Stop conditions

  • Stop when the observed state differs from the documented prerequisite or concrete expected evidence.
  • Stop when the independent recovery path is unavailable or an unrelated production incident is active.
13

command

Perform an actual isolated restore

danger

Restore the encrypted copy-only full backup under a unique database name with explicit MOVE destinations, CHECKSUM, and RECOVERY. Run on a dedicated recovery instance when possible.

Why this step matters

Only a real restore proves the target has the certificate, storage, permissions, engine compatibility, and time budget to materialize a database from the backup.

What to understand

Use a dedicated recovery instance to avoid production resource contention and to prove off-host independence.

RECOVERY brings the database online because this drill restores one copy-only full backup; full/log recovery sequences would use NORECOVERY until the final log.

System changes

  • Creates a new isolated database and data/log files, consuming target CPU, memory, I/O, and capacity.

Syntax explained

MOVE
Maps backup logical files to explicitly unused isolated target paths.
RECOVERY
Completes recovery and makes this one-backup restore readable.
no REPLACE
Preserves collision safeguards.
Command
Fill variables0/9 ready

Values stay on this page and are never sent or saved.

sqlcmd -S 'tcp:{{sqlRestoreHost}},{{sqlPort}}' -U {{sqlRestoreLogin}} -N -Q "RESTORE DATABASE [{{sqlRestoreDatabase}}] FROM DISK = N'{{sqlBackupFile}}' WITH MOVE N'{{sqlLogicalDataName}}' TO N'{{sqlRestoreDataFile}}', MOVE N'{{sqlLogicalLogName}}' TO N'{{sqlRestoreLogFile}}', CHECKSUM, STOP_ON_ERROR, RECOVERY, STATS = 10;"
Example output / evidence
10 percent processed.
50 percent processed.
100 percent processed.
Processed 12800 pages for database 'Orders_restore_20260728'.
RESTORE DATABASE successfully processed 12800 pages in 3.801 seconds.

Checkpoint: Checkpoint: Perform an actual isolated restore

Continue whenRestore processes all pages successfully and the isolated database becomes ONLINE at the measured RTO.

Stop whenTarget files collide, checksum fails, source production is targeted, capacity is pressured, or certificate decryption fails.

If this step fails

RESTORE DATABASE would overwrite or collide with existing files.

Likely causeLogical file names or MOVE destinations are wrong, the isolated database already exists, or an unsafe REPLACE option was introduced.

Safe checks
  • RESTORE FILELISTONLY FROM DISK = N'{{sqlBackupFile}}';
  • SELECT name, physical_name FROM sys.master_files;
  • test -e {{sqlRestoreDataFile}} || true; test -e {{sqlRestoreLogFile}} || true

ResolutionChoose a new isolated database name and unused data/log paths derived from FILELISTONLY. Never add REPLACE merely to bypass collision safeguards.

Security notes

  • Keep administrative endpoints on private networks, authenticate every operator, and record who approved the change window. TLS without identity validation protects confidentiality but not server authenticity.
  • Supply passwords and private-key passphrases through an approved secret manager or an interactive prompt. Never place them in shell history, process arguments, tutorial variables, logs, tickets, or screenshots.
  • Use a dedicated low-privilege application identity and a separate short-lived administrative identity. A successful superuser test does not prove the application permission boundary.
  • Treat replication, snapshots, and backups as different controls. Replication improves availability; only a protected, retained, independently restored copy establishes recoverability.

Alternatives

  • Restore on an ephemeral dedicated SQL Server 2025 recovery host using only off-host backup and key escrow; this is the strongest independence test.

Stop conditions

  • Stop before a disruptive test when monitoring, a named incident commander, a current backup, or a tested recovery channel is missing.
  • Stop when nodes disagree about identity, time, version, topology, or trust anchors; do not use force, trust bypasses, or quorum overrides to conceal the inconsistency.
  • Stop when the application does not implement the retry, idempotency, timeout, and connection-discovery behavior required by the tested database.
  • Stop after any unexplained data divergence, failed integrity check, authentication bypass, certificate validation failure, or loss of a majority.
14

verification

Run DBCC CHECKDB on the restored database

read-only

Perform physical and logical consistency validation on the isolated restored database. Preserve all error messages and never normalize a repair-with-data-loss option into routine recovery.

Why this step matters

VERIFYONLY does not verify data structure. CHECKDB on an actual restore tests allocation, catalog, and logical consistency at the selected recovery point.

What to understand

No output other than the completion line is expected with NO_INFOMSGS; any reported consistency error fails acceptance.

Prefer restoring another known-good backup over repair. REPAIR_ALLOW_DATA_LOSS can intentionally discard data and requires an exceptional incident decision.

System changes

  • Performs intensive read/worktable operations against only the isolated restored database.

Syntax explained

NO_INFOMSGS
Suppresses routine informational lines so errors are conspicuous.
ALL_ERRORMSGS
Returns all reported consistency errors for evidence.
Command
Fill variables0/4 ready

Values stay on this page and are never sent or saved.

sqlcmd -S 'tcp:{{sqlRestoreHost}},{{sqlPort}}' -U {{sqlRestoreLogin}} -N -d {{sqlRestoreDatabase}} -Q "DBCC CHECKDB (N'{{sqlRestoreDatabase}}') WITH NO_INFOMSGS, ALL_ERRORMSGS;"
Example output / evidence
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Checkpoint: Checkpoint: Run DBCC CHECKDB on the restored database

Continue whenCHECKDB completes with zero consistency errors inside the recovery time budget.

Stop whenAny consistency error appears, tempdb/capacity is pressured, or the command targets production.

If this step fails

RESTORE VERIFYONLY succeeds but DBCC CHECKDB fails after restore.

Likely causeVERIFYONLY proved media readability/completeness, not the logical/physical structure of restored pages, or corruption existed in the source.

Safe checks
  • DBCC CHECKDB (N'{{sqlRestoreDatabase}}') WITH NO_INFOMSGS, ALL_ERRORMSGS;
  • Review suspect_pages and SQL Server error log.
  • Repeat restore from an earlier independently retained backup.

ResolutionKeep the restored database isolated, preserve evidence, test another known-good backup, and follow Microsoft's corruption recovery guidance. Do not run repair with data loss on production without explicit incident approval.

Security notes

  • Keep administrative endpoints on private networks, authenticate every operator, and record who approved the change window. TLS without identity validation protects confidentiality but not server authenticity.
  • Supply passwords and private-key passphrases through an approved secret manager or an interactive prompt. Never place them in shell history, process arguments, tutorial variables, logs, tickets, or screenshots.
  • Use a dedicated low-privilege application identity and a separate short-lived administrative identity. A successful superuser test does not prove the application permission boundary.
  • Treat replication, snapshots, and backups as different controls. Replication improves availability; only a protected, retained, independently restored copy establishes recoverability.

Alternatives

  • Rehearse this step on a disposable environment with production-shaped data and topology before changing production.

Stop conditions

  • Stop when the observed state differs from the documented prerequisite or concrete expected evidence.
  • Stop when the independent recovery path is unavailable or an unrelated production incident is active.
15

verification

Verify deterministic data and application behavior

read-only

Compare manifest counts and a non-sensitive known business key, run representative stored procedures, verify compatibility/collation, and check that no production endpoint points at the isolated database.

Why this step matters

A structurally consistent database can still be the wrong recovery point or miss external dependencies. Deterministic application evidence completes the restore assertion.

What to understand

Use a manifest generated before backup with counts and synthetic IDs; avoid exposing customer row contents.

Validate jobs, credentials, linked services, encryption keys, and orphaned users as applicable, but never let the recovery drill contact production downstream systems.

System changes

  • Read-only queries against the isolated restored database.

Syntax explained

COUNT_BIG
Avoids integer overflow for large validation tables.
DB_NAME()
Proves the validation connection is on the isolated database, not production.
Command
Fill variables0/8 ready

Values stay on this page and are never sent or saved.

sqlcmd -S 'tcp:{{sqlRestoreHost}},{{sqlPort}}' -U {{sqlRestoreLogin}} -N -d {{sqlRestoreDatabase}} -Q "SET NOCOUNT ON; SELECT COUNT_BIG(*) AS expected_rows FROM [{{sqlReadSchema}}].[{{sqlValidationTable}}]; SELECT [{{sqlValidationKey}}] FROM [{{sqlReadSchema}}].[{{sqlValidationTable}}] WHERE [{{sqlValidationKey}}]=N'{{sqlKnownKey}}'; SELECT DB_NAME() AS database_name, DATABASEPROPERTYEX(DB_NAME(),'Status') AS status;"
Example output / evidence
expected_rows
-------------
428731
order_id
--------
synthetic-20260728
database_name             status
------------------------  ------
Orders_restore_20260728   ONLINE

Checkpoint: Checkpoint: Verify deterministic data and application behavior

Continue whenCounts and known key match the signed manifest, application queries pass, and DB_NAME is the isolated target.

Stop whenAny invariant differs, external side effects are possible, or the connection points to production.

If this step fails

The restored database is consistent but application validation fails.

Likely causeThe wrong backup point, collation, login mapping, compatibility level, encryption key, SQL Agent dependency, or external service is missing.

Safe checks
  • SELECT name, state_desc, compatibility_level, collation_name FROM sys.databases WHERE name='{{sqlRestoreDatabase}}';
  • Run deterministic count and known-key queries against the isolated database.
  • List orphaned users and required external dependencies.

ResolutionKeep production untouched, correct the recovery manifest or dependency plan, and repeat from a clearly identified backup generation.

Security notes

  • Keep administrative endpoints on private networks, authenticate every operator, and record who approved the change window. TLS without identity validation protects confidentiality but not server authenticity.
  • Supply passwords and private-key passphrases through an approved secret manager or an interactive prompt. Never place them in shell history, process arguments, tutorial variables, logs, tickets, or screenshots.
  • Use a dedicated low-privilege application identity and a separate short-lived administrative identity. A successful superuser test does not prove the application permission boundary.
  • Treat replication, snapshots, and backups as different controls. Replication improves availability; only a protected, retained, independently restored copy establishes recoverability.

Alternatives

  • Rehearse this step on a disposable environment with production-shaped data and topology before changing production.

Stop conditions

  • Stop when the observed state differs from the documented prerequisite or concrete expected evidence.
  • Stop when the independent recovery path is unavailable or an unrelated production incident is active.
16

command

Capture RPO/RTO evidence and remove only the drill database

danger

Preserve the manifest, timings, CHECKDB output, certificate thumbprint, and off-host hash, disconnect only drill sessions, then drop only the isolated database and securely retire temporary local files.

Why this step matters

A restore drill must leave no ambiguous database or exposed data, but cleanup is destructive and must be narrowly proven against the isolated identifier.

What to understand

Verify the database name twice and retain evidence before SINGLE_USER/DROP. Do not generalize this command with a wildcard or dynamic user input.

Keep retained off-host backup and key escrow according to policy; remove only temporary recovery-host copies when authorized.

System changes

  • Disconnects sessions from and permanently drops exactly the isolated restore database and its files.

Syntax explained

DB_ID guard
Executes cleanup only when the exact isolated database exists.
ROLLBACK IMMEDIATE
Disconnects drill sessions; it must never target a live database.
Command
Fill variables0/4 ready

Values stay on this page and are never sent or saved.

sqlcmd -S 'tcp:{{sqlRestoreHost}},{{sqlPort}}' -U {{sqlRestoreLogin}} -N -Q "IF DB_ID(N'{{sqlRestoreDatabase}}') IS NOT NULL BEGIN ALTER DATABASE [{{sqlRestoreDatabase}}] SET SINGLE_USER WITH ROLLBACK IMMEDIATE; DROP DATABASE [{{sqlRestoreDatabase}}]; END; SELECT DB_ID(N'{{sqlRestoreDatabase}}') AS restore_database_id;"
Example output / evidence
Nonqualified transactions are being rolled back. Estimated rollback completion: 100%.
restore_database_id
-------------------
NULL

Checkpoint: Checkpoint: Capture RPO/RTO evidence and remove only the drill database

Continue whenDB_ID returns NULL, production database remains ONLINE, and evidence plus retained recovery artifacts are complete.

Stop whenThe database name is not the approved isolated value, evidence is incomplete, or any production connection could be affected.

If this step fails

The restored database is consistent but application validation fails.

Likely causeThe wrong backup point, collation, login mapping, compatibility level, encryption key, SQL Agent dependency, or external service is missing.

Safe checks
  • SELECT name, state_desc, compatibility_level, collation_name FROM sys.databases WHERE name='{{sqlRestoreDatabase}}';
  • Run deterministic count and known-key queries against the isolated database.
  • List orphaned users and required external dependencies.

ResolutionKeep production untouched, correct the recovery manifest or dependency plan, and repeat from a clearly identified backup generation.

Security notes

  • Keep administrative endpoints on private networks, authenticate every operator, and record who approved the change window. TLS without identity validation protects confidentiality but not server authenticity.
  • Supply passwords and private-key passphrases through an approved secret manager or an interactive prompt. Never place them in shell history, process arguments, tutorial variables, logs, tickets, or screenshots.
  • Use a dedicated low-privilege application identity and a separate short-lived administrative identity. A successful superuser test does not prove the application permission boundary.
  • Treat replication, snapshots, and backups as different controls. Replication improves availability; only a protected, retained, independently restored copy establishes recoverability.

Alternatives

  • Keep the restored database OFFLINE and encrypted under a short approved retention window when auditors require independent inspection before deletion.

Stop conditions

  • Stop before a disruptive test when monitoring, a named incident commander, a current backup, or a tested recovery channel is missing.
  • Stop when nodes disagree about identity, time, version, topology, or trust anchors; do not use force, trust bypasses, or quorum overrides to conceal the inconsistency.
  • Stop when the application does not implement the retry, idempotency, timeout, and connection-discovery behavior required by the tested database.
  • Stop after any unexplained data divergence, failed integrity check, authentication bypass, certificate validation failure, or loss of a majority.

Finish line

Verification checklist

Encrypted current sessionsqlcmd -S 'tcp:{{sqlHost}},{{sqlPort}}' -U {{sqlAdminLogin}} -N -Q "SELECT encrypt_option FROM sys.dm_exec_connections WHERE session_id=@@SPID;"TRUE with certificate validation and no TrustServerCertificate.
Application denial boundaryConnect as {{sqlAppLogin}} and run the approved SELECT plus forbidden CREATE TABLE probe.SELECT succeeds; CREATE TABLE returns Msg 262 permission denied.
Restored consistencysqlcmd -S 'tcp:{{sqlRestoreHost}},{{sqlPort}}' -U {{sqlRestoreLogin}} -N -d {{sqlRestoreDatabase}} -Q "DBCC CHECKDB (N'{{sqlRestoreDatabase}}') WITH NO_INFOMSGS, ALL_ERRORMSGS;"Completion with zero consistency errors.
Backup metadataQuery the selected msdb.dbo.backupset row and compare the off-host SHA-256 manifest.COPY_ONLY, has_backup_checksums=1, AES_256, expected certificate thumbprint, and matching hash.

Recovery guidance

Common problems and safe checks

mssql-server does not start after TLS configuration.

Likely causeThe certificate/key path, PEM format, private-key permission, SAN, strict-encryption setting, or OpenSSL/SQL Server compatibility is invalid.

Safe checks
  • systemctl status mssql-server --no-pager
  • journalctl -u mssql-server -n 200 --no-pager
  • sudo -u mssql test -r {{sqlTlsCertificate}} -a -r {{sqlTlsKey}}

ResolutionRestore the saved mssql.conf and certificate paths, start the instance, then correct certificate material offline before another approved restart.

A client fails after Force Strict Encryption is enabled.

Likely causeThe driver lacks TDS 8.0/strict support, does not trust the CA, uses a hostname absent from SAN, or still requests TrustServerCertificate.

Safe checks
  • openssl s_client -connect {{sqlHost}}:{{sqlPort}} -servername {{sqlHost}} -CAfile {{sqlCaFile}} -verify_return_error </dev/null
  • Review the driver's supported encryption matrix and effective connection string without credentials.
  • Query sys.dm_exec_connections from a known supported client.

ResolutionUpgrade/configure the supported driver, distribute the CA, and use the certificate DNS identity. If strict rollout must pause, restore the reviewed previous server setting rather than bypassing validation.

The application can connect but receives permission denied.

Likely causeThe login is not mapped to the intended database user, schema ownership differs, a required stored procedure lacks permission, or the application previously depended on db_owner/sysadmin.

Safe checks
  • EXECUTE AS USER = '{{sqlAppUser}}'; SELECT USER_NAME(); REVERT;
  • SELECT * FROM fn_my_permissions(NULL, 'DATABASE');
  • Review failed audit events for the exact object and operation.

ResolutionGrant the smallest missing permission on the exact schema/object or signed module, then repeat positive and negative tests. Do not grant db_owner as a diagnostic shortcut.

BACKUP DATABASE fails with operating system error 5 or path not found.

Likely causeThe path is evaluated on the SQL Server host, the mssql service cannot write it, the mount is absent, or policy forbids the destination.

Safe checks
  • sudo -u mssql test -d {{sqlBackupDirectory}} -a -w {{sqlBackupDirectory}}
  • findmnt {{sqlBackupDirectory}}
  • journalctl -u mssql-server -n 100 --no-pager

ResolutionCreate/mount the approved host-side backup directory with least privilege and sufficient capacity. Do not grant broad filesystem permissions.

RESTORE VERIFYONLY fails with a checksum or media error.

Likely causeThe backup is truncated/corrupt, storage changed it, the wrong file was selected, or required checksum/encryption metadata is unavailable.

Safe checks
  • sha256sum {{sqlBackupFile}}
  • RESTORE HEADERONLY FROM DISK = N'{{sqlBackupFile}}';
  • Inspect msdb.dbo.backupset for has_backup_checksums and encryption metadata.

ResolutionQuarantine the artifact, select an earlier known-good protected backup, investigate storage transfer/integrity, and never use CONTINUE_AFTER_ERROR for normal recovery acceptance.

RESTORE reports that the encrypted backup cannot find the server certificate.

Likely causeThe target master database lacks the backup-encryption certificate and private key, the wrong certificate was imported, or its private-key protection password is unavailable.

Safe checks
  • SELECT name, thumbprint, expiry_date FROM master.sys.certificates WHERE name = '{{sqlBackupCertificate}}';
  • Compare certificate thumbprint with the backup manifest.
  • RESTORE HEADERONLY FROM DISK = N'{{sqlBackupFile}}';

ResolutionImport the matching certificate and private key from the separately protected off-host escrow into master, then retry. Without it, the encrypted backup is unrecoverable.

RESTORE DATABASE would overwrite or collide with existing files.

Likely causeLogical file names or MOVE destinations are wrong, the isolated database already exists, or an unsafe REPLACE option was introduced.

Safe checks
  • RESTORE FILELISTONLY FROM DISK = N'{{sqlBackupFile}}';
  • SELECT name, physical_name FROM sys.master_files;
  • test -e {{sqlRestoreDataFile}} || true; test -e {{sqlRestoreLogFile}} || true

ResolutionChoose a new isolated database name and unused data/log paths derived from FILELISTONLY. Never add REPLACE merely to bypass collision safeguards.

RESTORE VERIFYONLY succeeds but DBCC CHECKDB fails after restore.

Likely causeVERIFYONLY proved media readability/completeness, not the logical/physical structure of restored pages, or corruption existed in the source.

Safe checks
  • DBCC CHECKDB (N'{{sqlRestoreDatabase}}') WITH NO_INFOMSGS, ALL_ERRORMSGS;
  • Review suspect_pages and SQL Server error log.
  • Repeat restore from an earlier independently retained backup.

ResolutionKeep the restored database isolated, preserve evidence, test another known-good backup, and follow Microsoft's corruption recovery guidance. Do not run repair with data loss on production without explicit incident approval.

The restored database is consistent but application validation fails.

Likely causeThe wrong backup point, collation, login mapping, compatibility level, encryption key, SQL Agent dependency, or external service is missing.

Safe checks
  • SELECT name, state_desc, compatibility_level, collation_name FROM sys.databases WHERE name='{{sqlRestoreDatabase}}';
  • Run deterministic count and known-key queries against the isolated database.
  • List orphaned users and required external dependencies.

ResolutionKeep production untouched, correct the recovery manifest or dependency plan, and repeat from a clearly identified backup generation.

The backup job succeeds but no usable off-host recovery point exists.

Likely causeFiles remain on the database host, retention/replication is not monitored, the encryption certificate/private key was not escrowed, or no actual restore drill occurred.

Safe checks
  • Compare local and off-host checksums/manifests.
  • Verify immutable retention and access logs at the independent destination.
  • Review the latest completed restore-drill evidence and RTO.

ResolutionTransfer the backup and certificate escrow through the approved secure channel, verify checksums, and complete an isolated restore before claiming recoverability.

After the procedure

Alternatives and next steps

Consider these alternatives

  • Use integrated Active Directory or Microsoft Entra authentication on supported deployments to avoid long-lived SQL passwords.
  • Use SQL Server Agent and enterprise backup tooling for scheduled full/differential/log policy, while retaining the same checksum, encryption, escrow, off-host, and restore-test gates.
  • Restore to a disposable isolated network/recovery subscription when customer data policy requires stronger separation than a second on-premises host.

Operate it safely

  • Implement the normal RPO chain of full, differential, and transaction-log backups with monitored job history and retention.
  • Run scheduled off-host restore drills, certificate/key recovery tests, CHECKDB, and application validation; trend actual RTO.
  • Rotate backup certificates while retaining every historical private key needed by backups still inside retention.
  • Audit strict client adoption, disabled/privileged logins, permission drift, certificate expiry, backup age/failure, and CHECKDB results.

Reference

Frequently asked questions

Is RESTORE VERIFYONLY a restore test?

No. It checks that the backup set is complete/readable and can validate backup checksums, but it neither restores nor validates the database structure. An actual restore plus CHECKDB and application checks is required.

Can we discard the backup certificate after rotation?

Only after every backup encrypted by that certificate has expired from retention. Without its private key, those backups cannot be restored on another instance.

Does SQL Server 2025 TLS protect Always On mirroring endpoints on Linux?

No. Microsoft's Linux encrypted-connections documentation explicitly states TLS is not supported for Always On availability-group database-mirroring endpoints.

Recovery

Rollback

Restore the saved SQL Server network configuration if strict TLS blocks critical clients; database security and recovery changes are reversed only by exact named objects after dependency review.

  1. If the service fails, restore `/var/opt/mssql/mssql.conf.pre-wyd302-050`, verify original certificate paths, and restart before any further change.
  2. If clients fail, retain encryption and upgrade/fix trust where possible; if the approved rollback is invoked, restore the prior force settings and record the temporary exposure.
  3. Remove a new login/user/role only after confirming no application session depends on it; never restore broad legacy roles merely for convenience.
  4. Retain backup certificate/private-key escrow for every encrypted backup inside retention even if the new backup policy is rolled back.
  5. Drop only the isolated restore database after evidence capture. Production data is never overwritten or restored with REPLACE during this guide.

Evidence

Sources and review

Verified 2026-07-24Review due 2026-10-22
Microsoft SQL Server on Linux encrypted connectionsofficialMicrosoft mssql-conf TLS settingsofficialMicrosoft SQL Server 2025 TLS 1.3 supportofficialMicrosoft BACKUP Transact-SQLofficialMicrosoft backup encryptionofficialMicrosoft BACKUP CERTIFICATEofficialMicrosoft restore verification limitationsofficialMicrosoft RESTORE arguments and checksumsofficial