Difficulty: Hard | Concepts: Service Principal, Azure Key Vault, Managed Identity, Azure SQL Database, Secure Authentication

"How would you connect a Fabric Pipeline to Azure SQL Database without hardcoding a password?" is a security-focused Azure interview question that separates candidates who've only ever used username/password connections from those who understand production-grade secret management.

What Is a Service Principal, and Why Not Just Use a SQL Login?

A Service Principal is an identity in Azure Active Directory (Entra ID) created specifically for an application or automated process — not a human user — to authenticate and access Azure resources. Instead of storing a SQL username and plaintext password inside a pipeline (which is a security risk if the pipeline definition is ever exported, shared, or committed to source control), you register a Service Principal, grant it access to the Azure SQL Database, and store its credential (a client secret or certificate) in Azure Key Vault — a managed, encrypted secret store. The pipeline then only needs a reference to the Key Vault secret, never the secret's actual value in plain text. This separates who can run the pipeline from what credential the pipeline uses, and lets you rotate the secret centrally without touching the pipeline itself.

Scenario

You need a Fabric Pipeline to pull nightly data from an Azure SQL Database. Security policy prohibits storing any password directly inside the pipeline or notebook code.

Setup Steps

Register a Service Principal (App Registration) in Entra ID; note its Application (client) ID and Tenant ID.
Generate a client secret for the Service Principal in Entra ID.
In Azure Key Vault, create a secret (e.g., sql-sp-secret) and store the client secret's value there.

In Azure SQL Database, run:

CREATE USER [fabric-pipeline-sp] FROM EXTERNAL PROVIDER;
ALTER ROLE db_datareader ADD MEMBER [fabric-pipeline-sp];

In the Fabric Pipeline's connection, choose Service Principal as the authentication type, supply the Client ID and Tenant ID, and point the secret field to the Key Vault reference instead of typing a value directly.

Authentication Flow

Component	Role
Service Principal	The non-human identity the pipeline authenticates as
Azure Key Vault	Stores the Service Principal's secret, encrypted and access-controlled
Azure SQL external provider user	Maps the Service Principal identity to a database-level role with least-privilege access
Fabric connection	References the Key Vault secret by name — never stores the raw value

Visual Representation

Entra ID: Service Principal "fabric-pipeline-sp" registered | Azure Key Vault: secret "sql-sp-secret" stores the SP's client secret | Azure SQL: CREATE USER [fabric-pipeline-sp] FROM EXTERNAL PROVIDER granted db_datareader only (least privilege) | Fabric Pipeline connection: auth = Service Principal client secret = Key Vault reference (not plaintext) | Pipeline runs nightly -> authenticates as SP -> reads via db_datareader role

Common Mistake to Avoid

A frequent mistake is granting the Service Principal db_owner or broader permissions "just to make it work faster" during testing, and then forgetting to scope it down before production. Always apply least privilege — typically db_datareader for ingestion-only pipelines — and grant write access only to the specific tables that truly need it.

Conclusion

The strong interview answer highlights three layers working together: a Service Principal as the non-human identity, Key Vault for secret storage and rotation, and least-privilege database roles — not just "I'd use a Service Principal" without explaining how the secret itself stays out of the pipeline definition.