Azure Connections and Authentication for Data Engineers: Every Service, Every Method, and How to Remember Them All

You can build pipelines, write PySpark, and design Delta tables. But every time you need to connect Service A to Service B, you freeze: “Do I use an access key? A SAS token? A managed identity? A service principal? An endpoint? A connection string? What is the difference? Which one is secure? Which one works for THIS specific combination?”

This confusion is universal. Azure has dozens of authentication methods, each valid for different scenarios. The problem is not that they are complicated individually — it is that nobody organizes them in one place. You learn ADLS access keys in one tutorial, JDBC connection strings in another, and Key Vault RBAC in a third. When you need to connect Databricks to SQL Database via Key Vault, you are stitching together three separate mental models.

This post is your connection reference card. Every Azure service a data engineer uses, every authentication method, every endpoint format, and a decision framework so you never have to Google “how to connect X to Y” again.

Think of Azure authentication like different types of keys in a building. Access keys are the master key (opens everything, dangerous if lost). SAS tokens are a temporary visitor badge (expires, limited access). Managed Identity is facial recognition (the building knows your face, no key needed). Service Principal is an employee badge (permanent, scoped access). Connection strings are the full address with directions. Understanding which “key” to use for which “door” is the entire game.

Table of Contents

• The Five Authentication Methods (And When to Use Each)
• The Master Decision Framework
• ADLS Gen2 / Blob Storage Connections
• Azure SQL Database Connections
• Azure Key Vault Connections
• Azure Databricks Connections
• Azure Data Factory / Synapse Connections
• Microsoft Fabric / OneLake Connections
• Azure Event Hubs / Kafka Connections
• Power BI Connections
• The Connection Matrix: Every Source × Every Target
• Endpoint Formats Reference
• Connection String Formats Reference
• The Secure vs Quick Decision
• Credential Rotation and Lifecycle
• Troubleshooting Connection Failures
• The One-Page Cheat Sheet
• Interview Questions
• Wrapping Up

The Five Authentication Methods (And When to Use Each)

Every Azure connection uses one of these five methods. Learn these five, and you can connect anything to anything:

1. Access Keys (The Master Key)

What: A long string (88 characters) that grants FULL access to the resource
Where: Storage accounts (Blob/ADLS), Cosmos DB, Event Hubs
Danger: Anyone who has the key has full access — read, write, delete EVERYTHING
Rotate: Regenerated manually in Azure Portal

Storage Account Access Key:
xYz123AbCdEfGhIjKlMnOpQrStUvWxYz789AbCdEfGhIjKlMnOpQrStUvWxYz789AbCdEfGhIjKlMn==

Real-life analogy: An access key is the master key to your entire house. Anyone who finds it can open every door, every drawer, every safe. Quick and easy, but if you lose it, everything is compromised. You have two keys (Key1 and Key2) so you can rotate one while the other is in use.

Use when: Quick development, local testing, scripts that need full access Never use in: Production code, shared notebooks, Git repositories

2. SAS Tokens (The Temporary Visitor Badge)

What: A token appended to a URL that grants limited access for a limited time
Where: Storage accounts (Blob/ADLS), SQL Server (less common)
Scope: Container, blob, or account level
Permissions: Read, Write, Delete, List (you choose)
Expires: You set the expiry (1 hour, 1 day, 1 year)

SAS Token (appended to URL):
?sv=2023-01-03&st=2026-05-21T00:00:00Z&se=2026-05-22T00:00:00Z
&sr=c&sp=rl&sig=AbCdEfGhIjKlMn...

Full URL with SAS:
https://storageaccount.blob.core.windows.net/container/file.csv?sv=2023-01-03&st=...

Real-life analogy: A SAS token is a temporary visitor badge at an office building. It lets you into specific floors (containers), during specific hours (expiry), and only lets you look at files (read permission) — not modify them. When it expires, the badge stops working.

Use when: Sharing specific files/containers with external partners, temporary access, allowing vendors to upload to a specific container Never use in: Long-term application access (use Managed Identity instead)

