Skip to content
InFocus Data
All posts
Database Administration·November 10, 2026·30 min read

Zero-Trust SQL Server Access Starts With the Shared Login

Zero-trust SQL Server access: Vault-issued logins, one per person, that expire on a TTL and die on revoke, with a tailnet policy in place of a VPN.

By Chris Sparks

Two people read the same SQL Server table through one shared login, 0.4 seconds apart. The server’s login audit kept this, with client IP, application name and workstation name at the end of each row:

2026-11-06 00:20:33.791 LGIS 1 field_shared  172.19.0.3 SQLCMD ALICE-LAPTOP
2026-11-06 00:20:34.202 LGIS 1 field_shared  172.19.0.3 SQLCMD ALICE-LAPTOP

The second row is Bob, whose sqlcmd sent -H ALICE-LAPTOP; the server records whatever workstation and application name the client sends. Both clients ran inside the SQL Server container, so 172.19.0.3 is the server’s own address. That stands in for two people at one shared field-office PC, or an office behind NAT, without showing either. Nothing in these rows names a person.

Compare a login HashiCorp Vault created when engineer1 asked for one, used for two sessions:

2026-11-06 00:17:50.526 CR   1 v-userpass-engineer1-readonly-Pm6r9mVwisfnk8D0AuqX-1793924270 by vault_admin 172.19.0.2 go-mssqldb 333f176154f4
2026-11-06 00:17:51.978 LGIS 1 v-userpass-engineer1-readonly-Pm6r9mVwisfnk8D0AuqX-1793924270  172.19.0.3 SQLCMD 74fcdb8d4fa1
2026-11-06 00:17:52.367 LGIS 1 v-userpass-engineer1-readonly-Pm6r9mVwisfnk8D0AuqX-1793924270  172.19.0.3 SQLCMD 74fcdb8d4fa1

The name carries the Vault identity that asked and the role. Its lease said 3,600 seconds; we watched Vault drop logins at expiry on a 20-second role.

Pair logins like that with a tailnet (Tailscale, or a Headscale server you run) whose policy lets a laptop reach Vault on port 8200 and SQL Server on 1433, and you can retire both the VPN and the shared login. Start with the login. A DBA can change it alone, on a server they already run, and SQL Server’s own audit shows the result; the tailnet does nothing for you until its policy file exists, because Tailscale and Headscale both start out allowing every device to reach every other one. We ran the database half in Docker against SQL Server 2022 CU27 GDR (16.0.4305.5) and Vault 2.2.1, from an empty container to the last audit query in 192 seconds, and checked the tailnet policy with Headscale 0.29.7’s policy engine. All times in this post are UTC.

What NIST asks for, and how close an hour gets

Tenet 2 of the seven in NIST SP 800-207 reads “All communication is secured regardless of network location,” and its explanation begins “Network location alone does not imply trust.”[1] A site VPN that drops a laptop onto the office subnet uses location as trust. Tenet 3: “Access to individual enterprise resources is granted on a per-session basis,” and “Access should also be granted with the least privileges needed to complete the task.”[1:1]

A shared login gives everyone the grants the most demanding user needed. A one-hour Vault login belongs to one Vault identity and carries one role’s grants. It is not per-session: engineer1’s login opened two sessions in under half a second.

Each Vault password lasts as long as its lease. For that hour it can still be pasted into a query tool’s saved profile or a connection string. Login password age is on the checklist in SQL Server Security Fundamentals.

Give Vault a login that isn’t sysadmin

Vault’s login creates logins and database users, grants permissions and kills sessions. Our setup.sql takes its permission list from HashiCorp’s minimum-privilege script for the SQL Server plugin[2] and changes where one grant goes:

USE master;
CREATE LOGIN vault_admin WITH PASSWORD = 'Lab-Vault-Initial-1!';
GRANT ALTER ANY LOGIN TO vault_admin;
GRANT ALTER ANY CONNECTION TO vault_admin;
GRANT VIEW ANY DEFINITION TO vault_admin;
GRANT VIEW SERVER STATE TO vault_admin;
GO
USE fieldops;
CREATE USER vault_admin FOR LOGIN vault_admin;
GRANT ALTER ANY USER TO vault_admin;
GRANT VIEW DEFINITION TO vault_admin;
GRANT CONTROL ON SCHEMA::dbo TO vault_admin;
ALTER ROLE db_accessadmin ADD MEMBER vault_admin;

HashiCorp’s script grants ALTER ANY LOGIN to the database user. That’s a server permission, and from a user database SQL Server refuses it:

Msg 4621, Level 16, State 10, Server 74fcdb8d4fa1, Line 1
Permissions at the server scope can only be granted when the current database is master

So setup.sql grants it to the login, in master.

CONTROL ON SCHEMA::dbo lets Vault grant SELECT on dbo to the logins it creates, and it is control of every object in dbo as well. After rotate-root, only Vault knows that password, which makes Vault’s root a DBA credential to guard like one. If your roles only grant reads on a few views, move those views to their own schema and give Vault CONTROL on that schema alone.

vault secrets enable database
vault write database/config/fieldsql \
  plugin_name=mssql-database-plugin \
  connection_url='sqlserver://{{username}}:{{password}}@mssql:1433?database=fieldops' \
  allowed_roles='readonly,readwrite,short,inverted' \
  username=vault_admin password='Lab-Vault-Initial-1!'
vault write -force database/rotate-root/fieldsql

After rotate-root, sqlcmd with the original password printed Sqlcmd: Error: Microsoft ODBC Driver 18 for SQL Server : Login failed for user 'vault_admin'.., and the audit recorded the reason, Password did not match that for the login provided. Make sure your own sysadmin access doesn’t run through vault_admin before you rotate.

