LLI Wiki — My Digital Knowledge Base

Entra ID authentication to Azure SQL from PowerShell

Access tokens, managed identities and service principals: which identity for which job, and least privilege in the database.

SQL passwords in configuration files belong to the past. With Azure SQL, a PowerShell script authenticates against Microsoft Entra ID, gets an access token and presents it to the database. With a managed identity there is no secret left to store. Here is the mechanism, the identity types and how to choose.

The principle: a token for the Azure SQL resource

Entra ID issues an OAuth 2.0 token whose audience (the resource) is https://database.windows.net/. The client hands it to SQL (the connection's AccessToken), the database validates it and maps the identity to a database user.

sequenceDiagram
    participant S as "PowerShell script"
    participant M as "Identity endpoint (IMDS or Entra ID)"
    participant Q as "Azure SQL"
    S->>M: "Request a token (resource database.windows.net)"
    M-->>S: "Access token (short lived)"
    S->>Q: "SQL connection with the token"
    Q->>Q: "Validate token, map the database user"
    Q-->>S: "Session opened with that user's rights"

Getting a token

From a workstation or a session already signed in with the Az module:

$t = Get-AzAccessToken -ResourceUrl 'https://database.windows.net/'
# Depending on the module version, Token is a SecureString
$token = if ($t.Token -is [securestring]) { $t.Token | ConvertFrom-SecureString -AsPlainText } else { $t.Token }

$cn = [Microsoft.Data.SqlClient.SqlConnection]::new('Server=tcp:sql-contoso-prd.database.windows.net,1433;Database=AppDb;Encrypt=True')
$cn.AccessToken = $token
$cn.Open()
$cmd = $cn.CreateCommand(); $cmd.CommandText = 'SELECT SUSER_SNAME()'
$cmd.ExecuteScalar()
$cn.Close()

On an Azure machine with a managed identity there are two routes: Connect-AzAccount -Identity (add -AccountId <clientId> for a user-assigned identity) then Get-AzAccessToken, or call the metadata endpoint (IMDS) directly:

$uri = 'http://169.254.169.254/metadata/identity/oauth2/token?api-version=2018-02-01&resource=https://database.windows.net/&client_id=00000000-0000-0000-0000-000000000000'
(Invoke-RestMethod -Uri $uri -Headers @{ Metadata = 'true' }).access_token

The client_id parameter is only needed to pick a user-assigned identity. Using Authentication=Active Directory Managed Identity in the Microsoft.Data.SqlClient connection string does the same without writing token code.

Which identity for which job

Identity Secret to manage Use for
Your own account (interactive) no (MFA) manual work, troubleshooting
System-assigned managed identity no a single resource (a VM, a function) that owns the identity
User-assigned managed identity no several resources sharing rights: Batch pool, agents, a management session
Service principal (secret or certificate) yes outside Azure: pipeline without federation, third-party tool
Pipeline service connection (OIDC federation) no running inside an Azure DevOps pipeline

My decision tree: if it runs on Azure, managed identity; otherwise federation when possible; service principal with a secret as a last resort, secret in a vault, rotation scheduled.

To detect the run context, my script looks at system environment variables (TF_BUILD in an Azure DevOps pipeline, AZ_BATCH_NODE_ID on a Batch node) and picks the identity from the configuration accordingly.

Granting rights: least privilege

On the database side the identity becomes a contained user, with no server-level login:

CREATE USER [uami-dba-batch] FROM EXTERNAL PROVIDER;
ALTER ROLE db_datareader ADD MEMBER [uami-dba-batch];
GRANT EXECUTE ON SCHEMA::dba TO [uami-dba-batch];

Rules I apply:

Lessons learned

Mastodon LLIMastodon SocialFosstodon