3. Managed Identity (Facial Recognition)

What: An Azure-managed identity automatically assigned to a service
Where: ADF, Synapse, Databricks (Access Connector), Azure Functions, VMs
Secret: NONE — Azure handles the credential lifecycle automatically
Rotate: Never — Azure rotates automatically

How it works:
  Synapse Workspace → has a system-assigned managed identity
  Azure says: "I know this Synapse workspace. Its identity is 'naveen-synapse-ws'"
  You assign: Storage Blob Data Contributor role to 'naveen-synapse-ws'
  Synapse connects: No password, no key, no connection string

Real-life analogy: Managed Identity is like facial recognition at your office. The building KNOWS your face (Azure knows the service identity). You walk up, the door opens. No badge, no key, no code. If you leave the company (service is deleted), your face is removed from the system automatically.

Use when: ALWAYS in production. Any Azure service connecting to another Azure service. This is the gold standard. No secrets to manage, no keys to rotate, no credentials to leak.

4. Service Principal (The Employee Badge)

What: An application identity (like a robot user) with a client ID + client secret
Where: Databricks (OAuth), CI/CD pipelines, external tools, multi-tenant access
Created: Azure AD → App Registrations
Components: Client ID + Client Secret (or Certificate) + Tenant ID
Rotate: Manually — secrets expire (1-2 years), must be renewed

Service Principal credentials:
  Client ID:     xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx
  Client Secret: Abc123DefGhi456JklMno789Pqr...
  Tenant ID:     xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx

Real-life analogy: A Service Principal is an employee badge for a robot. The robot (application) gets an official ID card (Client ID) and a PIN code (Client Secret). The PIN expires every year and must be renewed by HR (Azure AD). The badge grants access to specific rooms (RBAC roles) — not everything.

Use when: Databricks connecting to ADLS via OAuth, CI/CD pipelines deploying to Azure, external applications accessing Azure resources, multi-tenant scenarios Prefer Managed Identity when possible — Service Principals require secret management

5. Connection Strings (The Full Address with Directions)

What: A single string containing the server, database, authentication, and options
Where: Azure SQL, Event Hubs, Cosmos DB, Storage (in some tools)
Contains: Endpoint + authentication + database + options in one string

Azure SQL Connection String:
Server=tcp:naveen-sql-server.database.windows.net,1433;
Database=AdventureWorksLT;
User ID=sqladmin;
Password=P@ssw0rd;
Encrypt=True;
TrustServerCertificate=False;

Event Hubs Connection String:
Endpoint=sb://naveen-eventhubs.servicebus.windows.net/;
SharedAccessKeyName=RootManageSharedAccessKey;
SharedAccessKey=AbCdEfGhIjKlMn...

Real-life analogy: A connection string is like a complete address with directions: “123 Main Street (server), Apartment 4B (database), buzzer code 1234 (password), take the elevator to floor 4 (port 1433), building has security cameras (encrypt=true).”

Use when: Connecting to SQL databases (JDBC/ODBC), Event Hubs, Cosmos DB, any service that requires a structured connection specification

The Master Decision Framework

Question 1: Is this Azure service → Azure service?
  YES → Use Managed Identity (always preferred)
  NO  ↓

Question 2: Is this an application/CI/CD pipeline?
  YES → Use Service Principal
  NO  ↓

Question 3: Do you need temporary, scoped access?
  YES → Use SAS Token
  NO  ↓

Question 4: Is this quick development/testing?
  YES → Use Access Key (but NEVER commit to Git)
  NO  ↓

Question 5: Is this a database connection?
  YES → Use Connection String (ideally from Key Vault)

The hierarchy of security:

MOST SECURE:  Managed Identity (no secrets, auto-rotated)
              ↓
SECURE:       Service Principal (secret expires, must rotate)
              ↓
MODERATE:     SAS Token (expires, scoped access)
              ↓
RISKY:        Connection String in Key Vault (secret stored centrally)
              ↓
