cloud

Scanning a SQL Server on a VM with Microsoft Purview

/ cloudfriendly / Leave a comment

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.

Building Reusable Terraform Modules and Consuming Them Securely with a GitHub App

/ cloudfriendly / Leave a comment

If your platform team publishes shared Terraform modules, you’ve probably run into the same problem eventually: how do you let other repositories pull those modules in CI without handing out a personal access token tied to someone’s GitHub account? PATs work, but they’re a liability — they expire unpredictably, they’re bound to a human, and when that person leaves the team, your pipelines break.

The fix is a GitHub App. It gives your automation its own non-human identity, with scoped permissions and short-lived tokens minted fresh on every workflow run. Here’s how to set the whole pattern up, from module publishing through to consumption in Actions.

Who this is for

This guide is for platform teams that:

  • Publish reusable Terraform modules in a private GitHub Enterprise repository
  • Consume those modules from other private repositories
  • Want CI authentication that isn’t tied to personal accounts

The goal

By the end, you’ll have:

  1. A module producer repository that publishes pinned module versions
  2. Consumer repositories that download those modules in GitHub Actions
  3. Authentication handled by a GitHub App with short-lived tokens
  4. Azure authentication for Terraform handled via OIDC

Part 1: Publish modules in a dedicated repository

Structure your module repo like this:

modules/terraform_azurerm_<module_name>
examples/terraform_azurerm_<module_name>
docs/MODULE-CONSUMPTION.md
docs/MODULE-PUBLISHING.md

Always pin module usage to immutable tags — never a moving ref like main:

module "example" {
  source = "git::https://<your-ghe-host>/<org>/<module-repo>.git//modules/terraform_azurerm_<module_name>?ref=v1.1.3"
}

A few publishing practices that save you pain later:

  • Use semantic version tags
  • Never consume modules from moving refs
  • Keep your examples runnable and validated, not just illustrative

Part 2: Why a GitHub App beats a PAT

A PAT represents a user. Whoever generated it, the token acts as them — with their permissions, tied to their account’s lifecycle.

A GitHub App gives you something better:

  • A non-human identity built for automation
  • Permissions scoped to specific repositories
  • Short-lived installation tokens, minted fresh at workflow runtime

The result: no long-lived, user-bound credentials sitting in your CI secrets.

Part 3: Create and install the GitHub App

Create the app in your GitHub Enterprise settings with the minimum permissions it actually needs:

  • Repository Contents: Read-only
  • Repository Metadata: Read-only

Install it only on the repositories that need it — for module consumption, that’s your module repo (<org>/<module-repo>).

Generate a private key and store both values as secrets in the consumer repository:

  • GHE_MODULES_APP_ID
  • GHE_MODULES_APP_PRIVATE_KEY

Two things that trip people up here:

  • The App ID must be numeric
  • The private key must include the full PEM content, BEGIN and END lines included

Part 4: The consumer workflow pattern

In your consumer workflow, mint a GitHub App token and rewrite git URLs before terraform init runs:

- name: Create GitHub App token for private modules
  id: ghe_app_token
  if: ${{ env.GHE_MODULES_APP_ID != '' && env.GHE_MODULES_APP_PRIVATE_KEY != '' }}
  uses: actions/create-github-app-token@v1
  with:
    app-id: ${{ env.GHE_MODULES_APP_ID }}
    private-key: ${{ env.GHE_MODULES_APP_PRIVATE_KEY }}
    owner: ${{ github.repository_owner }}
    github-api-url: ${{ github.api_url }}
- name: Configure git for private modules
  if: ${{ steps.ghe_app_token.outputs.token != '' }}
  run: |
    git config --global url."https://x-access-token:${{ steps.ghe_app_token.outputs.token }}@<your-ghe-host>/".insteadOf "https://<your-ghe-host>/"

From there, Terraform runs exactly as it would otherwise:

terraform init
terraform validate
terraform plan
terraform apply  # where relevant

Part 5: Validating the setup

After you’ve wired this up, check your workflow logs for:

  • Successful GitHub App token creation
  • Successful module download from the private module repository
  • Terraform plan running cleanly for both test and prod, where applicable

Part 6: Troubleshooting

If module authentication fails, work through this checklist:

  1. Is the app installed on the module repo?
  2. Does it have Contents and Metadata read permissions?
  3. Are GHE_MODULES_APP_ID and GHE_MODULES_APP_PRIVATE_KEY actually present in the workflow environment?

Wrapping up

This pattern — pinned module versions, a purpose-built GitHub App identity, and short-lived tokens minted at runtime — gives teams a secure, repeatable way to scale Terraform module consumption across repositories, without a single personal credential in sight.