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:
- one identity per kind of job (read-only report, maintenance, collection), not one universal identity;
db_datareaderrather thandb_owner,EXECUTEon a specific schema rather than on the database;- administrative rights (creating users,
ALTER ANY) stay with the identity that needs them, never the reporting one; - create the user from an Entra connection, because
FROM EXTERNAL PROVIDERrequires the caller itself to be authenticated through Entra ID.
Lessons learned
- Which id to pass varies: the token API expects the identity's client id, while SQL and RBAC see its object id. Mixing them gives misleading errors.
- "Login failed for user ''": the identity gets a token but does not exist as a user in that database. Test the token and a
SELECT, database by database. - Several identities on the same machine: without
client_idthe IMDS returns an error; always say which one. - Tokens expire (about an hour): request a new one before each long operation, do not cache it forever.
- Never log a token, not even partially.