DANGEROUS:    Access Key / Connection String hardcoded (full access, never expires)

ADLS Gen2 / Blob Storage Connections

How Each Service Connects to ADLS

ADLS Endpoint Formats

Blob endpoint:   https://accountname.blob.core.windows.net/container/path
DFS endpoint:    https://accountname.dfs.core.windows.net/container/path
ABFSS (Spark):   abfss://container@accountname.dfs.core.windows.net/path
WASBS (legacy):  wasbs://container@accountname.blob.core.windows.net/path

When to use which: – abfss:// — Databricks, Synapse Spark, Fabric notebooks (always use this) – https://...blob... — REST API calls, ADF linked services, browser access – https://...dfs... — ADLS Gen2 specific REST API (hierarchical namespace) – wasbs:// — Legacy. Avoid. Use abfss:// instead.

Spark Configuration for ADLS

# Method 1: Access Key (quick dev, not production)
spark.conf.set(
    f"fs.azure.account.key.{account}.dfs.core.windows.net",
    dbutils.secrets.get("keyvault-scope", "storage-key")
)

# Method 2: Service Principal (production)
spark.conf.set(f"fs.azure.account.auth.type.{account}.dfs.core.windows.net", "OAuth")
spark.conf.set(f"fs.azure.account.oauth.provider.type.{account}.dfs.core.windows.net",
    "org.apache.hadoop.fs.azurebfs.oauth2.ClientCredsTokenProvider")
spark.conf.set(f"fs.azure.account.oauth2.client.id.{account}.dfs.core.windows.net",
    dbutils.secrets.get("keyvault-scope", "sp-client-id"))
spark.conf.set(f"fs.azure.account.oauth2.client.secret.{account}.dfs.core.windows.net",
    dbutils.secrets.get("keyvault-scope", "sp-client-secret"))
spark.conf.set(f"fs.azure.account.oauth2.client.endpoint.{account}.dfs.core.windows.net",
    f"https://login.microsoftonline.com/{tenant_id}/oauth2/token")

# Method 3: Unity Catalog (best — no config in notebooks)
# Access Connector MI + External Location = notebooks just read/write with no spark.conf

Azure SQL Database Connections

JDBC Connection String (Databricks / Spark)

jdbc:sqlserver://server-name.database.windows.net:1433;
database=AdventureWorksLT;
encrypt=true;
trustServerCertificate=false;
hostNameInCertificate=*.database.windows.net;
loginTimeout=30

How Each Service Connects to SQL

SQL Firewall Rules

Before ANY connection works, you must allow the source in SQL firewall:

SQL Server → Networking → Firewall rules:
  ☑ Allow Azure services and resources to access this server (for ADF, Synapse, Databricks)
  + Add your client IP (for local SSMS/Azure Data Studio access)

Creating SQL Users for Managed Identities

-- For Synapse Managed Identity
CREATE USER [naveen-synapse-ws] FROM EXTERNAL PROVIDER;
ALTER ROLE db_datareader ADD MEMBER [naveen-synapse-ws];
ALTER ROLE db_datawriter ADD MEMBER [naveen-synapse-ws];

-- For ADF Managed Identity
CREATE USER [naveen-adf] FROM EXTERNAL PROVIDER;
ALTER ROLE db_datareader ADD MEMBER [naveen-adf];

Azure Key Vault Connections

How Each Service Connects to Key Vault

Key Vault URI Format

https://keyvault-name.vault.azure.net/
https://keyvault-name.vault.azure.net/secrets/secret-name/version

Accessing Secrets

# Databricks (via Secret Scope)
secret = dbutils.secrets.get(scope="keyvault-scope", key="sql-password")

# Python (via Azure SDK)
from azure.identity import DefaultAzureCredential
from azure.keyvault.secrets import SecretClient
client = SecretClient(vault_url="https://kv.vault.azure.net/", credential=DefaultAzureCredential())
secret = client.get_secret("sql-password").value

