Arc-Enabled SQL Server Gone Wrong: Fixing Failed Onboarding, Billing Tags & Missing Inventory

Finance forwards you the Azure invoice with a line item nobody recognised: SQL Server – Pay-as-you-go, several hundred dollars against a server you already own a perpetual license for. Or the security team asks why three of your SQL hosts show up in the SQL Server instances blade and the fourth doesn’t. Or the patching team complains that the new database the app team created last month never appeared in inventory, so it never got scanned. None of these are SQL bugs. They are SQL Server enabled by Azure Arc doing exactly what it was configured to do — which, when the configuration drifted or was never set, is rarely what you wanted.

Arc-enabled SQL Server projects a SQL Server instance running anywhere — your data centre, another cloud, an Azure VM — into Azure as a manageable resource. The mechanism is a small extension, the Azure extension for SQL Server (WindowsAgent.SqlServer on Windows, LinuxAgent.SqlServer on Linux), riding on the Azure Connected Machine agent you already installed to Arc-enable the host. Once it lands, each instance becomes a Microsoft.AzureArcData/sqlServerInstances resource: you see its edition and version, its databases roll up into an inventory, you can enrol it for Extended Security Updates, and — the part that surprises people — it carries a LicenseType that decides whether Microsoft bills you hourly for the SQL software. Get that property wrong and the platform charges you for a license you already paid for. Leave the inventory permissions wrong and half your estate is invisible to governance. Block one outbound port and the instance never onboards at all.

This is the diagnostic playbook for when that goes sideways. We treat the four pains — failed/partial onboarding, wrong billing (LicenseType / tags), missing instances or databases in inventory, and the extension that won’t deploy or update — as distinct symptom classes, each confirmed with an exact az command, KQL query, or portal path, and localised to one link in the chain: host Arc agent → SQL extension → SQL discovery → ARM resource → billing meter. Because this is a reference you return to mid-incident, the playbook, the LicenseType matrix and the error states are all tables — read the prose once, keep the tables open when finance is on the call.

What problem this solves

Arc-enabled SQL Server hides a lot of machinery so an on-prem or multicloud SQL instance can be governed like a native Azure resource. That abstraction is a gift until something is misconfigured, and then it fails quietly — the most expensive failure mode there is. Nothing throws a red banner when your LicenseType is PAYG against a server you own outright; the only signal is a number on next month’s invoice. Nothing alerts you when a database is missing from inventory; it simply isn’t there, and your compliance report counts what it sees, not what exists. The information you need is captured — in the extension settings, the sqlServerInstances resource, the SQL error log, and Resource Graph — but if you don’t know which place maps to which symptom you burn a day clicking blades while the meter runs.

What breaks without this knowledge: someone disables Arc to “stop the charges” (losing governance and ESU eligibility with the bill), opens a billing dispute that goes nowhere (the charge is legitimate for the configured LicenseType), or calls the estate “fully inventoried” when a third of the databases never showed up. Meanwhile the actual cause — a tag set wrong during automatic onboarding, an extension that deployed but never had SQL management enabled, or a service account that lost sysadmin after a hardening sweep — sits there, perfectly diagnosable, ignored. It hits any team running SQL Server outside Azure PaaS under Azure governance, hardest during a license true-up, a compliance audit, or an end-of-support migration (where ESU enrolment fails because the LicenseType doesn’t qualify). The fix is almost never “turn off Arc” — it is “find the link in the chain that drifted and set it back to the truth.”

To frame the field, here is every symptom class, the question it forces, and the first place to look:

Learning objectives

By the end of this article you can:

• Map any Arc-enabled SQL Server problem to a link in the chain — host agent → SQL extension → SQL discovery → ARM resource → billing meter — and name the likely root cause.
• Diagnose failed onboarding: an unregistered Microsoft.AzureArcData provider, blocked outbound endpoints, an unsupported region, or a Disconnected host — confirming each with exact commands.
• Read and correct the LicenseType (Paid, PAYG, LicenseOnly, Undefined), know exactly what each bills, and use the ArcSQLServerExtensionDeployment tag and Azure Policy to enforce it at scale.
• Fix missing inventory: enable SqlManagement, restore the NT AUTHORITY\SYSTEM CONNECT SQL / sysadmin access the agent needs, and recognise why offline databases are correctly absent.
• Troubleshoot the extension itself: a Failed or stuck provisioningState, a pending reboot, an outdated agent, or a least-privilege config that blocked discovery — and force a redeploy.
• Use ExcludedSqlInstances to keep instances out of management, and know the one case where exclusion is ignored (PAYG billing takes precedence).
• Drive the core tools: az connectedmachine, the extension settings JSON, Resource Graph KQL over microsoft.azurearcdata/sqlserverinstances, the SQL error log, and billing meters.

Prerequisites & where this fits

You should understand what Azure Arc is and have at least one server onboarded. If “Arc-enable a server” is fuzzy, read Azure Arc Explained: The Control Plane That Extends Azure Anywhere for the concept and Onboarding Servers to Azure Arc: Connected Machine Agent, Service Principals & Bulk Enrollment for the host onboarding this article sits on top of. You should be comfortable running az in Cloud Shell, reading JSON, and connecting to a SQL Server instance with sqlcmd as an administrator.