With no revocation statement of your own, Vault looks for users to drop only in the database named in connection_url,[2:1] which is why it ends in ?database=fieldops.

One login per person, per request

Each Vault role holds the SQL statements Vault runs when someone asks for a login:

vault write database/roles/readonly db_name=fieldsql default_ttl=1h max_ttl=8h \
  creation_statements="CREATE LOGIN [{{name}}] WITH PASSWORD = '{{password}}'; CREATE USER [{{name}}] FOR LOGIN [{{name}}]; GRANT SELECT ON SCHEMA::dbo TO [{{name}}];"

engineer1 read all 12,000 rows of the test table and was refused an insert with Msg 229 ... The INSERT permission was denied on the object 'daily_volume'. A second role, readwrite, grants SELECT, INSERT to the people who load data.

Vault’s default name template for SQL Server is v-, the requester’s display name cut to 20 characters, the role name cut to 20, 20 random characters and a Unix timestamp.[3] In the lab people sign in with Vault’s username and password method, so the display name is userpass- plus the username. The prefix takes 9 of the 20 characters and leaves 11 for the person, so fieldengineer-alpha1 and fieldengineer-alpha2 both become fieldengine:

fieldengineer-alpha1 -> v-userpass-fieldengine-readonly-xSRU8FIGRjj62ySfaaJB-1793924428
fieldengineer-alpha2 -> v-userpass-fieldengine-readonly-XGw7jq1JuWlBAnHnHKQ8-1793924429

Under OIDC or LDAP the display name comes from that auth method and the claim or attribute it is configured to use. Read display_name from one real user’s token with vault token lookup before you trust what the login name says, then set a template that keeps it. This one allows 60 characters of display name and keeps the timestamp:

vault write database/config/fieldsql \
  plugin_name=mssql-database-plugin \
  connection_url='sqlserver://{{username}}:{{password}}@mssql:1433?database=fieldops' \
  allowed_roles='readonly,readwrite,short,inverted' username=vault_admin \
  username_template='{{ printf "v-%s-%s-%s-%s" (.DisplayName | truncate 60) (.RoleName | truncate 20) (random 12) (unix_time) | truncate 128 }}'

fieldengineer-alpha1 -> v-userpass-fieldengineer-alpha1-readonly-drUxn10JH6SP-1793924430
fieldengineer-alpha2 -> v-userpass-fieldengineer-alpha2-readonly-vDUDcF1BmKpr-1793924431

That write leaves out the password. Vault kept the rotated one, and both requests after it succeeded.

sequenceDiagram
    participant L as Laptop
    participant V as Vault (tag:vault)
    participant S as SQL Server (tag:fieldsql)
    L->>V: sign in, read database/creds/readonly (tcp 8200)
    V->>S: CREATE LOGIN, CREATE USER, GRANT SELECT (tcp 1433)
    V->>L: username, password, 1h lease
    L->>S: connect as the new login (tcp 1433)
    Note over V,S: lease revoked or expired
    V->>S: disable login, kill its sessions, drop user and login

Revoking: a read, a write, one person, everyone

Vault’s default revocation for SQL Server disables the login, kills its sessions, then drops the user and the login.[3:1]

A running read. We started a 45-second waitfor as engineer2, waited 3 seconds, read the server clock (00:17:56.320) and revoked the lease:

vault lease revoke -sync database/creds/readonly/oZJkTs6ySDqmS3IgWh5Iubxu

It returned in 697 ms. engineer2’s sqlcmd exited with Cannot continue the execution because the session is in the kill state. The audit, on the same clock, has the disable 0.467 s and the drop 0.520 s after the clock read:

2026-11-06 00:17:53.292 LGIS 1 v-userpass-engineer2-readonly-D4iWl5GKxcGj9GG4g4LX-1793924271  172.19.0.3 SQLCMD 74fcdb8d4fa1
2026-11-06 00:17:56.787 LGDA 1 v-userpass-engineer2-readonly-D4iWl5GKxcGj9GG4g4LX-1793924271 by vault_admin 172.19.0.2 go-mssqldb 333f176154f4
2026-11-06 00:17:56.840 DR   1 v-userpass-engineer2-readonly-D4iWl5GKxcGj9GG4g4LX-1793924271 by vault_admin 172.19.0.2 go-mssqldb 333f176154f4
2026-11-06 00:17:58.324 LGIF 0 v-userpass-engineer2-readonly-D4iWl5GKxcGj9GG4g4LX-1793924271  172.19.0.3 SQLCMD 74fcdb8d4fa1

A long write in one transaction. As engineer2 under readwrite, we opened a transaction and inserted 50,000 rows at a time without committing. After 20 seconds the transaction held 3,579,740 rows and 1,858 MB of log. The revoke failed:

* failed to revoke entry: resp: (*logical.Response)(nil) err: mssql: Could not drop login 'v-userpass-engineer2-readwrite-qmveT7RArhAO4k73Bual-1793924287' as the user is currently logged in.
lease revoke -sync exited 2 after 809 ms
login disabled: 1   sessions: 1

Vault had disabled the login and killed the session, and SQL Server was rolling the transaction back, so the session still existed when the drop ran. You can watch a rollback’s progress with:

select r.session_id, r.command, r.percent_complete
from sys.dm_exec_requests r join sys.dm_exec_sessions s on s.session_id = r.session_id
where s.login_name = 'v-...';

Our one sample read percent_complete 7.0. The session was gone 20 seconds after the revoke started, the table held 0 rows, and a second vault lease revoke -sync dropped the login. vault lease revoke -h in the 2.2.1 container explains the choice: by default Vault “will queue the revocation and keep trying if it fails,” while with -sync “it is then up to the caller to retry on failure.”

