在azuresqlserver中,作为服务主体的AD Admin能否在主数据库上运行查询?

鉴于:

1. MyAzureSqlServer
2. 服务负责人- MyServicePrincipal

我有运行的Powershell代码 SELECT 1

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"

在哪里? Get-AzureAuthenticationToken 是:

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
}

以及 Invoke-SqlQueryThruAdoNet

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()
    }
}

它在任何数据库上都能正常工作,除了在主数据库上,我得到:

[MyAzureSqlServer.database.windows.net\master]用户'4登录失败。。。1@2…b'. (SqlError 18456,行号=65536,客户端连接ID=b8f4f657-2772-4306-b222-453301327D1)

4...1 是的客户端Id 我的服务负责人 和 2...b 是我们的Azure AD租户Id。

所以我知道访问令牌是可以的,因为我可以在其他数据库上运行查询。特别是 master 这是有问题的。有解决办法吗?当然,它必须与作为广告管理员的服务主体一起工作。

• 使用Azure CLI。其实很简单:

az sql server ad-admin create --resource-group {YourAzureSqlResourceGroupName} `
        --server-name {YourAzureSqlServerName} `
        --display-name {ADAdminName} `
        --object-id {ServicePrincipalObjectId}

这个 {ADAdminName} 可以是任意的,但是我们传递服务主体的显示名称。

现在,虽然这样做可行,但我们还是放弃了azurecli,转而使用Az Powershell,因为后者不会以明文形式在磁盘上持久化服务主体凭据。但是,Az Powershell的函数 Set-AzSqlServerActiveDirectoryAdministrator 不接受服务主体。然而,Azure REST API确实允许这样做,因此我们有以下自定义PS函数来完成这项工作:

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
}

获取AzureAuthenticationToken . 为了满足我们的需要,它将当前登录的服务主体设置为AD admin。