This sits in the Hybrid / Troubleshooting track. The host-onboarding article above is its hard prerequisite — Arc-enabled SQL Server is an extension on an already-Arc-enabled machine, so every host-level failure (egress, RBAC, agent health) is upstream of everything here. On governance it leans on tags and policy: Azure Resource Tags: A Governance and Cost-Allocation Strategy explains the tag mechanism, and Azure Policy Remediation Tasks: Fixing Existing Resources with Managed Identity is how you bulk-correct a wrong LicenseType. To query inventory you’ll use Azure Resource Graph: A KQL Inventory Queries Cookbook. If your SQL lives in an Azure VM and you also want backups, Backing Up SQL Server in an Azure VM: Policies and Restore is the adjacent concern.

A quick map of who owns which link, so you escalate fast:

Core concepts

Five mental models make every later diagnosis obvious.

Arc-enabled SQL Server is an extension, not a separate product. When a host is Arc-enabled (Connected Machine agent installed, shows as Microsoft.HybridCompute/machines), the platform can install the Azure extension for SQL Server on it. That extension discovers the instances already installed on that machine and creates one Microsoft.AzureArcData/sqlServerInstances resource per instance. So there are always two layers: the machine resource (the host) and the SQL instance resources. A problem is either at the machine layer (host not connected) or the SQL layer (extension/discovery/billing) — your first split is always “is the host even Connected?”

Discovery is automatic, and that is why surprises happen. On a Connected Machine in a supported region, the platform automatically installs the SQL extension if it detects any instance, and connects the instances to Arc without you asking. That is the root of the billing surprise: a server you Arc-enabled merely to apply tag policy can, by itself, sprout sqlServerInstances resources and — depending on the LicenseType — start a PAYG meter. You can disable the switch and populate an exclusion list, but the default is on.

LicenseType is a billing decision, not a label. Each resource carries a LicenseType with three real values plus an unset state: Paid (a license with Software Assurance or a subscription — Azure Hybrid Benefit, no SQL software charge), PAYG (you pay Microsoft hourly for the SQL software), and LicenseOnly (a perpetual license with no SA — management/portal features only, no paid features, not ESU-eligible). When Undefined, the resource is inventoried but paid features are off. The trap: automatic onboarding may set Paid or PAYG for you via a tag and a first-come-first-served Hybrid Benefit calculation — so a value you never typed can be billing you.

Inventory depends on the agent reading the engine. The deployer runs as NT AUTHORITY\SYSTEM (LocalSystem) and must connect to the instance (CONNECT SQL) and, by default, sit in sysadmin to enumerate databases and configure its per-instance login. If a hardening sweep removed NT AUTHORITY\SYSTEM, or the service account lost sysadmin, discovery registers the instance but can’t read its databases — the instance appears and the list is empty or stale. Also, only online and updatable databases are inventoried, so an offline or read-only-snapshot database is correctly absent.

Everything reaches Azure outbound over 443, and one new endpoint matters. Like the base agent, the SQL extension talks outbound over TCP 443. Beyond the standard Arc endpoints, data services use the *.<region>.arcdataservices.com family. If your egress allow-list was built for plain Arc and never updated, the host onboards fine but the extension can’t phone home — a partial state that looks like a SQL problem but is a firewall problem.

The vocabulary in one table

Before the deep sections, pin down every moving part — the mental model side by side (the glossary repeats these for lookup):

The Arc-enabled SQL chain, link by link

Every symptom localises to one link. Here is the chain “manage this SQL instance” travels — what each link does, what breaks, and how you confirm health:

The discipline is the same as any layered system: don’t theorise about billing while the host is Disconnected. Walk the links top-down and stop at the first red one.

Failed and partial onboarding

“Onboarding failed” means one of two very different things — split them first: either the host never connected, or the host connected but the SQL extension/resource never materialised. Scan, then read the matching detail:

Cause 1 — The host never actually connected

Arc-enabled SQL Server is an extension; if the machine isn’t Connected, nothing SQL works, and the symptom (no SQL resource) misleads you into debugging SQL. Confirm the host first.

Confirm. On the server:

# On the server itself — must show Status: Connected
azcmagent show
# Outbound connectivity preflight (endpoints, proxy, TLS)
azcmagent check

From Azure, the machine resource should exist and report Connected:

az connectedmachine show --name sql-host-01 --resource-group rg-arc-sql \
  --query "{name:name, status:status, agentVersion:agentVersion, region:location}" -o table

Fix. This is a host problem — blocked egress, wrong onboarding role, proxy or clock skew — fully covered in Onboarding Servers to Azure Arc: Connected Machine Agent, Service Principals & Bulk Enrollment. Get azcmagent show to say Connected, then return.

Cause 2 — The Microsoft.AzureArcData resource provider is not registered

sqlServerInstances resources live under the Microsoft.AzureArcData provider. If it was never registered in the subscription, the host connects, the extension may install, but the resource can’t be created — silently. Plain Arc uses Microsoft.HybridCompute, often registered while AzureArcData is not, so this catches teams who “already use Arc.”

Confirm.

az provider show --namespace Microsoft.AzureArcData \
  --query "{provider:namespace, state:registrationState}" -o table
# You also need HybridCompute, HybridConnectivity and GuestConfiguration for the host

Fix. Register it (needs Owner/Contributor at the subscription), then re-trigger the extension or wait for the next discovery cycle:

az provider register --namespace Microsoft.AzureArcData
# Registration is async — poll until Registered
az provider show --namespace Microsoft.AzureArcData --query registrationState -o tsv

The providers the whole feature depends on, and what each unlocks:

Cause 3 — Outbound to the data-services endpoints is blocked