A load that commits every 50,000 rows. The same revoke against the same insert loop, without the open transaction, exited 0 after 568 ms. The session was gone within a second, and the table kept 5,950,000 rows: 119 committed batches, with the batch in flight rolled back. Batches shrink the rollback; they don’t remove the race between rollback and drop. A big enough batch can still fail the drop, and the retry above still applies.

One person. When a Vault token is revoked, Vault revokes every lease created with it.[4] Revoking by login path takes out every token engineer1 got from the userpass method, and with them engineer1’s database logins:

vault token revoke -mode=path auth/userpass/login/engineer1
vault delete auth/userpass/users/engineer1

It returned in 573 ms, and engineer1’s login count on the server was already 0 on the next query. With OIDC or LDAP the path is that method’s login path, and you also disable the person at the identity provider so they can’t sign in to Vault again.

Everyone. For an incident, revoke by prefix. One command dropped the four logins the readonly role had out, in 665 ms:

vault lease revoke -sync -prefix database/creds/readonly/

default_ttl ends a lease unless something renews it

A lease runs for default_ttl; max_ttl caps renewals and the first lease alike.[4:1] The lab’s short role used 20 and 60 seconds and issued three logins:

Login What happened Expected life Lifetime, CR to DR (audit)
A No renewal, running a 45-second read 20 s 20.055 s
B Renewed once, about 9 s after issue, -increment=40s 48.2 to 50.4 s 52.747 s
C Renewed once, -increment=600s 60 s 65.825 s

A’s read died at expiry with the same kill state error as a manual revoke. B’s expected life is the renewal time (between 00:19:27.207 and 00:19:28.443 on the server clock) plus 40 seconds, so expiry ran 2.3 to 4.5 s late. C’s renewal came back capped, TTL of "10m" exceeded the effective max_ttl of "50s"; TTL value is capped accordingly, and expiry ran 5.8 s late. A role with default_ttl=60s and max_ttl=20s issued a 20-second lease with the same warning.

A nine-hour extract on a role with default_ttl=1h and max_ttl=8h dies at one hour unless the job renews its lease, and at eight hours if it does. Give a long job its own role with a default_ttl that covers the run. For people, skip renewal and have them ask again each hour, which gives the audit a new login per hour.

The tailnet half, as configuration

{
  "groups": {
    "group:dba": ["dba1@"],
    "group:engineers": ["engineer1@"]
  },
  "tagOwners": {
    "tag:fieldsql": ["group:dba"],
    "tag:vault": ["group:dba"]
  },
  "grants": [
    // People reach Vault for a login, and SQL Server on 1433. Nothing else.
    { "src": ["group:engineers", "group:dba"], "dst": ["tag:vault"], "ip": ["tcp:8200"] },
    { "src": ["group:engineers", "group:dba"], "dst": ["tag:fieldsql"], "ip": ["tcp:1433"] },
    // Vault reaches SQL Server to create and drop logins.
    { "src": ["tag:vault"], "dst": ["tag:fieldsql"], "ip": ["tcp:1433"] }
  ],
  "tests": [
    { "src": "engineer1@", "accept": ["tag:fieldsql:1433", "tag:vault:8200"],
      "deny": ["tag:fieldsql:3389", "tag:fieldsql:445", "tag:vault:22"] },
    { "src": "tag:vault", "accept": ["tag:fieldsql:1433"], "deny": ["tag:fieldsql:3389"] }
  ]
}

The file is HuJSON, so the comments are legal. dba1@ and engineer1@ are Headscale user names; Tailscale wants the full login, such as alice@example.com.[5] In Tailscale you edit the policy in the admin console or manage it through GitOps.[5:1] Headscale reads the file named in policy.path and picks up changes on systemctl reload headscale or a SIGHUP; it can also keep the policy in its database.[6]

The policy assumes a default instance on TCP 1433. A named instance uses a dynamic port unless you set one, and clients find it through the SQL Server Browser service on UDP 1434.[7] Give the named instance a static TCP port, grant that port instead of 1433, and connect with host,port so nobody needs udp:1434 through the tailnet.

Grants deny by default: a connection gets through only if some grant allows it.[8] The default policy is the opposite. Tailscale starts a new tailnet “with a default allow all access policy,”[9] and Headscale with no policy file “allows all traffic between nodes.”[6:1]

Keep the tests block. When Headscale started with this file before any user or node existed, it logged:

2026-11-06T00:20:43Z WRN policy tests failed at boot; server starting anyway, fix the policy and reload error="test(s) failed:\nengineer1@: failed to resolve source \"engineer1@\": resolving: user not found: token \"engineer1@\"\ntag:vault: source \"tag:vault\" resolved to no IP addresses"
container running: true

headscale policy get then printed this file, so the server had loaded it despite the failed tests. Whether it enforced every grant we didn’t test; no client connected. Run headscale policy check -f <file> before each reload: it exits 1 on a failing test where the server only warns. With three placeholder nodes registered and none tagged, it failed on destination resolved to no IP addresses: "tag:fieldsql:1433". After tagging one node tag:fieldsql and one tag:vault, it printed Policy is valid. A copy of the file that lists tag:fieldsql:1433 in engineer1’s deny as well as accept failed with engineer1@ -> tag:fieldsql:1433: expected DENIED, got ALLOWED. There the grant was right and the test was wrong; when this error appears, read both before you change either.

The engine evaluated each assertion in tests: engineer1 allowed to 1433 and 8200 and denied 3389, 445 and 22, Vault allowed to 1433 and denied 3389. The tailnet doesn’t touch the office LAN, so scope the server’s inbound firewall rule for TCP 1433 to the tailnet interface.

Troubleshooting