# ADF (via Linked Service)
# Key Vault linked service → reference secret in other linked services

Azure Databricks Connections

How Databricks Connects to Other Services

The Databricks Connection Pattern

Step 1: Store ALL credentials in Key Vault
Step 2: Create Secret Scope pointing to Key Vault
Step 3: Read credentials in Config notebook: dbutils.secrets.get(scope, key)
Step 4: Set spark.conf or pass to JDBC — credentials never visible

Azure Data Factory / Synapse Connections

ADF and Synapse use Linked Services as their connection model. Every external resource your pipeline touches — storage, databases, Key Vault, APIs — gets a Linked Service.

Linked Service Authentication Options

The Key Vault Linked Service Pattern (Production Standard)

Instead of storing passwords directly in Linked Services:

  ❌ Bad:  ADLS Linked Service → Account Key pasted directly
  ❌ Bad:  SQL Linked Service → Password field = "P@ssw0rd"

  ✅ Good: Key Vault Linked Service (Managed Identity)
             → SQL Linked Service → Password from Key Vault secret "sql-password"
             → REST Linked Service → API Key from Key Vault secret "api-key"

  Step 1: Create Key Vault Linked Service (uses ADF Managed Identity)
  Step 2: Store all secrets in Key Vault
  Step 3: Other Linked Services reference Key Vault secrets
  Step 4: ADF never sees the actual passwords — Key Vault resolves them at runtime

  Result: One Managed Identity, one Key Vault, zero hardcoded secrets.

Managed VNet Connections

When ADF/Synapse has Managed VNet enabled, all connections go through Managed Private Endpoints — no public internet:

ADF (Managed VNet)
  ├── Managed PE → ADLS Gen2       (private, no public access)
  ├── Managed PE → Azure SQL       (private, no public access)
  ├── Managed PE → Key Vault       (private, no public access)
  └── Self-Hosted IR → On-Prem SQL (via SHIR VM in your VNet)

Without Managed VNet:
  ADF → Public internet → ADLS Gen2 public endpoint
  (works, but data travels over the internet)

For a deep dive on Managed VNet setup, see our Azure Networking post.

Microsoft Fabric / OneLake Connections

Fabric dramatically simplifies connections compared to the traditional Azure stack. Most data stays inside OneLake, so many connections disappear entirely.

The Fabric Connection Model

Traditional Azure Stack:
  ADF → Linked Service (Managed Identity) → ADLS Gen2 Linked Service → Dataset → Pipeline
  5 configuration objects to connect one pipeline to one storage account

Fabric:
  Notebook → spark.read.format("delta").load("Tables/customers")
  Zero configuration. OneLake is built in. No linked services, no datasets.

When You Still Need Connections in Fabric

OneLake Endpoint Formats

OneLake DFS endpoint:
  https://onelake.dfs.fabric.microsoft.com/{workspace-name}/{item-name}.Lakehouse/Tables/

OneLake ABFSS (from Spark):
  abfss://{workspace-name}@onelake.dfs.fabric.microsoft.com/{item-name}.Lakehouse/Tables/table

OneLake Shortcut path (inside notebook):
  Tables/shortcut_name     ← appears as a local table, data lives elsewhere

Key difference from ADLS Gen2:
  ADLS:    abfss://container@account.dfs.core.windows.net/path
  OneLake: abfss://workspace@onelake.dfs.fabric.microsoft.com/lakehouse.Lakehouse/Tables/

The Fabric simplification: In the traditional stack, you spend hours configuring Linked Services, Datasets, RBAC roles, firewall rules, and connection strings. In Fabric, the Lakehouse and Warehouse are already inside OneLake. Notebooks read from them with a one-line path. No Linked Services, no Datasets, no access keys. Connection complexity shifts from “how do I authenticate” to “where is the data.”

Azure Event Hubs / Kafka Connections

Event Hubs is Azure’s streaming data service — the entry point for real-time data. It also supports the Kafka protocol, so any Kafka client can connect to Event Hubs without code changes.