The base agent has its own endpoint list; Arc-enabled data services add the *.<region>.arcdataservices.com family on 443. An allow-list built for plain Arc and never extended leaves the host Connected while the SQL extension can’t report status or upload inventory — a partial state that mimics a SQL fault.

Confirm. azcmagent check includes the data-services endpoints in newer agents; the extension status shows it failing to communicate:

# Re-run the connectivity preflight; look for arcdataservices endpoints failing
azcmagent check

Fix. Allow outbound 443 to the Arc endpoints and *.<region>.arcdataservices.com (plus login/Resource Manager). Prefer a Private Link scope or the documented endpoint list over wildcarding the internet. The endpoint groups you must allow:

Cause 4 — The Arc machine is in an unsupported region

Arc-enabled SQL Server is supported in a defined set of regions. If the Arc machine resource was created outside that set, the host is fine but no sqlServerInstances are created. People place the Arc resource in a “nearest” region without checking the feature’s list.

Confirm. Check the machine’s region against the current supported-regions documentation:

az connectedmachine show --name sql-host-01 --resource-group rg-arc-sql \
  --query location -o tsv

Fix. Onboard the Arc machine into a supported region. The machine region is a property of the connection (set during host onboarding), so changing it means disconnecting and re-onboarding the host — not migrating the workload, which never moves.

Cause 5 — Automatic onboarding is disabled (so nothing auto-appeared)

If you expected auto-discovery and got none, automatic onboarding may be off for the subscription, or the extension was removed. (The same switch prevents auto-discovery when you don’t want it.)

Confirm. Check whether the SQL extension is present on the host:

az connectedmachine extension list --machine-name sql-host-01 --resource-group rg-arc-sql \
  --query "[?contains(name,'SqlServer')].{name:name, state:provisioningState}" -o table

Fix. Re-enable automatic onboarding (a subscription-level feature switch) or install the extension manually with the settings you want. Installing manually also lets you set LicenseType and SqlManagement deliberately from the first second — the safest path for billing.

Wrong billing: LicenseType and the deployment tag

The most expensive and least obvious symptom, because nothing fails — you are simply charged. The whole game is the LicenseType on each resource and what set it. What each value bills and grants:

The reading that saves money: Paid and PAYG cost the same in management terms but bill the software completely differently. Own a license with Software Assurance → Paid (no software charge); if onboarding set PAYG, you’re paying twice. Own a perpetual license without SA → LicenseOnly is correct but forfeits ESU, so plan the SA/subscription before end-of-support, not after.

How a value you never typed got set

Automatic onboarding decides a default LicenseType for you. If you set a default via the ArcSQLServerExtensionDeployment tag, that wins; otherwise an automated determination runs: if your SQL is covered by SA/subscription and you have more purchased licenses than you’ve already committed to Azure Hybrid Benefit, it sets Paid first-come-first-served; failing that it can land on a value that bills. The lesson: set the tag deliberately, or you inherit a heuristic.

Confirm the LicenseType across the estate. Query Resource Graph rather than clicking each resource:

az graph query -q "
resources
| where type =~ 'microsoft.azurearcdata/sqlserverinstances'
| project name, resourceGroup, location,
          licenseType = tostring(properties.licenseType),
          edition = tostring(properties.edition),
          version = tostring(properties.version),
          cores = toint(properties.vCore)
| order by licenseType asc, name asc
" --query "data" -o table

Any row showing PAYG (or Undefined where you expected Paid) on a server you own is the leak.

Confirm the steering tag. It can sit on the resource group or the machine and sets the deployment’s default license:

az resource show --ids "$(az connectedmachine show -n sql-host-01 -g rg-arc-sql --query id -o tsv)" \
  --query "tags" -o json
# Look for ArcSQLServerExtensionDeployment and its value

Fixing the LicenseType — one instance

Set it on the extension settings and the resource property follows. Via az:

# Update the SQL extension settings to assert Paid (Azure Hybrid Benefit)
az connectedmachine extension update \
  --machine-name sql-host-01 --resource-group rg-arc-sql \
  --name WindowsAgent.SqlServer \
  --settings '{"LicenseType":"Paid", "SqlManagement": {"IsEnabled": true}}'

As Bicep — the durable fix, because as code it can’t drift back:

resource sqlExt 'Microsoft.HybridCompute/machines/extensions@2024-07-10' = {
  name: '${machineName}/WindowsAgent.SqlServer'
  location: location
  properties: {
    publisher: 'Microsoft.AzureData'
    type: 'WindowsAgent.SqlServer'
    autoUpgradeMinorVersion: true
    settings: {
      LicenseType: 'Paid'           // Paid | PAYG | LicenseOnly
      SqlManagement: { IsEnabled: true }
    }
  }
}

The deployment tag, set deliberately so future auto-onboarded instances default correctly:

az resource tag --tags ArcSQLServerExtensionDeployment=Paid \
  --ids "$(az connectedmachine show -n sql-host-01 -g rg-arc-sql --query id -o tsv)"

Fixing it at scale with Azure Policy

One server is a command; three hundred is a policy. Use Azure Policy to enforce LicenseType — a Modify/DeployIfNotExists policy targeting the resource type both remediates existing sqlServerInstances (via a remediation task with a managed identity) and keeps future ones aligned. The mechanics are in Azure Policy Remediation Tasks: Fixing Existing Resources with Managed Identity. Which value to enforce where:

Finally, confirm what is on the meter in Cost Management — filter to the SQL Server PAYG meters and group by resource. A correction takes effect going forward only: no retroactive credit for hours billed while a resource was PAYG. That asymmetry — wrong-by-default costs immediately, right-by-correction only helps from now — is why the first onboarding is the moment to get LicenseType right.

Missing inventory: instances and databases that don’t appear

“It’s not in the blade” splits two ways: the instance is missing, or the instance is there but its databases are missing — different causes, different fixes. Scan, then read the detail:

Cause 1 — SqlManagement is off, so the instance is a shell

The extension can be installed with SqlManagement.IsEnabled=false — discovery-light. The resource appears with basic facts but no database inventory and no paid features. Teams who installed it “just to see the server” hit this and assume inventory is broken.

Confirm. Read the extension settings:

az connectedmachine extension show \
  --machine-name sql-host-01 --resource-group rg-arc-sql \
  --name WindowsAgent.SqlServer \
  --query "properties.settings" -o json
# Look for "SqlManagement": { "IsEnabled": false }

Fix. Turn it on (and assert LicenseType here too):

az connectedmachine extension update \
  --machine-name sql-host-01 --resource-group rg-arc-sql \
  --name WindowsAgent.SqlServer \
  --settings '{"LicenseType":"Paid", "SqlManagement": {"IsEnabled": true}}'

Cause 2 — The agent can’t read the engine (NT AUTHORITY\SYSTEM)

This is the deep one. To enumerate databases and configure its per-instance login, the deployer runs as NT AUTHORITY\SYSTEM and must connect to SQL Server (CONNECT SQL). By default NT AUTHORITY\SYSTEM and the SQL service account sit in sysadmin, so it just works — until a CIS-style hardening sweep removes NT AUTHORITY\SYSTEM or strips sysadmin. Then the instance still registers (basic facts come from the OS/registry) but the agent can’t read databases, so the database list is empty or frozen.

Confirm. Check whether NT AUTHORITY\SYSTEM can connect and is privileged:

-- Run as a sysadmin in SSMS / sqlcmd against the instance
SELECT name, type_desc, is_disabled
FROM sys.server_principals
WHERE name IN ('NT AUTHORITY\SYSTEM', 'NT SERVICE\SQLServerExtension');

-- Is NT AUTHORITY\SYSTEM in sysadmin?
SELECT IS_SRVROLEMEMBER('sysadmin', 'NT AUTHORITY\SYSTEM') AS system_is_sysadmin;

The SQL Server error log also records “Login failed for user ‘NT AUTHORITY\SYSTEM’” right when discovery runs — a clean smoking gun.

Fix. Restore the login and the minimum it needs — re-add NT AUTHORITY\SYSTEM and let the extension reconfigure, or (least-privilege) create the login with CONNECT SQL and let the agent provision its NT SERVICE\SQLServerExtension login:

-- Re-create the login if it was removed, and allow it to connect
IF NOT EXISTS (SELECT 1 FROM sys.server_principals WHERE name = 'NT AUTHORITY\SYSTEM')
    CREATE LOGIN [NT AUTHORITY\SYSTEM] FROM WINDOWS;
GRANT CONNECT SQL TO [NT AUTHORITY\SYSTEM];
-- If your discovery still can't enumerate, restore sysadmin (or apply the documented
-- least-privilege role set the extension expects) and restart the extension.

Then restart the extension so the deployer re-runs discovery. The permission chain that must hold for databases to appear:

Cause 3 — The database is offline or not updatable

Inventory lists only databases that are online and updatable. A database that is OFFLINE, RECOVERING, SUSPECT, or a read-only snapshot is correctly absent — the rule excludes it, the agent isn’t hiding it. People file “missing database” tickets for a database that is simply offline.

Confirm.

SELECT name, state_desc, is_read_only, is_in_standby
FROM sys.databases
ORDER BY name;
-- Anything not ONLINE (and updatable) will not appear in Arc inventory

Fix. If it should be serving, bring it online / out of standby and it appears on the next discovery cycle. If it is intentionally offline, the absence is correct — nothing to fix.

Cause 4 — The instance is on the exclusion list

If a specific named instance never appears, it may be in ExcludedSqlInstances — a list (read from the extension settings) of instances to skip, case-sensitive and exact-match. Someone may have excluded a dev instance and forgotten.

Confirm.

az connectedmachine extension show \
  --machine-name sql-host-01 --resource-group rg-arc-sql \
  --name WindowsAgent.SqlServer \
  --query "properties.settings.ExcludedSqlInstances" -o json

Fix. Remove it from the list (re-set the settings without it). The catch worth memorising: you cannot exclude a PAYG instance — billing enforcement takes precedence, so a PAYG instance is always indexed regardless of the list. To remove it, change its LicenseType off PAYG first, or uninstall the extension on that host.

Cause 5 — Inventory is just stale

Discovery runs on a cycle, not instantly — a database created minutes ago, or a LicenseType just changed, can lag the portal. Give it a cycle before deep-diving; to force a re-run, re-apply the extension settings (shown in Cause 1), which nudges the deployer to re-discover.

The extension itself: deploy, update, and status

Sometimes the problem is the extension’s own lifecycle — it never reached Succeeded, or an update is stuck. provisioningState is your truth source:

Confirm the state and message — sub-statuses often name the exact cause (pending reboot, agent too old, permission):

az connectedmachine extension show \
  --machine-name sql-host-01 --resource-group rg-arc-sql \
  --name WindowsAgent.SqlServer \
  --query "{state:properties.provisioningState, type:properties.type, version:properties.typeHandlerVersion}" -o json