Login failed for user 'v-...'.. with no reason. The client prints the same line whether the login is gone (lease revoked or expired) or the password is wrong; our log has both. The reason is in the audit’s failed-login record (action_id = 'LGIF', column statement): Could not find a login matching the name provided. for an expired or revoked lease, Password did not match that for the login provided. for a bad password.

A session survives a revoke. Check that Vault’s login holds ALTER ANY CONNECTION, which SQL Server requires for KILL.[7:1]

What the lab leaves out

The Vault here is a dev server, in memory and unsealed at start, and HashiCorp’s docs say never to run one in production. Their production hardening guide covers end-to-end TLS, audit device logs and seal configuration.[10]

TLS: this lab had none worth the name. Vault listened on plain HTTP inside the container, sqlcmd ran with -C, which trusts the server’s self-signed certificate, and the connection URL set no encryption options, so we didn’t check what the driver negotiated. Tenet 2 needs all three fixed; -C belongs in a lab only.

Vault’s logins are SQL logins, so the server needs SQL Server authentication on. If the field already connects with a shared SQL login, it is on already, and Vault adds one powerful SQL login of its own, vault_admin, while taking the shared password away from everyone else. If people already sign in with their own domain accounts, you have attribution and offboarding through the directory; keep it.

Applications with a fixed connection string need a static role, which the SQL Server plugin supports:[2:2] Vault rotates one named login’s password on a schedule, and the application reads the current password from Vault when it connects. A static role rotates a shared password. It gives no per-person attribution. We didn’t test static roles.

Availability groups: HashiCorp notes that server logins don’t replicate across replicas and that rotate-root changes the password only on the instance Vault connects to; its options are domain accounts with Vault’s LDAP secrets engine, or rotating the password on every instance outside Vault and updating Vault’s configuration.[2:3] SQL Server 2022’s contained availability groups keep logins in the group’s own master, which replicates with the group, but only connections through the contained group’s listener see them,[7:2] so Vault’s connection_url would name the listener. We didn’t test Vault against one. On SQL Server 2022, we’d test the contained group first, through a failover, because it keeps per-person dynamic logins; on older versions we’d take domain accounts through the LDAP secrets engine, since rotating outside Vault brings back the manual step this setup removes.

Reproduce it

compose.yaml:

services:
  mssql:
    image: mcr.microsoft.com/mssql/server:2022-CU27-GDR1-ubuntu-22.04
    environment:
      ACCEPT_EULA: "Y"
      MSSQL_SA_PASSWORD: "Lab-Only-Sa-2026!"
    volumes: [ "./setup.sql:/lab/setup.sql:ro" ]
  vault:
    image: hashicorp/vault:2.2.1
    command: [ "server", "-dev", "-dev-root-token-id=lab-root" ]
    cap_add: [ IPC_LOCK ]
    environment:
      VAULT_ADDR: http://127.0.0.1:8200
      VAULT_TOKEN: lab-root

setup.sql:

-- A database to protect, a login for Vault, and a server audit of logins.
CREATE DATABASE fieldops;
GO
USE fieldops;
CREATE TABLE dbo.daily_volume (
  well_id   int           NOT NULL,
  prod_date date          NOT NULL,
  oil_bbl   decimal(10,2) NOT NULL,
  gas_mcf   decimal(10,2) NOT NULL,
  PRIMARY KEY (well_id, prod_date)
);
INSERT INTO dbo.daily_volume
SELECT w.n, DATEADD(day, d.n, '2026-01-01'), (w.n * 7 + d.n) % 300 / 1.5, (w.n * 13 + d.n) % 900 / 1.5
FROM (SELECT TOP 40 ROW_NUMBER() OVER (ORDER BY (SELECT 1)) n FROM sys.objects) w
CROSS JOIN (SELECT TOP 300 ROW_NUMBER() OVER (ORDER BY (SELECT 1)) - 1 n FROM sys.all_objects) d;
CREATE TABLE dbo.load_test (id bigint NOT NULL, payload char(400) NOT NULL);
GO
-- Vault's own login: creates and drops logins, kills their sessions. Not sysadmin.
USE master;
CREATE LOGIN vault_admin WITH PASSWORD = 'Lab-Vault-Initial-1!';
GRANT ALTER ANY LOGIN TO vault_admin;
GRANT ALTER ANY CONNECTION TO vault_admin;
GRANT VIEW ANY DEFINITION TO vault_admin;
GRANT VIEW SERVER STATE TO vault_admin;
GO
USE fieldops;
CREATE USER vault_admin FOR LOGIN vault_admin;
GRANT ALTER ANY USER TO vault_admin;
GRANT VIEW DEFINITION TO vault_admin;
GRANT CONTROL ON SCHEMA::dbo TO vault_admin;
ALTER ROLE db_accessadmin ADD MEMBER vault_admin;
GO
-- Every login, failed login and login create/drop, to a file.
USE master;
CREATE SERVER AUDIT login_audit TO FILE (FILEPATH = '/var/opt/mssql/data/');
CREATE SERVER AUDIT SPECIFICATION login_audit_spec FOR SERVER AUDIT login_audit
  ADD (SUCCESSFUL_LOGIN_GROUP), ADD (FAILED_LOGIN_GROUP), ADD (SERVER_PRINCIPAL_CHANGE_GROUP)
  WITH (STATE = ON);
ALTER SERVER AUDIT login_audit WITH (STATE = ON);
GO

drill.sh runs every step above from a clean start. It needs GNU date (for %3N milliseconds), and MSYS_NO_PATHCONV only matters in Git Bash on Windows, where we ran it:

