Given:
- An Azure SQL Server -
MyAzureSqlServer - A Service Principal -
MyServicePrincipal - The Service Principal is configured as the AD Admin of the Azure SQL Server. (Azure Portal and Az Powershell module do not allow it, but Azure CLI and the REST API do)
I have Powershell code that runs SELECT 1 on the given database in the aforementioned Azure SQL Server:
param($db)
$AzContext = Get-AzContext # Assume this returns the Az Context for MyServicePrincipal
$TenantId = $AzContext.Tenant.Id
$ClientId = $AzContext.Account.Id
$SubscriptionId = $AzContext.Subscription.Id
$ClientSecret = $AzContext.Account.ExtendedProperties.ServicePrincipalSecret
$token = Get-AzureAuthenticationToken -TenantID $TenantId -ClientID $ClientId -ClientSecret $ClientSecret -ResourceAppIDUri "https://database.windows.net/"
Invoke-SqlQueryThruAdoNet -ConnectionString "Server=MyAzureSqlServer.database.windows.net;database=$db" -AccessToken $token -Query "SELECT 1"
Where Get-AzureAuthenticationToken is:
function Get-AzureAuthenticationToken(
[Parameter(Mandatory)][String]$TenantID,
[Parameter(Mandatory)][String]$ClientID,
[Parameter(Mandatory)][String]$ClientSecret,
[Parameter(Mandatory)][String]$ResourceAppIDUri)
{
$tokenResponse = Invoke-RestMethod -Method Post -UseBasicParsing `
-Uri "https://login.windows.net/$TenantID/oauth2/token" `
-Body @{
resource = $ResourceAppIDUri
client_id = $ClientID
grant_type = 'client_credentials'
client_secret = $ClientSecret
} -ContentType 'application/x-www-form-urlencoded'
Write-Verbose "Access token type is $($tokenResponse.token_type), expires $($tokenResponse.expires_on)"
$tokenResponse.access_token
}
And Invoke-SqlQueryThruAdoNet is:
function Invoke-SqlQueryThruAdoNet(
[parameter(Mandatory=$true)]
[ValidateNotNullOrEmpty()]
[string]$ConnectionString,
[parameter(Mandatory=$true)]
[string]$Query,
$QueryTimeout = 30,
[string]$AccessToken
)
{
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
try
{
$SqlConnection.ConnectionString = $ConnectionString
if ($AccessToken)
{
$SqlConnection.AccessToken = $AccessToken
}
$SqlConnection.Open()
$SqlCmd.CommandTimeout = $QueryTimeout
$SqlCmd.CommandText = $Query
$SqlCmd.Connection = $SqlConnection
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.SelectCommand = $SqlCmd
[void]$SqlAdapter.Fill($DataSet)
$res = $null
if ($DataSet.Tables.Count)
{
$res = $DataSet.Tables[$DataSet.Tables.Count - 1]
}
$res
}
finally
{
$SqlAdapter.Dispose()
$SqlCmd.Dispose()
$SqlConnection.Dispose()
}
}
And it works fine on any database, except on the master, for which I get:
[MyAzureSqlServer.database.windows.net\master] Login failed for user '4...1@2...b'. (SqlError 18456, LineNumber = 65536, ClientConnectionId = b8f4f657-2772-4306-b222-4533013227d1)
where 4...1 is the client Id of MyServicePrincipal and 2...b is our Azure AD Tenant Id.
So I know the access token is OK, because I can run queries on other databases. It is specifically the master that is problematic. Is there a solution for that? Of course, it must work with the Service Principal being the AD Admin.
EDIT 1
As I have mentioned there are 2 ways to configure a Service Principal to be the AD Admin:
- Using Azure CLI. It is actually straightforward:
az sql server ad-admin create --resource-group {YourAzureSqlResourceGroupName} `
--server-name {YourAzureSqlServerName} `
--display-name {ADAdminName} `
--object-id {ServicePrincipalObjectId}
The {ADAdminName} can be whatever, but we pass the display name of the Service Principal.
Now while this works, we abandoned Azure CLI in favour of Az Powershell, because the latter does not persist Service Principal credentials on disk in clear text. However, Az Powershell's function Set-AzSqlServerActiveDirectoryAdministrator does not accept a Service Principal. Yet the Azure REST API does allow it, hence we have the following custom PS function doing the job:
function Set-MyAzSqlServerActiveDirectoryAdministrator
{
[CmdLetBinding(DefaultParameterSetName = 'NoObjectId')]
param(
[Parameter(Mandatory, Position = 0)][string]$ResourceGroupName,
[Parameter(Mandatory, Position = 1)][string]$ServerName,
[Parameter(ParameterSetName = 'ObjectId', Mandatory)][ValidateNotNullOrEmpty()]$ObjectId,
[Parameter(ParameterSetName = 'ObjectId', Mandatory)][ValidateNotNullOrEmpty()]$DisplayName
)
$AzContext = Get-AzContext
if (!$AzContext)
{
throw "No Az context is found."
}
$TenantId = $AzContext.Tenant.Id
$ClientId = $AzContext.Account.Id
$SubscriptionId = $AzContext.Subscription.Id
$ClientSecret = $AzContext.Account.ExtendedProperties.ServicePrincipalSecret
if ($PsCmdlet.ParameterSetName -eq 'NoObjectId')
{
$sp = Get-AzADServicePrincipal -ApplicationId $ClientId
$DisplayName = $sp.DisplayName
$ObjectId = $sp.Id
}
$path = "/subscriptions/$SubscriptionId/resourceGroups/$ResourceGroupName/providers/Microsoft.Sql/servers/$ServerName/administrators/activeDirectory"
$apiUrl = "https://management.azure.com${path}?api-version=2014-04-01"
$jsonBody = @{
id = $path
name = 'activeDirectory'
properties = @{
administratorType = 'ActiveDirectory'
login = $DisplayName
sid = $ObjectId
tenantId = $TenantId
}
} | ConvertTo-Json -Depth 99
$token = Get-AzureAuthenticationToken -TenantID $TenantId -ClientID $ClientId -ClientSecret $ClientSecret -ResourceAppIDUri "https://management.core.windows.net/"
$headers = @{
"Authorization" = "Bearer $token"
"Content-Type" = "application/json"
}
[Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12
Invoke-RestMethod $apiUrl -Method Put -Headers $headers -Body $jsonBody
}
It uses the already familiar (see above) function Get-AzureAuthenticationToken. For our needs it sets the currently logged in Service Principal as the AD admin.