Authentication Methods

Connection Details

Event Hubs namespace endpoint:
  sb://namespace-name.servicebus.windows.net/

Connection string (from Shared access policies):
  Endpoint=sb://namespace.servicebus.windows.net/;
  SharedAccessKeyName=send-policy;
  SharedAccessKey=AbCdEfGh...;
  EntityPath=eventhub-name

RBAC roles for Managed Identity:
  Azure Event Hubs Data Sender    ← produce/send messages
  Azure Event Hubs Data Receiver  ← consume/read messages
  Azure Event Hubs Data Owner     ← full control + manage consumer groups

Connecting from Databricks (Spark Structured Streaming)

# Event Hubs connection via connection string (from Key Vault)
eh_conn_str = dbutils.secrets.get("keyvault-scope", "eh-connection-string")

eh_conf = {
    "eventhubs.connectionString": 
        sc._jvm.org.apache.spark.eventhubs.EventHubsUtils.encrypt(eh_conn_str)
}

# Read stream
df = (spark.readStream
    .format("eventhubs")
    .options(**eh_conf)
    .load()
)

# Write stream to Delta
(df.writeStream
    .format("delta")
    .outputMode("append")
    .option("checkpointLocation", "/mnt/checkpoints/eh-stream")
    .table("bronze.events")
)

Kafka Protocol on Event Hubs

Event Hubs supports Kafka protocol natively. Any Kafka client can connect:

  Bootstrap server:   namespace-name.servicebus.windows.net:9093
  Security protocol:  SASL_SSL
  SASL mechanism:     PLAIN
  SASL username:      $ConnectionString
  SASL password:      (the full Event Hubs connection string)

  This means existing Kafka producers/consumers work with Event Hubs
  without changing code — just change the bootstrap server and credentials.

For a deep dive on streaming with Event Hubs, see our Real-Time Intelligence post.

Power BI Connections

Power BI connects to data sources for building reports and dashboards. The connection method depends on the data source and refresh mode.

Connection Methods by Source

Import vs DirectQuery vs Direct Lake

Import:
  Data is COPIED into Power BI's in-memory model
  Fast queries, but data is stale until next scheduled refresh
  Best for: Small-medium datasets, ADLS Parquet, Azure SQL

DirectQuery:
  No data copied — Power BI sends SQL queries to the source LIVE
  Always fresh, but slower (every visual = a query to the source)
  Best for: Real-time dashboards, large datasets that cannot fit in memory

Direct Lake (Fabric only):
  Power BI reads Delta Parquet files DIRECTLY from OneLake
  No import, no SQL query — columnar scan on the files themselves
  Combines Import speed with DirectQuery freshness
  Best for: Fabric Lakehouse/Warehouse → Power BI reports

On-Premises Data Gateway

For on-premises data sources, Power BI requires a Data Gateway — a bridge between your corporate network and Power BI Service in the cloud:

On-Premises SQL Server
      ↓
  Data Gateway (installed on a server in your network)
      ↓ (encrypted, outbound HTTPS only)
  Power BI Service (cloud)
      ↓
  Scheduled refresh runs → Gateway queries on-prem SQL → data flows to Power BI

The Gateway is similar in concept to ADF's Self-Hosted Integration Runtime —
both act as bridges between on-premises and cloud.

Azure Data Factory / Synapse Connections

ADF and Synapse use Linked Services as their connection model. Every external resource your pipeline touches — storage, databases, Key Vault, APIs — gets a Linked Service.

Linked Service Authentication Options

The Key Vault Linked Service Pattern (Production Standard)

Instead of storing passwords directly in Linked Services:

  ❌ Bad:  ADLS Linked Service → Account Key pasted directly
  ❌ Bad:  SQL Linked Service → Password field = "P@ssw0rd"

  ✅ Good: Key Vault Linked Service (Managed Identity)
             → SQL Linked Service → Password from Key Vault secret "sql-password"
             → REST Linked Service → API Key from Key Vault secret "api-key"

  Step 1: Create Key Vault Linked Service (uses ADF Managed Identity)
  Step 2: Store all secrets in Key Vault
  Step 3: Other Linked Services reference Key Vault secrets
  Step 4: ADF never sees the actual passwords — Key Vault resolves them at runtime

  Result: One Managed Identity, one Key Vault, zero hardcoded secrets.