The common extension-lifecycle failures and their fixes:

Force a clean redeploy when Failed with an unhelpful message — remove and reinstall:

# Remove the SQL extension, then reinstall with explicit settings
az connectedmachine extension delete \
  --machine-name sql-host-01 --resource-group rg-arc-sql \
  --name WindowsAgent.SqlServer --yes

az connectedmachine extension create \
  --machine-name sql-host-01 --resource-group rg-arc-sql \
  --name WindowsAgent.SqlServer \
  --publisher Microsoft.AzureData --type WindowsAgent.SqlServer \
  --settings '{"LicenseType":"Paid", "SqlManagement": {"IsEnabled": true}}'

Removing the extension removes the management only; it never touches the SQL engine or its data. The instance simply stops being Arc-managed until it’s back.

Architecture at a glance

Hold the whole system as a short mental model and the diagnosis writes itself. Picture three planes. At the bottom is your SQL Server — a real engine on a real machine, in your data centre or another cloud, unchanged by any of this; its databases live in sys.databases and it never moves. In the middle, on that same machine, is the agent plane: the Connected Machine agent (azcmagent) that Arc-enables the host, and on it the Azure extension for SQL Server, whose deployer — as NT AUTHORITY\SYSTEM — logs into the local engine, reads the instances and their online databases, and applies the login it needs. At the top is Azure: the Microsoft.HybridCompute/machines resource and, hanging off it, one Microsoft.AzureArcData/sqlServerInstances resource per instance, each carrying edition, version, database inventory, the LicenseType that drives billing, and the ESU flag.

Now trace the two flows. Outbound, the agent talks to Azure over TCP 443 — to the login and Resource Manager endpoints and, crucially, *.<region>.arcdataservices.com — to create and update those resources and report inventory. Inbound to the engine, the deployer talks to SQL locally as NT AUTHORITY\SYSTEM. Every failure here is a broken link on one path: the outbound path breaks at the firewall (data-services endpoint blocked) or control plane (provider unregistered, region unsupported); the local path breaks at the engine boundary (NT AUTHORITY\SYSTEM can’t connect, database offline). Billing is not a flow at all — it is a property on the top-plane resource, which is why a billing problem has no error and no log line, only a value (LicenseType) you read and a meter you reconcile. Keep that picture and you always know whether to open a firewall, register a provider, run a GRANT, or fix a tag.

Real-world scenario

Northwind Components is a mid-size manufacturer running 40 SQL Server instances: 22 in their own data centre, 12 on Azure VMs (lift-and-shifted years ago), and 6 in AWS after an acquisition. The platform team Arc-enabled every host last quarter to satisfy a board-level mandate for a single governance plane, applied their tag policy, and moved on. Automatic onboarding did its job: the SQL extension landed on each host and 40 sqlServerInstances resources appeared. Everyone called it done.

Eleven weeks later, FinOps flagged the monthly invoice: a $4,100 “SQL Server – Pay-as-you-go” line climbing since onboarding. The DBA lead was adamant the company owned licenses with Software Assurance for the entire estate. The platform team’s first instinct — “Arc is charging us, turn it off” — would have destroyed their governance and ESU eligibility to stop a charge that was their own misconfiguration.

Instead they ran the chain. A single Resource Graph query over microsoft.azurearcdata/sqlserverinstances projecting licenseType told the whole story in one table: 31 instances were Paid, 9 were PAYG. The nine were the AWS-acquired servers plus three data-centre boxes onboarded the same week — and the common factor was that those resource groups had no ArcSQLServerExtensionDeployment tag, so automatic onboarding’s heuristic, finding no Hybrid Benefit headroom for them, had defaulted them to PAYG. The $4,100 was entirely legitimate for PAYG. There was nothing to dispute.

The fix took an afternoon. They authored an Azure Policy with a Modify effect targeting sqlServerInstances, enforcing LicenseType = Paid, scoped to the management group covering the licensed estate, and ran a remediation task with a managed identity to correct all nine existing resources at once (the pattern from Azure Policy Remediation Tasks: Fixing Existing Resources with Managed Identity). They also set the deployment tag on every resource group so future instances default to Paid. The PAYG meter stopped within the hour.

Two lessons stuck. First, the costly default is silent — no error, no alert, just a value on a resource and a number on an invoice eleven weeks later; they added a scheduled Resource Graph check that alerts on any PAYG instance. Second, while reconciling they noticed the 6 AWS instances showed no databases in inventory: the acquisition’s hardened images had removed NT AUTHORITY\SYSTEM from SQL. A GRANT CONNECT SQL and an extension restart on each, and the databases — and therefore Defender’s SQL scanning — finally appeared. The board got its single pane of glass; the platform team got a standing query so the panes never quietly go blank again.

Advantages and disadvantages

The trade-off of bringing SQL Server under Arc:

When each side matters: the advantages dominate with a genuinely heterogeneous SQL estate and a compliance/end-of-support driver — Arc is the only way to get on-prem and other-cloud SQL into Azure governance and ESU without migrating. The disadvantages dominate when the estate is tiny, already all-Azure-PaaS, or run by a team without a DBA to keep engine permissions intact. The deciding factor is who watches the LicenseType and the inventory: with a standing Resource Graph check and a license-enforcing policy the downsides vanish; without them, they are exactly the failures in this article.

Hands-on lab