#!/usr/bin/env bash
# Vault dynamic SQL Server logins: issue, use, revoke, expire, renew, offboard one person, audit.
# Usage: bash drill.sh 2>&1 | tee drill.log
set -u
export MSYS_NO_PATHCONV=1
C="docker compose -p ztlab"
SA_PW='Lab-Only-Sa-2026!'
V="$C exec -T vault vault"
sq() { $C exec -T mssql /opt/mssql-tools18/bin/sqlcmd -S localhost -C -h -1 -W "$@"; }
sa() { sq -U sa -P "$SA_PW" -Q "set nocount on; $1"; }
ms() { date +%s%3N; }
now() { sa "select convert(varchar(23), sysutcdatetime(), 121)"; }
field() { sed -n "s/.*\"$1\": *\"\{0,1\}\([[:alnum:]_./:-]*\)\"\{0,1\},\{0,1\}$/\1/p" | head -1; }
logins() { sa "select count(*) from sys.server_principals where name like '$1'"; }
sessions() { sa "select count(*) from sys.dm_exec_sessions where login_name = '$1'"; }
# creds <vault user> <role>: log in as a person and ask Vault for a SQL Server login
creds() {
  $C exec -T -e VAULT_TOKEN= vault sh -c \
    "VAULT_TOKEN=\$(vault login -token-only -method=userpass username=$1 password=lab-$1) vault read -format=json database/creds/$2"
}
ROLE_SQL="CREATE LOGIN [{{name}}] WITH PASSWORD = '{{password}}'; CREATE USER [{{name}}] FOR LOGIN [{{name}}];"
START=$(ms)

echo "== clean start"
$C down -v 2>&1 | tail -1
$C up -d 2>&1 | tail -1
until sa "select 1" >/dev/null 2>&1; do sleep 2; done
until $V status >/dev/null 2>&1; do sleep 1; done

echo "== versions"
docker version --format 'docker client {{.Client.Version}} on {{.Client.Os}}/{{.Client.Arch}}; engine {{.Server.Version}} on {{.Server.Os}}/{{.Server.Arch}}'
docker info --format 'docker info: {{.OperatingSystem}}, kernel {{.KernelVersion}}'
date --version | head -1
docker manifest inspect mcr.microsoft.com/mssql/server:2022-CU27-GDR1-ubuntu-22.04 >/dev/null && echo "MCR has mcr.microsoft.com/mssql/server:2022-CU27-GDR1-ubuntu-22.04"
docker image inspect --format '{{index .RepoDigests 0}}' mcr.microsoft.com/mssql/server:2022-CU27-GDR1-ubuntu-22.04 hashicorp/vault:2.2.1
sa "select @@version" | head -1
$V version
echo "-- vault lease revoke -h, on retries:"
$V lease revoke -h 2>&1 | grep -A3 'revoke asynchronously'

echo "== 1. SQL Server: database, Vault's login, login audit"
sq -U sa -P "$SA_PW" -b -i /lab/setup.sql | grep -v '^Changed database context'
echo "-- ALTER ANY LOGIN granted from a user database, as in HashiCorp's sample script:"
sa "use fieldops; grant alter any login to vault_admin;"

echo "== 2. Vault: connection, root rotation, roles, people"
$V secrets enable database
$V write database/config/fieldsql \
  plugin_name=mssql-database-plugin \
  connection_url='sqlserver://{{username}}:{{password}}@mssql:1433?database=fieldops' \
  allowed_roles='readonly,readwrite,short,inverted' \
  username=vault_admin password='Lab-Vault-Initial-1!'
$V write -force database/rotate-root/fieldsql
echo "-- vault_admin with its original password after rotate-root:"
sq -U vault_admin -P 'Lab-Vault-Initial-1!' -Q "select 1"
$V write database/roles/readonly db_name=fieldsql default_ttl=1h max_ttl=8h \
  creation_statements="$ROLE_SQL GRANT SELECT ON SCHEMA::dbo TO [{{name}}];"
$V write database/roles/readwrite db_name=fieldsql default_ttl=1h max_ttl=8h \
  creation_statements="$ROLE_SQL GRANT SELECT, INSERT ON SCHEMA::dbo TO [{{name}}];"
$V write database/roles/short db_name=fieldsql default_ttl=20s max_ttl=60s \
  creation_statements="$ROLE_SQL GRANT SELECT ON SCHEMA::dbo TO [{{name}}];"
$V auth enable userpass
printf 'path "database/creds/*" { capabilities = ["read"] }\n' | $C exec -T vault vault policy write fieldsql-people -
for u in engineer1 engineer2 fieldengineer-alpha1 fieldengineer-alpha2; do
  $V write auth/userpass/users/$u password=lab-$u token_policies=fieldsql-people
done

echo "== 3. engineer1 and engineer2 each ask for a login"
J1=$(creds engineer1 readonly); J2=$(creds engineer2 readonly)
U1=$(echo "$J1" | field username); P1=$(echo "$J1" | field password)
U2=$(echo "$J2" | field username); P2=$(echo "$J2" | field password); L2=$(echo "$J2" | field lease_id)
echo "engineer1: $U1  ttl_s=$(echo "$J1" | field lease_duration)"
echo "engineer2: $U2  ttl_s=$(echo "$J2" | field lease_duration)"

echo "== 4. engineer1 connects twice: two reads, one refused insert"
sq -U "$U1" -P "$P1" -Q "set nocount on; select suser_sname() as login_name, count(*) as row_count from fieldops.dbo.daily_volume"
sq -U "$U1" -P "$P1" -Q "insert into fieldops.dbo.daily_volume values (999, '2026-11-05', 1, 1)"
sa "select name, type_desc from sys.server_principals where name like 'v-%' order by name"