Managed VNet Connections

When ADF/Synapse has Managed VNet enabled, all connections go through Managed Private Endpoints — no public internet:

ADF (Managed VNet)
  ├── Managed PE → ADLS Gen2       (private, no public access)
  ├── Managed PE → Azure SQL       (private, no public access)
  ├── Managed PE → Key Vault       (private, no public access)
  └── Self-Hosted IR → On-Prem SQL (via SHIR VM in your VNet)

Without Managed VNet:
  ADF → Public internet → ADLS Gen2 public endpoint
  (works, but data travels over the internet)

For a deep dive on Managed VNet setup, see our Azure Networking post.

Microsoft Fabric / OneLake Connections

Fabric dramatically simplifies connections compared to the traditional Azure stack. Most data stays inside OneLake, so many connections disappear entirely.

The Fabric Connection Model

Traditional Azure Stack:
  ADF → Linked Service (Managed Identity) → ADLS Gen2 Linked Service → Dataset → Pipeline
  5 configuration objects to connect one pipeline to one storage account

Fabric:
  Notebook → spark.read.format("delta").load("Tables/customers")
  Zero configuration. OneLake is built in. No linked services, no datasets.

When You Still Need Connections in Fabric

OneLake Endpoint Formats

OneLake DFS endpoint:
  https://onelake.dfs.fabric.microsoft.com/{workspace-name}/{item-name}.Lakehouse/Tables/

OneLake ABFSS (from Spark):
  abfss://{workspace-name}@onelake.dfs.fabric.microsoft.com/{item-name}.Lakehouse/Tables/table

OneLake Shortcut path (inside notebook):
  Tables/shortcut_name     ← appears as a local table, data lives elsewhere

Key difference from ADLS Gen2:
  ADLS:    abfss://container@account.dfs.core.windows.net/path
  OneLake: abfss://workspace@onelake.dfs.fabric.microsoft.com/lakehouse.Lakehouse/Tables/

The Fabric simplification: In the traditional stack, you spend hours configuring Linked Services, Datasets, RBAC roles, firewall rules, and connection strings. In Fabric, the Lakehouse and Warehouse are already inside OneLake. Notebooks read from them with a one-line path. No Linked Services, no Datasets, no access keys. Connection complexity shifts from “how do I authenticate” to “where is the data.”

Azure Event Hubs / Kafka Connections

Event Hubs is Azure’s streaming data service — the entry point for real-time data. It also supports the Kafka protocol, so any Kafka client can connect to Event Hubs without code changes.

Authentication Methods

Connection Details

Event Hubs namespace endpoint:
  sb://namespace-name.servicebus.windows.net/

Connection string (from Shared access policies):
  Endpoint=sb://namespace.servicebus.windows.net/;
  SharedAccessKeyName=send-policy;
  SharedAccessKey=AbCdEfGh...;
  EntityPath=eventhub-name

RBAC roles for Managed Identity:
  Azure Event Hubs Data Sender    ← produce/send messages
  Azure Event Hubs Data Receiver  ← consume/read messages
  Azure Event Hubs Data Owner     ← full control + manage consumer groups

Connecting from Databricks (Spark Structured Streaming)

# Event Hubs connection via connection string (from Key Vault)
eh_conn_str = dbutils.secrets.get("keyvault-scope", "eh-connection-string")

eh_conf = {
    "eventhubs.connectionString": 
        sc._jvm.org.apache.spark.eventhubs.EventHubsUtils.encrypt(eh_conn_str)
}

# Read stream
df = (spark.readStream
    .format("eventhubs")
    .options(**eh_conf)
    .load()
)

