Azure SQL: connecting with Invoke-Sqlcmd via managed identity or service principal
Examples of connecting to Azure SQL Database with Invoke-Sqlcmd and sqlcmd, using managed identity, service principal or interactive Azure AD.
Examples of connecting to Azure SQL Database from Invoke-Sqlcmd (PowerShell) and sqlcmd, using managed identity (MSI) or Azure AD service principal authentication.
Invoke-Sqlcmd with a managed identity token
Get an access token from the instance metadata endpoint (user-assigned managed identity) and use it with Invoke-Sqlcmd:
$AppID = "https://database.windows.net"
$UamiID = "00000000-0000-0000-0000-000000000000"
$metadataUri = "http://169.254.169.254/metadata/identity/oauth2/token?api-version=2018-02-01&resource=$AppID&object_id=$UamiID"
$request = Invoke-RestMethod -Uri $metadataUri -Headers @{Metadata = "true" }
$AccessToken = $request.access_token
$myArguments = @{
AccessToken = $AccessToken
ServerInstance = "myserver.database.windows.net"
Database = "mydatabase"
}
$myArguments.Query = "EXEC [dbo].[sp_ExportViews] @Destination = N'MSSQL', @Create_Drop = N'Y'"
Invoke-Sqlcmd @myArguments -Verbose 4>&1
sqlcmd with an Azure AD service principal
The modern sqlcmd client (e.g. v1.8.2) supports ActiveDirectoryServicePrincipal authentication: the service principal's application id, followed by @ and the tenant id, is used as the username (-U); the service principal's secret is used as the password (-P).
sqlcmd --authentication-method ActiveDirectoryServicePrincipal ^
-S myserver.database.windows.net ^
-d mydatabase ^
-U <app-id>@<tenant-id> ^
-P "<PASSWORD>"
Check the installed version:
sqlcmd --version
Check the identity and database in use:
sqlcmd -S myserver.database.windows.net -d master -C -Q "SELECT SUSER_SNAME(), DB_NAME()" --authentication-method ActiveDirectoryServicePrincipal
Interactive sqlcmd (Azure AD user)
The -G option enables interactive Azure AD (browser) authentication; -U then specifies the Azure AD username:
sqlcmd -S myserver.database.windows.net -d master -C -Q "SELECT SUSER_SNAME(), DB_NAME()" -G -U user@example.com
Notes
- Prefer a managed identity when the script runs on an Azure resource that supports it (VM, App Service, Azure Automation...) rather than managing a service principal secret.
- Never store a service principal secret in plain text in a script: use a secret vault (Azure Key Vault) or a protected environment variable.
- The format
Data Source=<server>;Initial Catalog=<database>;is a standard ADO.NET connection string for Azure SQL.