This lab assumes one Arc-enabled host running SQL Server (Developer or Express edition, free; follow the host-onboarding article first if you don’t). You will inspect the extension, deliberately break and fix inventory permissions, set the LicenseType safely, and tear down. Use a non-production, Developer-edition instance — it’s not billable, so you can practise LicenseType without a meter.

1. Confirm the host and providers.

az connectedmachine show -n sql-host-01 -g rg-arc-sql \
  --query "{status:status, region:location, agent:agentVersion}" -o table
az provider show --namespace Microsoft.AzureArcData --query registrationState -o tsv

Expected: Connected, a supported region, and Registered.

2. List the SQL extension and read its settings.

az connectedmachine extension list --machine-name sql-host-01 -g rg-arc-sql \
  --query "[?contains(name,'SqlServer')].{name:name, state:provisioningState}" -o table

az connectedmachine extension show --machine-name sql-host-01 -g rg-arc-sql \
  --name WindowsAgent.SqlServer --query "properties.settings" -o json

Expected: a Succeeded extension and a settings blob showing LicenseType and SqlManagement.

3. See your instances and databases in inventory (Resource Graph).

az graph query -q "
resources
| where type =~ 'microsoft.azurearcdata/sqlserverinstances'
| project name, licenseType=tostring(properties.licenseType),
          edition=tostring(properties.edition), version=tostring(properties.version)
" --query "data" -o table

Expected: one row per instance on the host, with a LicenseType.

4. Break inventory on purpose, then watch the database list go stale. In sqlcmd/SSMS as a sysadmin:

-- Simulate a hardening sweep that removed SYSTEM's ability to read the engine
DENY CONNECT SQL TO [NT AUTHORITY\SYSTEM];

After the next discovery cycle the SQL Server error log shows failed NT AUTHORITY\SYSTEM logins, and the database inventory stops updating.

5. Confirm and fix.

SELECT IS_SRVROLEMEMBER('sysadmin','NT AUTHORITY\SYSTEM') AS is_sysadmin,
       HAS_PERMS_BY_NAME(NULL,NULL,'CONNECT SQL') AS my_connect;  -- context check
-- Restore connectivity
GRANT CONNECT SQL TO [NT AUTHORITY\SYSTEM];

Then nudge the extension to re-discover:

az connectedmachine extension update --machine-name sql-host-01 -g rg-arc-sql \
  --name WindowsAgent.SqlServer --settings '{"SqlManagement": {"IsEnabled": true}}'

Re-run step 3’s portal/inventory view; databases reappear on the next cycle.

6. Set the LicenseType safely (Developer edition is not billed).

az connectedmachine extension update --machine-name sql-host-01 -g rg-arc-sql \
  --name WindowsAgent.SqlServer \
  --settings '{"LicenseType":"LicenseOnly", "SqlManagement": {"IsEnabled": true}}'

Re-query Resource Graph and confirm licenseType changed. (On a real licensed server you would set Paid; never set PAYG unless you intend to rent the SQL software.)

7. Teardown. Removing the extension unmanages the instance; it does not touch SQL or its data:

az connectedmachine extension delete --machine-name sql-host-01 -g rg-arc-sql \
  --name WindowsAgent.SqlServer --yes

If you want to fully revert, re-add NT AUTHORITY\SYSTEM permissions you changed and (optionally) disconnect the host per the host-onboarding article’s teardown.

Common mistakes & troubleshooting

Keep this open during an incident — it spans the basic failures (provider, port, off switch) and the advanced ones (engine permissions, billing heuristics, PAYG-exclusion precedence). Find your symptom, confirm with the exact command, apply the fix — and ignore the band-aid that only masks it.

Error and status reference

The strings and states you’ll see, and what they map to:

The two traps worth memorising

Two playbook rows defeat a reasonable mental model and so waste the most time. PAYG-on-owned-license (row 5) is silent — no error, only an invoice; confirm with a fleet-wide Resource Graph query, never spot checks, and fix in two parts (remediate existing resources, then set the deployment tag so it can’t recur). PAYG-exclusion precedence (row 10) breaks the assumption “I excluded it, so it shouldn’t bill”: billing overrides exclusion, so a PAYG instance is always indexed regardless of ExcludedSqlInstances — change its LicenseType off PAYG or remove the extension.

Best practices

• Set LicenseType deliberately on day one. Never let the heuristic pick it — set the ArcSQLServerExtensionDeployment tag on the resource group before hosts onboard so every instance defaults right.
• Enforce LicenseType with Azure Policy, not vigilance. A Modify policy scoped to your licensed management group keeps the intended value true forever and remediates drift.
• Run a standing Resource Graph check for PAYG and Undefined. Alert on any unexpected LicenseType — the billing surprise is silent, so make it loud.
• Protect the engine-access path from hardening. Document that NT AUTHORITY\SYSTEM needs CONNECT SQL for inventory, and add it to your CIS/STIG exception list.
• Keep the Connected Machine agent current. Many extension failures are old-agent failures; let autoUpgradeMinorVersion run and patch the base agent on a schedule.
• Update egress allow-lists for data services. Add *.<region>.arcdataservices.com:443 to every relevant firewall/NSG/proxy — plain-Arc allow-lists are insufficient.
• Decide ESU eligibility before end-of-support. LicenseOnly forfeits ESU; move an approaching server to Paid (with SA) or PAYG in advance, not in the last week.
• Manage the extension as code (Bicep). A portal-edited property drifts; the extension settings in Bicep are the durable source of truth for LicenseType and SqlManagement.
• Use ExcludedSqlInstances for genuinely unmanaged instances — but PAYG instances ignore it; control those via LicenseType or by not installing the extension.
• Separate “instance missing” from “database missing” reflexively — different causes; knowing which you have saves the first ten minutes of every inventory incident.
• Confirm at the resource type, not the blade. microsoft.azurearcdata/sqlserverinstances in Resource Graph is fleet-wide and fast; the portal blade is per-resource.