# Write stream to Delta
(df.writeStream
    .format("delta")
    .outputMode("append")
    .option("checkpointLocation", "/mnt/checkpoints/eh-stream")
    .table("bronze.events")
)

Kafka Protocol on Event Hubs

Event Hubs supports Kafka protocol natively. Any Kafka client can connect:

  Bootstrap server:   namespace-name.servicebus.windows.net:9093
  Security protocol:  SASL_SSL
  SASL mechanism:     PLAIN
  SASL username:      $ConnectionString
  SASL password:      (the full Event Hubs connection string)

  This means existing Kafka producers/consumers work with Event Hubs
  without changing code — just change the bootstrap server and credentials.

For a deep dive on streaming with Event Hubs, see our Real-Time Intelligence post.

Power BI Connections

Power BI connects to data sources for building reports and dashboards. The connection method depends on the data source and refresh mode.

Connection Methods by Source

Import vs DirectQuery vs Direct Lake

Import:
  Data is COPIED into Power BI's in-memory model
  Fast queries, but data is stale until next scheduled refresh
  Best for: Small-medium datasets, ADLS Parquet, Azure SQL

DirectQuery:
  No data copied — Power BI sends SQL queries to the source LIVE
  Always fresh, but slower (every visual = a query to the source)
  Best for: Real-time dashboards, large datasets that cannot fit in memory

Direct Lake (Fabric only):
  Power BI reads Delta Parquet files DIRECTLY from OneLake
  No import, no SQL query — columnar scan on the files themselves
  Combines Import speed with DirectQuery freshness
  Best for: Fabric Lakehouse/Warehouse → Power BI reports

On-Premises Data Gateway

For on-premises data sources, Power BI requires a Data Gateway — a bridge between your corporate network and Power BI Service in the cloud:

On-Premises SQL Server
      ↓
  Data Gateway (installed on a server in your network)
      ↓ (encrypted, outbound HTTPS only)
  Power BI Service (cloud)
      ↓
  Scheduled refresh runs → Gateway queries on-prem SQL → data flows to Power BI

The Gateway is similar in concept to ADF's Self-Hosted Integration Runtime —
both act as bridges between on-premises and cloud.

The Connection Matrix: Every Source × Every Target

Endpoint Formats Reference

Connection String Formats Reference

Azure SQL (ADO.NET / pyodbc)

Server=tcp:server-name.database.windows.net,1433;
Database=AdventureWorksLT;
User ID=sqladmin;
Password={your_password};
Encrypt=True;
TrustServerCertificate=False;
Connection Timeout=30;

Azure SQL (JDBC — Databricks)

jdbc:sqlserver://server-name.database.windows.net:1433;
database=AdventureWorksLT;
encrypt=true;
trustServerCertificate=false;
hostNameInCertificate=*.database.windows.net;
loginTimeout=30

Event Hubs

Endpoint=sb://namespace.servicebus.windows.net/;
SharedAccessKeyName=RootManageSharedAccessKey;
SharedAccessKey=AbCdEfGh...;
EntityPath=eventhub-name

Cosmos DB

AccountEndpoint=https://account.documents.azure.com:443/;
AccountKey=AbCdEfGh...;

The Secure vs Quick Decision

The rule: Dev shortcuts are fine for learning. Production MUST use Managed Identity or Service Principal. Access keys and passwords should NEVER appear in code, notebooks, or Git.

Credential Rotation and Lifecycle

Best practice: Store all secrets in Key Vault. When a credential is compromised, update it in ONE place (Key Vault), and all services that reference it automatically get the new value.

Troubleshooting Connection Failures

The One-Page Cheat Sheet

CONNECTING TO ADLS GEN2:
  From ADF/Synapse:  Managed Identity + Storage Blob Data Contributor role
  From Databricks:   Secret Scope → Access Key  OR  Unity Catalog (Access Connector)
  From local:        Access Key (dev only) or SAS Token
  Spark path:        abfss://container@account.dfs.core.windows.net/path