echo "== 5. revoke engineer2's lease while a 45-second read is running (the script waits 3 s first)"
sq -U "$U2" -P "$P2" -Q "set nocount on; waitfor delay '00:00:45'; select 'session survived revocation'" > bg-read.out 2>&1 &
BG=$!
sleep 3
echo "sessions for engineer2: $(sessions "$U2")   server time: $(now)"
t0=$(ms); $V lease revoke -sync "$L2"; t1=$(ms)
echo "lease revoke -sync returned in $((t1 - t0)) ms"
wait $BG; echo "-- the open session's sqlcmd exited $? and printed:"; cat bg-read.out; rm -f bg-read.out
echo "sessions for engineer2: $(sessions "$U2")   logins: $(logins "$U2")"
echo "-- engineer2 reconnecting with the revoked password:"
sq -U "$U2" -P "$P2" -Q "select 1"

echo "== 6. revoke without -sync, then look at the server at once"
J=$(creds engineer2 readonly); UA=$(echo "$J" | field username); LA=$(echo "$J" | field lease_id)
$V lease revoke "$LA"
echo "logins right after: $(logins "$UA")"
sleep 3
echo "logins 3 s later:   $(logins "$UA")"

echo "== 7. revoke engineer2's lease during a long write"
J=$(creds engineer2 readwrite)
UW=$(echo "$J" | field username); PW=$(echo "$J" | field password); LW=$(echo "$J" | field lease_id)
sq -U "$UW" -P "$PW" -Q "set nocount on; begin tran;
  while 1 = 1 insert into fieldops.dbo.load_test (id, payload)
    select top (50000) row_number() over (order by (select 1)), 'x' from sys.all_objects a cross join sys.all_objects b;
  commit;" > bg-write.out 2>&1 &
BG=$!
sleep 20
echo "uncommitted rows after 20 s: $(sa "select count(*) from fieldops.dbo.load_test with (nolock)")"
echo "log used by the open transaction, MB: $(sa "select max(database_transaction_log_bytes_used) / 1048576 from sys.dm_tran_database_transactions where database_id = db_id('fieldops')")"
echo "server time at revoke: $(now)"
t0=$(ms); $V lease revoke -sync "$LW"; rc=$?; t1=$(ms)
echo "lease revoke -sync exited $rc after $((t1 - t0)) ms"
echo "login disabled: $(sa "select is_disabled from sys.server_principals where name = '$UW'")   sessions: $(sessions "$UW")"
echo "rollback in progress (command, percent_complete):"
sa "select r.command, r.percent_complete from sys.dm_exec_requests r join sys.dm_exec_sessions s on s.session_id = r.session_id where s.login_name = '$UW'"
while [ "$(sessions "$UW")" != "0" ]; do sleep 1; done
t2=$(ms)
echo "session gone $(( (t2 - t0) / 1000 )) s after the revoke started; server time: $(now)"
wait $BG; echo "-- the writer's sqlcmd exited $? and printed:"; cat bg-write.out; rm -f bg-write.out
echo "rows in load_test after rollback: $(sa "select count(*) from fieldops.dbo.load_test")"
echo "logins for the writer before retry: $(logins "$UW")"
$V lease revoke -sync "$LW"
echo "logins for the writer after retry:  $(logins "$UW")"

echo "== 7b. revoke during a load that commits every 50,000 rows"
sa "truncate table fieldops.dbo.load_test"
J=$(creds engineer2 readwrite)
UW=$(echo "$J" | field username); PW=$(echo "$J" | field password); LW=$(echo "$J" | field lease_id)
sq -U "$UW" -P "$PW" -Q "set nocount on;
  while 1 = 1 insert into fieldops.dbo.load_test (id, payload)
    select top (50000) row_number() over (order by (select 1)), 'x' from sys.all_objects a cross join sys.all_objects b;" > bg-write.out 2>&1 &
BG=$!
sleep 20
echo "committed rows after 20 s: $(sa "select count(*) from fieldops.dbo.load_test")"
t0=$(ms); $V lease revoke -sync "$LW"; rc=$?; t1=$(ms)
echo "lease revoke -sync exited $rc after $((t1 - t0)) ms"
while [ "$(sessions "$UW")" != "0" ]; do sleep 1; done
t2=$(ms)
echo "session gone $(( (t2 - t0) / 1000 )) s after the revoke started"
wait $BG; rm -f bg-write.out
echo "rows in load_test after the kill: $(sa "select count(*) from fieldops.dbo.load_test")"
echo "logins for the writer: $(logins "$UW")"
[ "$(logins "$UW")" != "0" ] && $V lease revoke -sync "$LW" && echo "logins for the writer after retry: $(logins "$UW")"

echo "== 8. lease expiry and renewal (role short: default_ttl=20s, max_ttl=60s)"
JA=$(creds engineer1 short); JB=$(creds engineer1 short); JC=$(creds engineer1 short)
UA=$(echo "$JA" | field username); PA=$(echo "$JA" | field password)
UB=$(echo "$JB" | field username); LB=$(echo "$JB" | field lease_id)
UC=$(echo "$JC" | field username); LC=$(echo "$JC" | field lease_id)
echo "A (no renewal, runs a 45 s read): $UA"
echo "B (renewed once, +40s):           $UB"
echo "C (renewed once, asks +600s):     $UC"
sq -U "$UA" -P "$PA" -Q "set nocount on; waitfor delay '00:00:45'; select 'A survived expiry'" > bg-ttl.out 2>&1 &
BG=$!
sleep 8
echo "server time before renewals: $(now)"
$V lease renew -increment=40s "$LB"
$V lease renew -increment=600s "$LC"
echo "server time after renewals:  $(now)"
wait $BG; echo "-- A's sqlcmd exited $? and printed:"; cat bg-ttl.out; rm -f bg-ttl.out
while [ "$(sa "select count(*) from sys.server_principals where name in ('$UB', '$UC')")" != "0" ]; do sleep 2; done
echo "-- a role whose default_ttl (60s) is longer than its max_ttl (20s):"
$V write database/roles/inverted db_name=fieldsql default_ttl=60s max_ttl=20s \
  creation_statements="$ROLE_SQL GRANT SELECT ON SCHEMA::dbo TO [{{name}}];"