Security notes

Arc-enabled SQL Server adds an extension with privileged local access and an outbound channel — both deserve attention. Least privilege on the host: the deployer runs as NT AUTHORITY\SYSTEM and, by default, with sysadmin; newer agent versions support a least-privilege configuration that replaces broad sysadmin with the narrower role set the extension actually needs. Prefer it where supported, and accept that some engine access is irreducible — inventory cannot exist without the agent reading the engine. Don’t “fix” a missing-inventory ticket by granting more than CONNECT SQL plus the documented role; over-granting turns the extension’s identity into an attack surface.

Identity and tokens: the host’s managed identity (from the Connected Machine agent) authenticates to Azure; treat it like any managed identity and scope its RBAC tightly. If you see token-acquisition failures behind any of this, the patterns in Managed Identity: System-Assigned vs User-Assigned Patterns and the 403 triage in Troubleshooting Managed Identity: Token Acquisition and 403 Errors apply directly.

Network isolation: prefer an Azure Arc Private Link Scope so agent and data-services traffic stays on your private network, and never widen the firewall to *:443 when the real need is two named endpoint families. Defender for SQL is a strong reason to keep inventory healthy: a database missing from inventory is a database Defender never scans — so the inventory hygiene above is also a security control. Finally, SQL credentials are never sent to Azure; the agent reads the engine locally and ships metadata, so the security boundary is the host, not the wire.

Cost & sizing

What drives the bill, and how to keep it honest:

The key sizing fact is the vCore count per instance, because both PAYG and ESU bill on cores — an over-provisioned host inflates both meters, so right-size before enrolling. Free of charge: SQL Developer or Express edition under Arc is never billed for the software regardless of LicenseType, which is why the lab uses Developer. In INR terms the management plane and a Developer lab cost effectively nothing; the costs that matter (PAYG and ESU) start only with a production edition and a deliberate choice — so the cheapest mistake to avoid is the accidental PAYG on a Standard/Enterprise instance you already own. If these run on Azure VMs and you want backups too, factor in the separate cost in Backing Up SQL Server in an Azure VM: Policies and Restore.

Interview & exam questions

Q1. What resource type represents a SQL Server instance enabled by Azure Arc, and what is its parent? Each instance is a Microsoft.AzureArcData/sqlServerInstances resource. It sits conceptually under the host’s Microsoft.HybridCompute/machines resource, because the SQL extension that creates it runs on an already-Arc-enabled machine. You always split a problem into “host layer” versus “SQL layer.”

Q2. A server you own (with Software Assurance) is billed SQL Server PAYG. Why, and how do you fix it across 50 servers? LicenseType is PAYG — likely chosen by automatic onboarding’s heuristic because no ArcSQLServerExtensionDeployment tag steered it to Paid. Fix existing resources with an Azure Policy Modify effect plus a remediation task, and set the tag on resource groups so future instances default to Paid. Corrections are not retroactive.

Q3. An Arc-enabled SQL instance appears but lists no databases. Most likely cause? The deployer (running as NT AUTHORITY\SYSTEM) can’t connect to the engine — usually a hardening sweep removed the NT AUTHORITY\SYSTEM login or stripped sysadmin. Confirm via the SQL error log (failed NT AUTHORITY\SYSTEM logins) and sys.server_principals; fix with GRANT CONNECT SQL (restore the role), then restart the extension.

Q4. Differentiate Paid, PAYG, and LicenseOnly. Paid asserts a license with SA/subscription (Azure Hybrid Benefit — no software charge, ESU eligible). PAYG rents the software hourly (charged, ESU eligible). LicenseOnly is a perpetual license without SA (no charge, inventory/portal only, not ESU eligible).

Q5. You added a dev instance to ExcludedSqlInstances but it’s still managed and billed. Why? Because it uses PAYG billing, and billing enforcement takes precedence over the exclusion list — PAYG instances are always indexed. Change its LicenseType off PAYG (then exclusion works) or uninstall the extension on that host.

Q6. Which resource provider must be registered for sqlServerInstances, and what’s the give-away if it isn’t? Microsoft.AzureArcData. If unregistered, the host can be Connected and the extension may install, but no SQL resource appears — with no error. Confirm with az provider show --namespace Microsoft.AzureArcData.

Q7. The SQL extension is stuck in Creating and never completes. First thing to check? A pending OS reboot often blocks installation. Check the Windows pending-reboot state and the provisioningState; reboot and the extension completes. If the agent is simply old, upgrade it.

Q8. A host is Connected and providers registered, but the SQL extension can’t report to Azure. Likely network cause? The Arc data services endpoints (*.<region>.arcdataservices.com on 443) are blocked though the base Arc endpoints are allowed. Confirm with azcmagent check; allow those endpoints (or use a Private Link scope).

Q9. Why might one database be absent while every other database on the instance is present? Inventory lists only online and updatable databases. One that is OFFLINE, RECOVERING, SUSPECT, or a read-only snapshot is correctly excluded. Confirm with sys.databases.state_desc; bring it online if it should serve.

