Azure Synapse Analytics — user setup guide

What to create on your Synapse workspace's dedicated SQL pool (every fact below was verified on a real DW100c pool) so DBSheriff can monitor it. Scope: dedicated SQL pool only — serverless SQL pool has no dm_exec_requests-equivalent DMV at all and is not monitored by this engine.

Every fact and T-SQL statement in this guide was run against a live, Terraform-provisioned Synapse workspace during the 2026-09-15 engine buildout — this is not written from Microsoft's documentation alone, and in several places it directly contradicts general claims about how Azure-managed SQL engines behave (see §5).

0. What must be enabled on the server

The floor. A VIEW DATABASE STATE monitor account gives you live + completed statement monitoring. Kill and lock genuinely need the workspace's own SQL administrator credentials — there is no way around this on the current version of this engine (confirmed live, see §2). Nothing here needs a pool restart; every grant is a plain GRANT, applied live.

1. The accounts

-- connect to master first for CREATE LOGIN -- Synapse requires this,
-- unlike a plain database-scoped CREATE USER (live-verified 2026-09-15:
-- "User must be in the master database" if attempted from the pool database)
CREATE LOGIN synapse_monitor WITH PASSWORD = '<pick a strong password>';

-- then connect to the pool database itself for CREATE USER / GRANT
CREATE USER synapse_monitor FOR LOGIN synapse_monitor;
GRANT VIEW DATABASE STATE TO synapse_monitor;
GRANT SELECT ON SCHEMA::dbo TO synapse_monitor;
GRANT VIEW DEFINITION ON DATABASE::<pool database> TO synapse_monitor;   -- Account Inventory, see §2

CREATE LOGIN synapse_classify WITH PASSWORD = '<pick a strong password>';
-- (then, per schema to scan, connected to the pool database)
CREATE USER synapse_classify FOR LOGIN synapse_classify;
GRANT SELECT ON SCHEMA::<schema> TO synapse_classify;

For the Azure service principal (posture/ARM):

az ad sp create-for-rbac --name "dbsheriff-posture-<workspace>" \
  --role "Reader" \
  --scopes "/subscriptions/<sub>/resourceGroups/<rg>/providers/Microsoft.Synapse/workspaces/<workspace>"

Keep the result as the pool's posture credential, posture_service_account_key (sent when you register the pool, below), a JSON string with these key names: tenant_id (the tenant that az prints), client_id (its appId), client_secret (its password), plus subscription_id, resource_group, workspace_name, and sql_pool_name. All seven are required; az's own names (tenant, appId, password) are not accepted.

Register the pool

The Admin Console's Monitored Servers form has no Azure Synapse choice yet, so the pool is registered through the Admin Console's API, the call the form makes for every other engine: POST /api/monitored-servers, signed in as an approver. Send these fields:

Synapse must be among the engines your licence covers and in the installer's engine list (the producers of an engine start only then), and the producer picks the new server up at its next registry reload (five minutes by default).

2. Grant reference (why each one is needed — and what was ruled out)

