LLI Wiki — My Digital Knowledge Base

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

Mastodon LLIMastodon SocialFosstodon