$V read database/creds/inverted 2>&1 | grep -E 'lease_duration|max_ttl|accordingly'

echo "== 9. offboard engineer1: revoke every token issued through engineer1's login path, then delete the user"
echo "v- logins for engineer1 before: $(logins 'v-userpass-engineer1-%')   all v- logins: $(logins 'v-%')"
t0=$(ms); $V token revoke -mode=path auth/userpass/login/engineer1; t1=$(ms)
echo "token revoke -mode=path returned in $((t1 - t0)) ms; engineer1's logins right after: $(logins 'v-userpass-engineer1-%')"
$V delete auth/userpass/users/engineer1
sa "select name from sys.server_principals where name like 'v-%' order by name"

echo "== 10. two people whose names share 20 characters"
for u in fieldengineer-alpha1 fieldengineer-alpha2; do echo "$u -> $(creds $u readonly | field username)"; done
$V write database/config/fieldsql \
  plugin_name=mssql-database-plugin \
  connection_url='sqlserver://{{username}}:{{password}}@mssql:1433?database=fieldops' \
  allowed_roles='readonly,readwrite,short,inverted' username=vault_admin \
  username_template='{{ printf "v-%s-%s-%s-%s" (.DisplayName | truncate 60) (.RoleName | truncate 20) (random 12) (unix_time) | truncate 128 }}'
for u in fieldengineer-alpha1 fieldengineer-alpha2; do echo "$u -> $(creds $u readonly | field username)"; done

echo "== 11. incident: revoke every login the readonly role has issued"
echo "readonly logins before: $(logins 'v-%readonly%')"
t0=$(ms); $V lease revoke -sync -prefix database/creds/readonly/; t1=$(ms)
echo "lease revoke -sync -prefix returned in $((t1 - t0)) ms; readonly logins after: $(logins 'v-%readonly%')"

echo "== 12. a shared login, both clients run inside the SQL Server container; bob sends alice's workstation name"
sa "create login field_shared with password = 'Shared-Login-2026!'; use fieldops; create user field_shared for login field_shared; grant select on schema::dbo to field_shared;" | grep -v "^Changed database context"
echo "alice: $(sq -U field_shared -P 'Shared-Login-2026!' -H ALICE-LAPTOP -Q "set nocount on; select count(*) from fieldops.dbo.daily_volume")"
echo "bob:   $(sq -U field_shared -P 'Shared-Login-2026!' -H ALICE-LAPTOP -Q "set nocount on; select count(*) from fieldops.dbo.daily_volume")"
sleep 3  # the audit writes to its file on a queue delay

AUDIT="sys.fn_get_audit_file('/var/opt/mssql/data/login_audit*.sqlaudit', default, default)"
COLS="convert(varchar(23), event_time, 121) as event_time_utc, action_id, succeeded,
      coalesce(nullif(object_name, ''), server_principal_name) as login,
      case when action_id in ('CR', 'LGDA', 'DR') then 'by ' + server_principal_name else '' end as actor,
      client_ip, application_name, host_name"
echo "== 13. the login audit"
echo "-- shared login"
sa "select $COLS from $AUDIT where action_id = 'LGIS' and server_principal_name = 'field_shared' order by event_time"
echo "-- engineer1's first Vault login"
sa "select $COLS from $AUDIT where coalesce(nullif(object_name, ''), server_principal_name) = '$U1' and action_id in ('CR', 'LGIS') order by event_time, sequence_number"
echo "-- engineer2, revoked during a read"
sa "select $COLS from $AUDIT where coalesce(nullif(object_name, ''), server_principal_name) = '$U2' order by event_time, sequence_number"
echo "-- lifetimes of the short-role logins, CR to DR"
sa "select left(object_name, 60), datediff(ms, min(case when action_id = 'CR' then event_time end), max(case when action_id = 'DR' then event_time end)) / 1000.0 as seconds
    from $AUDIT where object_name in ('$UA', '$UB', '$UC') group by object_name order by 2"
echo "-- failed-login reasons recorded"
sa "select distinct left(statement, 200) from $AUDIT where action_id = 'LGIF'"

echo "== drill done in $(( ($(ms) - START) / 1000 )) s"

The policy check runs Headscale with a placeholder relay map, since the nodes never connect. headscale-config.yaml:

server_url: http://headscale:8080
listen_addr: 0.0.0.0:8080
noise:
  private_key_path: /var/lib/headscale/noise_private.key
prefixes:
  v4: 100.64.0.0/10
database:
  type: sqlite
  sqlite:
    path: /var/lib/headscale/db.sqlite
dns:
  magic_dns: false
  override_local_dns: false
policy:
  mode: file
  path: /etc/headscale/policy.hujson
derp:
  server:
    enabled: false
  urls: []
  paths:
    - /etc/headscale/derp.yaml

derp.yaml:

regions:
  900:
    regionid: 900
    regioncode: lab
    regionname: lab placeholder
    nodes:
      - name: 900a
        regionid: 900
        hostname: derp.invalid

tailnet-policy-check.sh (policy-negative.hujson is policy.hujson with "tag:fieldsql:1433" added to engineer1’s deny list):