• VIEW DATABASE STATE — the one narrow, working grant on this engine. Live-verified sufficient for cross-user visibility into every DMV the producer needs (dm_pdw_exec_requests, dm_pdw_exec_sessions, dm_pdw_lock_waits) — no admin rights required for monitoring itself.
• VIEW DEFINITION ON DATABASE::<pool database> — required for Account Inventory, added 2026-09-19. Without it, sys.database_principals silently returns only 5 rows for synapse_monitor — itself plus the built-in dbo/guest/INFORMATION_SCHEMA/sys principals — even though every real account genuinely exists and the query itself runs with no error. Unlike on-prem SQL Server, where sys.database_ principals is visible in full to any user with CONNECT, this dedicated-pool engine filters it by metadata-visibility permission — confirmed live: the identical query as the workspace admin returns all 12 real principals, as synapse_monitor (pre-fix) only 5. This silently starved Account Inventory (governance.synapse_identities) of every account except the monitor's own the whole time this engine has existed — not a staleness/timing issue, a genuine missing grant. VIEW DEFINITION at the database scope is the narrowest fix found — it does not grant any ability to read table data or change anything, only to see other principals' names/types/role memberships via the catalog views.
• Kill needs admin — confirmed, not assumed. Microsoft's own docs say KILL on Azure Synapse Analytics "requires Admin permissions," and this was independently verified live: there is no server-scope securable class on this engine at all — GRANT CONTROL SERVER and even GRANT VIEW SERVER STATE (a read-only grant on plain SQL Server) both fail outright with "Securable class 'server' not supported in the server scope in this version of SQL Server" (error 40521). This is not a missing role or a permission oversight to work around; it is a structural property of this engine. Budget for the kill credential to be (or hold equivalent rights to) the workspace's own SQL administrator login.
• Lock needs the same admin access, for a different reason. ALTER LOGIN <login> DISABLE runs with no error but does not actually block a new connection — live-verified by connecting successfully immediately afterward. The mechanism that actually works is DENY CONNECT TO <user> at the database level, which a database-level CONTROL grant is enough to issue — but this alone was not tested independently of admin access for the session-termination rung (KILL on the target's open sessions), which needs the same admin-level access as the kill credential above.
• sys.fn_get_audit_file also needs admin, and a database-level CONTROL grant is not enough — confirmed live: CONTROL on the pool database lets the function call run without a permission error, but it returns zero rows regardless; only the actual workspace SQL administrator login gets real data back. This is why auth/statement audit collection ships parked in this release (see §5) rather than wired up under a dedicated, narrower "posture-audit" credential the way every other engine's audit reader works.
• Built-in Azure "Reader" role, not "SQL Security Manager." Azure SQL Database's own PS09 fix uses "SQL Security Manager," which does not cover Microsoft.Synapse/* actions at all — confirmed live via a 403 AuthorizationFailed on a plain workspace read. Plain "Reader" works for every ARM call this engine's posture/config-snapshot step needs.
• Classification is the one place a plain SELECT ON SCHEMA::dbo grant is genuinely enough — unlike Redshift. sys.tables/sys.columns/ sys.extended_properties on a dedicated SQL pool are not themselves scoped by schema USAGE the way Redshift's svv_all_columns is — live-verified 2026-09-15: synapse_classify with only SELECT ON SCHEMA::dbo sees every table's full column list, no separate USAGE grant needed. Real value-sampling (reading actual row data to confirm a name-based PII guess) still needs that same SELECT, same as every other engine.

3. What DBSheriff reads

Producer (no setup needed beyond §1-2 for the monitor account)

sys.dm_pdw_exec_requests joined to sys.dm_pdw_exec_sessions on session_id is a single unified view carrying both currently-running rows (status = 'Running') and completed rows (status IN ('Completed', 'Failed', 'Cancelled')) — the full query text is available directly (command/command2), with no separate text-resolution call needed (better than plain SQL Server, which needs sys.dm_exec_sql_text). These DMVs are capped at 10,000 rows each and reset entirely when the pool is paused or scaled (Microsoft's own documented behavior) — there is no cumulative counter to diff, so completed-statement capture is a plain time-window lookback, not an offset/cursor.

sys.dm_pdw_lock_waits carries no direct blocking_session_id column (the same shape gap Redshift's pg_locks has) — DBSheriff pairs a waiting session against a granted one on the same object_name. Not fully live-verified this pass: a deliberate two-session concurrent-UPDATE test did not reproduce a captured wait row in the time available (the second statement completed immediately rather than blocking) — the query is implemented against the DMV's real, live-confirmed columns, but its end-to-end behavior under a genuine lock conflict needs a follow-up check.

Classification scan (synapse_classify, §1)

sys.tables joined to sys.schemas/sys.columns/sys.types, with sys.extended_properties for any existing column comments and sys.partitions for a row-count estimate — one connection per registered pool, unqualified (a dedicated SQL pool has no cross-database references at all, the same limitation Azure SQL Database has, so there's nothing to enumerate beyond the one pool the connection is already scoped to). t.is_ms_shipped = 0 and excluding the sys/INFORMATION_SCHEMA/ dbsheriff schemas keeps DBSheriff's own plumbing and system objects out of the candidate list, same as the SQL Server engine.

Two real, engine-specific findings from building this (2026-09-15): - The sys.partitions row-count estimate is unreliable on this engine specifically — live-verified returning 1,000 for a table that actually has 25 rows (a dedicated pool's distributed columnstore rounds row-group estimates up in a way plain SQL Server's rowstore partitions don't). It's carried through as an approval-queue hint only, exactly like every other engine's own row-estimate caveat — never used for scoring, and not worth fixing with a real COUNT(*) per table, which would be far too expensive against a real customer's data. - TABLESAMPLE SYSTEM is rejected outright for value-sampling ("The SYSTEM option for TABLESAMPLE is not supported", error 105058) — unlike plain SQL Server, where it works and only the empty-table case falls back. Value-sampling here always uses a plain TOP N, so (like MySQL's/Oracle's/HANA's own accepted first-N-rows tradeoff) a very large table's sample may skew toward whatever the columnstore happens to return first rather than a truly random slice.

4. Verification

-- cross-user visibility actually works (run as synapse_monitor, not the admin login)
SELECT r.request_id, r.session_id, r.status, s.login_name, s.app_name,
       LEFT(COALESCE(r.command2, r.command), 100) AS statement
  FROM sys.dm_pdw_exec_requests r
  JOIN sys.dm_pdw_exec_sessions s ON r.session_id = s.session_id
 ORDER BY r.submit_time DESC;

-- QUOTED_IDENTIFIER must be ON for this session -- Synapse rejects the
-- sqlcmd default (OFF) outright with "Setting QUOTED_IDENTIFIER to 'OFF'
-- is not supported"; confirm your driver/ODBC connection sets it:
SELECT @@options & 256;  -- non-zero means QUOTED_IDENTIFIER is ON

Expected producer log line on first successful poll: [<server>][poll] nothing new (or a captured statement if one was running) — no permission error, which would mean VIEW DATABASE STATE is missing.

-- classification catalog access (run as synapse_classify)
SELECT s.name, t.name, c.name FROM sys.tables t
  JOIN sys.schemas s ON s.schema_id = t.schema_id
  JOIN sys.columns c ON c.object_id = t.object_id
 WHERE t.is_ms_shipped = 0;

Then run sudo docker exec -it dbsheriff-<env>-admin python scripts/classify_scan.py --engines synapse --server <name> (<env> is the environment name you gave the installer, prod by default) — expect PENDING candidates for any real column names matching DBSheriff's PII name rules, with the two-evidence-source ones (e.g. a ssn column whose sampled values pass the SSN validator) auto-approved.

5. Known limitations

• Kill and lock need the workspace SQL administrator's own credentials — the single biggest departure from every other engine DBSheriff supports, all of which have at least a narrower, purpose-built kill/lock grant. This is a structural property of Synapse's dedicated SQL pool (see §2), not a gap in this integration.
• Auth/statement audit collection is parked. sys.fn_get_audit_file's documented minimum, CONTROL SERVER, cannot be granted on this engine at all (no server-scope securable class exists); a database-level CONTROL grant lets the call succeed but returns no real data. The governance collector's other categories (identities, config snapshot, admin events, access log, coverage, PS20) are unaffected.
• WAITFOR DELAY is not supported on a dedicated SQL pool (live-verified: a parse error, "Incorrect syntax near 'WAITFOR'") — if you need a genuinely slow test query for your own validation, use a real cross-join of actual tables (COUNT_BIG(*) to avoid int overflow on a large enough cross-join), not WAITFOR.
• No auto-pause. Unlike Azure SQL Database's General Purpose Serverless tier, a Synapse dedicated SQL pool must be paused manually (az synapse sql pool pause) to stop compute billing — and the DMVs reset when it is (see §3).
• Reconnecting after unlock may be delayed beyond what the permission state alone would suggest. A real, only-partially-explained finding: after DENY CONNECT then REVOKE CONNECT, the target login kept hitting a ~30-second TCP/login timeout for well over a minute afterward, even though sys.database_permissions confirmed the deny was genuinely gone. Looks like an Azure-gateway-level cool-down after repeated failed/denied connection attempts from the same principal, not something DBSheriff's own code controls — do not assume UNLOCKED means immediately reconnectable under real (not testing-storm) traffic without checking.
• A locked-then-unlocked account with an explicit GRANT CONNECT needs DBSheriff's own unlock, not a manual REVOKE CONNECT. Synapse's GRANT/DENY permission state is a single tri-state per (principal, permission, securable) — DENY CONNECT (the lock mechanism above) silently overwrites any prior explicit GRANT CONNECT a login had, and a bare REVOKE CONNECT only clears that back to "no explicit entry," it cannot resurrect the original GRANT. A login that relied on an explicit grant to connect (most non-admin accounts; PRIVILEGED-tier logins are unaffected since CONTROL already implies CONNECT) would be left permanently unable to log in after an otherwise-successful unlock. Found+fixed live 2026-09-18: unlock_synapse() now records whether CONNECT was an explicit grant before locking and re-issues it on unlock — this is handled automatically by DBSheriff's own lock/ unlock flow, not something you need to grant or verify separately. Only relevant if you ever lock/unlock an account by hand outside DBSheriff.
• Serverless SQL pool is out of scope entirely — no dm_exec_requests-equivalent DMV, no confirmed KILL-via-SQL. Monitoring it would need a REST/Studio-Monitor-based approach, shaped like DBSheriff's Databricks integration, not a database connection.