Build Azure Data Factory SQL-to-ADLS ingestion with managed identity

1. Define the ingestion boundary and data contract

This pattern copies an approved SQL view into an ADLS Gen2 landing area. Establish the cloud, IR, SQL connector version, hierarchical-namespace-enabled storage account, target filesystem, encryption settings, and expected schema. All stores, staging areas, and identities must fit the approved provider offering and application boundary.

Define allowed columns and classifications before building the dataset. Use synthetic data for the first run. A landing zone needs explicit retention, authorized readers, and a publication process; a successful Copy does not establish that its contents are ready for analysts or model ingestion. If the output feeds AI, preserve the source permissions and review the Foundry RAG authorization design before indexing.

2. Enable the factory identity and grant data access

Use the factory’s system-assigned managed identity where the connector supports it. A user-assigned identity is another supported option for these connectors when represented through an ADF credential object; verify its setup and lifecycle in the target cloud. Deployment roles on the factory do not grant SQL or storage data-plane access.

Have a database administrator create the contained user using the configured Microsoft Entra administrator, then grant a narrow object permission. The administrator may need directory permissions for identity resolution; do not make the runtime identity a server administrator to work around that setup. Replace these example names with reviewed identities and objects:

CREATE USER [adf-regulated-ingestion] FROM EXTERNAL PROVIDER;
GRANT SELECT ON OBJECT::ingest.ApprovedExport
  TO [adf-regulated-ingestion];

For ADLS, grant access at the intended filesystem/container rather than the whole storage account where possible. Storage Blob Data Reader permits reading; Storage Blob Data Contributor permits writes and deletes, so verify whether ACL-based access better matches a write-only requirement. With ACL-based access, the identity needs execute/traverse permission on ancestors and the required read or write permission on the specific path.

Broad Azure RBAC data grants can allow access without the restrictive ACL check you intended. Do not combine an account-wide data role with a narrow directory ACL and assume the ACL confines that identity. Test an unrelated filesystem and document both RBAC and ACL decisions.

3. Create cloud-correct linked services with an explicit IR

The Azure SQL example uses the current documented connector properties. Verify the connector generation and supported encryption value before importing it; older connection-string examples have a different schema. The Government host is illustrative and must be replaced with the actual server’s endpoint.

{
  "name": "ls_approved_sql",
  "properties": {
    "type": "AzureSqlDatabase",
    "typeProperties": {
      "server": "APPROVED_SERVER.database.usgovcloudapi.net",
      "database": "APPROVED_DATABASE",
      "encrypt": "mandatory",
      "trustServerCertificate": false,
      "authenticationType": "SystemAssignedManagedIdentity"
    },
    "connectVia": {
      "referenceName": "ir_approved",
      "type": "IntegrationRuntimeReference"
    }
  }
}

For system-assigned identity, the ADLS linked service uses AzureBlobFS with the endpoint and no account key. Use the ordinary DFS service hostname and verify its private resolution. Government storage endpoints differ from commercial dfs.core.windows.net:

{
  "name": "ls_approved_lake",
  "properties": {
    "type": "AzureBlobFS",
    "typeProperties": {
      "url": "https://APPROVED_ACCOUNT.dfs.core.usgovcloudapi.net"
    },
    "connectVia": {
      "referenceName": "ir_approved",
      "type": "IntegrationRuntimeReference"
    }
  }
}

Use matching runtime references for source and sink in this design. A self-hosted node’s operating-system account is distinct from the identity used by an ADF managed-identity connector. Verify the connector’s actual identity flow rather than assuming the VM identity is the factory identity. Test retrieval and authentication from that selected IR.

4. Configure datasets, projection, and the Copy activity

Create an Azure SQL dataset for ingest.ApprovedExport and a Parquet dataset using AzureBlobFSLocation. Parameterize only the approved landing batch path; avoid allowing callers to supply arbitrary storage accounts, connection strings, or SQL text. Use a stable batch identifier across retries and a unique staging path per batch.

Select required columns explicitly, map types deliberately, and keep schema drift disabled unless the ingestion contract permits it. Treat precision, UTC timestamp representation, nullability, and source key uniqueness as acceptance requirements. Route malformed input to an access-restricted quarantine path with a defined retention policy.

This is an activity excerpt. Referenced datasets and their parameters must be created separately. Hiding inputs and outputs is appropriate where they can expose sensitive queries or payloads; retain sanitized operational metrics through a separate telemetry design.

{
  "name": "CopyApprovedExport",
  "type": "Copy",
  "policy": {
    "timeout": "00:30:00",
    "retry": 2,
    "retryIntervalInSeconds": 60,
    "secureInput": true,
    "secureOutput": true
  },
  "typeProperties": {
    "source": {
      "type": "AzureSqlSource",
      "sqlReaderQuery": "SELECT RecordId, UpdatedUtc, ApprovedValue FROM ingest.ApprovedExport"
    },
    "sink": {
      "type": "ParquetSink",
      "storeSettings": {
        "type": "AzureBlobFSWriteSettings"
      }
    }
  },
  "inputs": [
    {
      "referenceName": "ds_approved_sql",
      "type": "DatasetReference"
    }
  ],
  "outputs": [
    {
      "referenceName": "ds_landing_parquet",
      "type": "DatasetReference",
      "parameters": {
        "batchPath": "staging/APPROVED_BATCH_ID"
      }
    }
  ]
}

5. Protect the remaining credentials and publish only validated data

For connectors that require a secret, reference Azure Key Vault rather than putting the value into linked-service JSON, Git, parameters, or logs. Use environment-specific vaults and grant the consuming identity only the required secret permissions. Plan version selection and rotation together: a fixed secret version provides repeatability but does not automatically adopt a rotated secret.

Verify Key Vault networking with the consuming activity. In the managed-VNet flow, its standalone connection test only validates the URL format. Configure the secret-retrieval path and private endpoint independently, then exercise a successful retrieval and a denied secret read.

Before publishing the landing batch, reconcile row counts, required fields, key uniqueness, and sensitive-column exclusions. Keep the previous published batch available if validation fails. A manifest or control table should record the approved batch, schema version, source window, row count, run ID, and verification result without including row payloads.

6. Prove permissions, data fidelity, and rotation behavior

• The identity reads the approved view but is denied access to an unrelated sensitive table.
• The runtime writes the intended landing filesystem but cannot read or modify an unrelated filesystem.
• Public store paths are denied using a valid identity; private paths complete synthetic ingestion.
• A malformed record remains quarantined and does not advance the published manifest.
• A retry with the same batch identifier produces one logical published batch.
• Secret rotation, identity replacement, and retention/deletion tests behave as documented.

Save sanitized role and ACL exports, schema comparisons, denial results, and reconciliation evidence. Use bounded incremental loading for changing data, and operational monitoring for production checks.

Sources and technical review

Technical review: October 7, 2026. Recheck cloud, regional, connector, and authorization scope before implementation.

• Factory managed identity and lifecycle
• Azure SQL connector identity and encryption properties
• ADLS Gen2 connector identity and permissions
• Store connector credentials in Key Vault
• Pipeline and activity policies
• Azure Government endpoint differences
• ADLS access-control model and RBAC/ACL interaction
• Pipeline ARM schema and secure activity policy

Public offering and service-scope checks are documented in the Data Factory hub. The protected provider authorization package and live Azure behavior have not been reviewed or tested by these guides.