Getting Microsoft Purview to scan a production SQL Server database sounds like it should be a checkbox exercise: register the source, plug in credentials, hit scan. In practice, when the source is a standard SQL Server instance running on a VM behind private networking, there are several layers that all have to line up — authentication, DNS, and firewall rules — before a scan actually completes. Here’s what that setup looked like end to end, including the failure points along the way.

The setup

The target was a standard SQL Server instance running on a VM — not an Azure SQL Managed Instance or Azure SQL Database. That distinction matters immediately, because it determines both the connector type and the authentication approach.

The scan was configured with:

• A Self-hosted Integration Runtime (SHIR) running on a VM with network access to SQL Server
• SQL authentication via a dedicated purview_scanner login
• The production database as scan scope
• The system SQL Server scan rule set

And in Purview’s Data Map, the source was registered as SQL Server, since that’s the connector that matches a self-managed SQL Server instance, wherever it’s hosted.

For the baseline registration and authentication steps, Microsoft’s own guide is a good starting point: Connect to and manage on-premises SQL server instances. It walks through registering the source, setting up SQL or Windows authentication, and configuring a self-hosted integration runtime — but on its own it wasn’t enough to get a scan running here, since it doesn’t cover the private networking, DNS, and firewall layer that turned out to be the real blocker in this setup.

Problem 1: A successful connection test doesn’t mean the scan will work

The Purview connection test passed cleanly — it could list tables and views without issue. That confirmed three things: the SHIR could reach SQL Server, the credential was valid, and it had the access it needed.

The full scan still failed, with an error about being unable to reach Purview’s managed resources.

The reason is that a connection test and a full scan exercise different network paths:

Connection test:  SHIR → SQL Server
Full scan:        SHIR → SQL Server → Purview ingestion storage

Purview doesn’t just read from the source during a scan — it streams metadata into its own ingestion storage account. The connection test never touches that path, so it can pass while the scan still has nowhere to send its output.

Problem 2: Ingestion endpoints need their own private connectivity

Looking at the Purview account’s Azure resource properties surfaced the ingestion storage account and its endpoint. The SHIR VM couldn’t resolve the required Blob or Queue endpoints — the existing ingestion private endpoints had no usable DNS integration for this particular network path.

The fix was to create new ingestion private endpoint connections directly from the Purview account:

Purview account → Networking → Ingestion private endpoint connections

These were placed in the designated Private Endpoint subnet.

Problem 3: Wiring up DNS through centrally managed zones

Rather than spinning up duplicate private DNS zones in the workload subscription, the existing centrally managed zones were reused. This follows the Cloud Adoption Framework’s landing zone guidance, where private DNS zones live in a central connectivity subscription and get linked out to workload virtual networks — it’s also Microsoft’s recommended approach, since it avoids fragmented, duplicate DNS zones scattered across subscriptions and keeps name resolution consistent for every landing zone.

privatelink.blob.core.windows.net
privatelink.queue.core.windows.net

Each new Purview ingestion private endpoint got a DNS zone configuration pointing at the appropriate central zone, linked to the relevant virtual network. That produced the correct resolution chain:

<ingestion-storage>.z<region>.blob.storage.azure.net
  → <ingestion-storage>.privatelink.blob.core.windows.net
  → private IP of Blob endpoint
<ingestion-storage>.z<region>.queue.storage.azure.net
  → <ingestion-storage>.privatelink.queue.core.windows.net
  → private IP of Queue endpoint

Problem 4: DNS resolved, but the traffic still didn’t get through

With names resolving to private IPs, the next expectation was that connectivity would just work. It didn’t — HTTPS calls to the private endpoint IPs timed out.

Azure Firewall logs pointed to the cause: a broad spoke-to-spoke deny rule was blocking the east-west traffic between the SHIR’s subnet and the private endpoint subnet.

Rather than loosening that policy broadly, a narrow exception was added ahead of the global deny rule:

Source:            SHIR VM IP address or source subnet
Destination:       Purview ingestion Blob and Queue private endpoint IPs
Protocol:          TCP
Destination port:  443
Action:            Allow

The broad deny rule stayed in place for everything else — this was a scoped carve-out, not a policy change.

Validating before rerunning the scan

Before touching Purview again, it’s worth confirming both DNS and TCP connectivity from the SHIR VM directly:

Resolve-DnsName <blob-ingestion-fqdn>
Resolve-DnsName <queue-ingestion-fqdn>
Test-NetConnection <blob-ingestion-fqdn> -Port 443
Test-NetConnection <queue-ingestion-fqdn> -Port 443

Both endpoints should resolve to a private IP, and both connection tests should report:

TcpTestSucceeded : True

Once that’s true for both Blob and Queue, the scan can be rerun and checked in Data Map’s scan run details.

Key takeaways

• Use the SQL Server connector for a standard SQL Server instance, even if it happens to run in Azure — this isn’t an Azure SQL Managed Instance or Azure SQL Database scenario.
• A passing Purview connection test only proves SQL access — it says nothing about ingestion-storage reachability.
• Purview scans need private connectivity to both the ingestion Blob and Queue endpoints, not just one.
• DNS resolution and network access are separate problems. Resolving to a private IP doesn’t mean the firewall will let traffic through.
• Route private DNS through centrally managed zones per the Cloud Adoption Framework rather than duplicating zones per subscription — it’s also Microsoft’s recommended approach.
• Keep broad security policies (like a spoke-to-spoke deny rule) intact, and add narrowly scoped exceptions for specific paths like this one.