#!/usr/bin/env bash
# Evaluate policy.hujson with Headscale's own policy engine against three placeholder nodes.
# No tailnet runs here and the nodes never connect: this evaluates the policy rules only.
# Usage: bash tailnet-policy-check.sh 2>&1 | tee tailnet-policy-check.log
set -u
export MSYS_NO_PATHCONV=1
HS="docker exec hs-check headscale"
docker rm -f hs-check >/dev/null 2>&1
docker run -d --name hs-check \
  -v "$PWD/headscale-config.yaml:/etc/headscale/config.yaml:ro" \
  -v "$PWD/derp.yaml:/etc/headscale/derp.yaml:ro" \
  -v "$PWD/policy.hujson:/etc/headscale/policy.hujson:ro" \
  -v "$PWD/policy-negative.hujson:/etc/headscale/policy-negative.hujson:ro" \
  headscale/headscale:v0.29.7 serve >/dev/null
sleep 5
echo "== version"
$HS version
echo "== startup log: policy loaded before any user or node exists"
docker logs hs-check 2>&1 | grep -i "policy tests"
docker inspect -f 'container running: {{.State.Running}}' hs-check
echo "-- headscale policy get, first lines, after the failed boot tests:"
$HS policy get | head -4
$HS users create dba1
$HS users create engineer1
node() {  # id name user; ids are assigned 1, 2, 3 in a fresh database
  local key="hskey-authreq-$(head -c 32 /dev/urandom | base64 | tr -dc "A-Za-z0-9" | head -c 24)"
  $HS debug create-node --name "$2" --user "$3" -k "$key" >/dev/null
  $HS auth register --auth-id "$key" --user "$3"
}
node 1 laptop1 engineer1
node 2 fieldsql1 dba1
node 3 vault1 dba1
echo "== policy.hujson before any node is tagged"
$HS policy check -f /etc/headscale/policy.hujson; echo "exit=$?"
$HS nodes tag -i 2 -t tag:fieldsql
$HS nodes tag -i 3 -t tag:vault
echo "== nodes"
$HS nodes list -o json | tr -d ' \n\t' | grep -oE '"(given_name|tags|ip_addresses)":(\[[[:alnum:]",:._-]*\]|"[[:alnum:]_:.-]*")'
echo "== policy.hujson"
$HS policy check -f /etc/headscale/policy.hujson; echo "exit=$?"
echo "== policy-negative.hujson (expects engineer1 denied on 1433; must fail)"
$HS policy check -f /etc/headscale/policy-negative.hujson; echo "exit=$?"
docker rm -f hs-check >/dev/null

The SQL Server and Vault figures come from drill.log, one run of drill.sh from 00:17 to 00:21 UTC on November 6, 2026; its version section records Docker 28.5.1 (client on windows/amd64, engine on linux/amd64, Docker Desktop, WSL2 kernel). The Headscale figures come from tailnet-policy-check.log, one run of tailnet-policy-check.sh at 00:20 UTC the same day. Audit timestamps are the SQL Server container’s clock.

Get in touch

  1. Scott Rose, Oliver Borchert, Stu Mitchell and Sean Connelly, “Zero Trust Architecture,” NIST Special Publication 800-207 (August 2020), section 2.1, “Tenets of Zero Trust.” https://csrc.nist.gov/pubs/sp/800/207/final ↩︎1 ↩︎2

  2. HashiCorp, “MSSQL database secrets engine,” Vault documentation (retrieved November 5, 2026). https://developer.hashicorp.com/vault/docs/secrets/databases/mssql ↩︎1 ↩︎2 ↩︎3 ↩︎4

  3. HashiCorp, Vault v2.2.1 source, plugins/database/mssql/mssql.go (default username template; default revocation) (retrieved November 5, 2026). https://github.com/hashicorp/vault/blob/v2.2.1/plugins/database/mssql/mssql.go ↩︎1 ↩︎2

  4. HashiCorp, “Lease, Renew, and Revoke,” https://developer.hashicorp.com/vault/docs/concepts/lease, and “Database secrets engine (API),” role parameters default_ttl and max_ttl, https://developer.hashicorp.com/vault/api-docs/secret/databases (both retrieved November 5, 2026). ↩︎1 ↩︎2

  5. Tailscale, “Syntax reference for the tailnet policy file” (last validated April 8, 2026) and “Tailnet policy file” (last validated May 21, 2025), both retrieved November 5, 2026. https://tailscale.com/docs/reference/syntax/policy-file and https://tailscale.com/docs/features/tailnet-policy-file ↩︎1 ↩︎2

  6. Headscale documentation, “Policy” (retrieved November 5, 2026). https://headscale.net/stable/ref/policy/ ↩︎1 ↩︎2

  7. Microsoft, “KILL (Transact-SQL),” https://learn.microsoft.com/en-us/sql/t-sql/language-elements/kill-transact-sql; “SQL Server Browser service,” https://learn.microsoft.com/en-us/sql/tools/configuration-manager/sql-server-browser-service; “What is a contained availability group?,” https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/contained-availability-groups-overview (all retrieved November 5, 2026). ↩︎1 ↩︎2 ↩︎3

  8. Tailscale, “Grants syntax” (last validated January 5, 2026; retrieved November 5, 2026). https://tailscale.com/docs/reference/syntax/grants ↩︎

  9. Tailscale, “ACL policy examples,” section “Allow all (default ACL)” (last validated February 2, 2026; retrieved November 5, 2026). https://tailscale.com/docs/reference/examples/acls ↩︎

  10. HashiCorp, “Dev server mode,” https://developer.hashicorp.com/vault/docs/concepts/dev-server, and “Production hardening,” https://developer.hashicorp.com/vault/docs/concepts/production-hardening, Vault documentation (both retrieved November 5, 2026). ↩︎

Related insights

Database Administration

The Complete Guide to AWS RDS SQL Server: Migration, Security, and Operations

Data Engineering

Streamlining Data Pipelines: Leveraging Distributed Airflow Workers Across Customer Networks

Database Administration

SQL Server Maintenance Best Practices: Keep Your Database Running Smoothly