CONNECTING TO AZURE SQL:
  From ADF/Synapse:  Managed Identity → CREATE USER [mi-name] FROM EXTERNAL PROVIDER
  From Databricks:   JDBC + SQL Auth (password from Key Vault)
  From local:        SSMS → SQL Auth or Azure AD
  JDBC URL:          jdbc:sqlserver://server.database.windows.net:1433;database=dbname
  Firewall:          Allow Azure services = YES

CONNECTING TO KEY VAULT:
  From Databricks:   Secret Scope (App ID: 2ff814a6-3304-4ab8-85cb-cd0e6f879c1d)
  From ADF/Synapse:  Key Vault Linked Service (Managed Identity)
  Role needed:       Key Vault Secrets User
  URI:               https://vault-name.vault.azure.net/

SECURITY HIERARCHY:
  BEST:    Managed Identity (no secrets)
  GOOD:    Service Principal (secret in Key Vault)
  OK:      SAS Token (temporary, scoped)
  AVOID:   Access Key / Connection String in code

Interview Questions

Q: What are the different authentication methods for connecting to ADLS Gen2? A: Five methods: Access Key (full access, risky), SAS Token (scoped, temporary), Managed Identity (Azure-managed, no secrets), Service Principal (application identity with client secret), and Unity Catalog Access Connector (Databricks-specific, uses managed identity). Production should always use Managed Identity or Service Principal. Access keys are only for quick development.

Q: What is the difference between a Managed Identity and a Service Principal? A: Both are application identities. Managed Identity is created and managed by Azure automatically — no secrets to store, rotate, or leak. Service Principal is created manually in Azure AD with a client ID and client secret that expires and must be rotated. Always prefer Managed Identity for Azure-to-Azure connections. Use Service Principal when Managed Identity is not available (CI/CD, external tools, multi-tenant).

Q: How do you securely connect Databricks to ADLS Gen2 in production? A: Three approaches in order of preference: Unity Catalog with Access Connector (best — managed identity, no config in notebooks), Service Principal via Key Vault Secret Scope (good — credentials stored securely), Access Key via Secret Scope (acceptable — key stored in Key Vault, not in code). Never hardcode access keys in notebooks.

Q: What RBAC role does ADF need to write to ADLS Gen2? A: Storage Blob Data Contributor on the storage account, assigned to the ADF managed identity. Note that the Contributor role (management plane) does NOT grant data access — you specifically need Storage Blob Data Contributor (data plane).

Q: How do you troubleshoot a 403 Forbidden error when connecting to ADLS? A: Check three things: the identity has the correct RBAC role (Storage Blob Data Contributor, not just Contributor), the role is assigned at the correct scope (storage account, not resource group), and RBAC propagation has completed (wait up to 10 minutes after role assignment). Also verify the endpoint format is correct (abfss:// for Spark, https:// for REST).

Wrapping Up

Azure connections come down to five methods: Access Keys, SAS Tokens, Managed Identities, Service Principals, and Connection Strings. The decision framework is simple: Managed Identity for Azure-to-Azure, Service Principal for applications and CI/CD, SAS Tokens for temporary external access, and connection strings stored in Key Vault for database connections.

The confusion disappears once you realize that every Azure service uses the same five methods — only the configuration syntax differs. ADLS uses spark.conf.set(). SQL uses JDBC URLs. Key Vault uses secret scope names. Databricks uses dbutils.secrets.get(). But underneath, it is always one of the five methods.

Bookmark the one-page cheat sheet and the connection matrix. You will come back to them every week.

Related posts: – Azure RBAC Roles Demystified – Databricks Secret Scopes and Key Vault – Connecting Databricks to Blob/ADLS Gen2 – Connecting Databricks to Azure SQL (JDBC) – Azure Networking (Private Endpoints)

Naveen Vuppula is a Senior Data Engineering Consultant and app developer based in Ontario, Canada. He writes about Python, SQL, AWS, Azure, and everything data engineering at DriveDataScience.com.