Q10. How do you change the LicenseType so it sticks and doesn’t drift back? Set it via the extension settings (not the resource property) and manage those settings as Bicep so they’re the source of truth. To prevent drift, layer an Azure Policy Modify effect that enforces the value.

Q11. Defender for SQL is enabled on Arc instances, but one server’s databases are never scanned. Connect the dots. Defender scans what’s in inventory; if those databases are missing (engine-access problem or SqlManagement off), Defender never sees them. The inventory fix (CONNECT SQL for NT AUTHORITY\SYSTEM, SqlManagement.IsEnabled=true) is therefore also a security fix.

Q12. Map to certs. This material maps to AZ-104 (hybrid governance, Arc, Policy), AZ-305 (designing hybrid/governance solutions), and DP-300 (administering SQL Server, including Arc-enabled). The Arc + Policy + billing intersection is increasingly examinable as hybrid estates standardise on Arc.

Quick check

1. A host shows Status: Connected but no sqlServerInstances resource exists and there’s no error. What two configuration items do you check first?
2. You find LicenseType: PAYG on a server your company owns with Software Assurance. What value should it be, and what tag prevents this on future onboarding?
3. An Arc-enabled SQL instance lists no databases. Which account’s engine access do you verify, and with which permission?
4. You excluded an instance via ExcludedSqlInstances but it’s still managed and billed. What property explains the override?
5. Which LicenseType value is not eligible for Extended Security Updates?

Answers

1. The Microsoft.AzureArcData resource provider registration (it’s often unregistered when only plain Arc was used) and automatic onboarding (it may be off, or the machine’s region may be unsupported). If both are fine, install the SQL extension manually.
2. It should be Paid (Azure Hybrid Benefit — no SQL software charge, ESU eligible). Set the ArcSQLServerExtensionDeployment tag (to Paid) on the resource group before onboarding so the heuristic doesn’t pick PAYG.
3. NT AUTHORITY\SYSTEM — the account the extension’s deployer runs as. It must have CONNECT SQL (and, by default, sysadmin to enumerate databases). Confirm via the SQL Server error log and sys.server_principals.
4. PAYG billing takes precedence over the exclusion list — a PAYG instance is always indexed and managed regardless of ExcludedSqlInstances. Change the LicenseType off PAYG or uninstall the extension.
5. LicenseOnly (a perpetual license without Software Assurance). ESU requires Paid or PAYG.

Glossary

• SQL Server enabled by Azure Arc — The feature that projects a SQL Server instance running anywhere into Azure as a manageable sqlServerInstances resource via an extension on the Arc host.
• Azure extension for SQL Server — The extension (WindowsAgent.SqlServer / LinuxAgent.SqlServer, publisher Microsoft.AzureData) that discovers SQL instances and maintains their Azure resources.
• Microsoft.AzureArcData/sqlServerInstances — The ARM resource type for an Arc-enabled SQL Server instance; carries edition, version, database inventory, LicenseType, and the ESU flag.
• LicenseType — Billing/feature mode of an instance: Paid (license + SA), PAYG (rent hourly), LicenseOnly (perpetual, no SA), or Undefined (unset).
• Azure Hybrid Benefit — Using an existing SQL license with Software Assurance (LicenseType=Paid) so no SQL software charge applies under Arc.
• PAYG (pay-as-you-go) — Paying Microsoft hourly per vCore for the SQL software through Azure (LicenseType=PAYG).
• SqlManagement.IsEnabled — Extension setting that turns on full management and inventory; off means a thin resource with no database list or paid features.
• ArcSQLServerExtensionDeployment tag — A tag (on host/resource group) that steers automatic onboarding’s default LicenseType and deployment behaviour.
• ExcludedSqlInstances — Extension setting listing named instances to skip managing; case-sensitive and exact-match — ignored for PAYG instances.
• NT AUTHORITY\SYSTEM — The LocalSystem account the extension’s deployer uses; must be able to CONNECT SQL (and, by default, be sysadmin) to read instances and databases.
• Automatic onboarding — The default behaviour where the platform installs the SQL extension and creates sqlServerInstances resources whenever it detects SQL on a Connected Machine in a supported region.
• Extended Security Updates (ESU) — Paid security patches past a SQL version’s end-of-support; requires LicenseType of Paid or PAYG and the EnableExtendedSecurityUpdates flag.
• Connected Machine agent (azcmagent) — The base Arc agent that connects the host to Azure; the SQL extension rides on top of it.
• arcdataservices.com endpoints — The *.<region>.arcdataservices.com endpoint family (port 443) the SQL/data extension uses to communicate with Azure.
• provisioningState — The lifecycle state of the extension (Succeeded, Creating, Failed, etc.); the truth source for extension-deployment problems.

Next steps

• Get the host right first: Onboarding Servers to Azure Arc: Connected Machine Agent, Service Principals & Bulk Enrollment — every SQL problem here sits on a healthy Arc host.
• Enforce LicenseType at scale: Azure Policy Remediation Tasks: Fixing Existing Resources with Managed Identity — bulk-correct billing across the fleet.
• Query inventory fluently: Azure Resource Graph: A KQL Inventory Queries Cookbook — turn the one-off LicenseType query into a standing report.
• Tag deliberately from the start: Azure Resource Tags: A Governance and Cost-Allocation Strategy — so the deployment tag is right before onboarding.
• Understand the control plane: Azure Arc Explained: The Control Plane That Extends Azure Anywhere — the bigger picture this plugs into.