azure - 在 Azure SQL Server 中，作为服务主体的 AD 管理员是否可以在主数据库上运行查询？

Question

给定:

1. Azure SQL Server - MyAzureSqlServer
2. 服务主体 - MyServicePrincipal
3. 服务主体配置为 Azure SQL Server 的 AD 管理员。 (Azure Portal 和 Az Powershell 模块不允许，但 Azure CLI 和 REST API 允许)

我有 Powershell 代码，可以在上述 Azure SQL Server 中的给定数据库上运行 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] Login failed for user '[email protected]'. (SqlError 18456, LineNumber = 65536, ClientConnectionId = b8f4f657-2772-4306-b222-4533013227d1)

其中 4...1 是 MyServicePrincipal 的客户端 ID，2...b 是我们的 Azure AD 租户 ID。

所以我知道访问 token 没问题，因为我可以在其他数据库上运行查询。特别是 master 有问题。有解决办法吗？当然，它必须与作为 AD 管理员的服务主体一起使用。

编辑 1

正如我所提到的，有两种方法可以将服务主体配置为 AD 管理员:

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

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

{ADAdminName} 可以是任何内容，但我们传递服务主体的显示名称。

现在，虽然这可行，但我们放弃了 Azure CLI，转而使用 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
}

它使用已经熟悉的(见上文)函数Get-AzureAuthenticationToken。为了满足我们的需求，它将当前登录的服务主体设置为 AD 管理员。

Answer 1

根据我的测试，当我们直接将Azure服务主体设置为Azure SQL AD管理员时，会导致一些问题。我们无法使用服务主体登录 master 数据库。因为 Azure AD 管理员登录名应该是 Azure AD 用户或 Azure AD 组。更多详情请引用document

因此，如果要将 Azure 服务主体设置为 Azure SQL AD 管理员，我们需要创建一个 Azure AD 安全组，添加服务主体作为该组的成员，然后将 Azure AD 组设置为 Azure SQL AD 管理员。

例如

1. 配置 Azure AD 管理员

Connect-AzAccount

$group=New-AzADGroup -DisplayName SQLADADmin -MailNickname SQLADADmin 

$sp=Get-AzADServicePrincipal -DisplayName "TodoListService-OBO-sample-v2"

Add-AzADGroupMember -MemberObjectId $sp.Id -TargetGroupObjectId $group.id

$sp=Get-AzADServicePrincipal -DisplayName "<your sq name>"

Remove-AzSqlServerActiveDirectoryAdministrator -ResourceGroupName "<>" -ServerName "<>" -force

Set-AzSqlServerActiveDirectoryAdministrator -ResourceGroupName "<>" -ServerName "<>" -DisplayName $group.DisplayName -ObjectId $group.id

查询

$appId = "<your sp app id>"
$password = "<your sp password>"
$secpasswd = ConvertTo-SecureString $password -AsPlainText -Force
$mycreds = New-Object System.Management.Automation.PSCredential ($appId, $secpasswd)
Connect-AzAccount -ServicePrincipal -Credential $mycreds -Tenant "<your AD tenant id>"
#get token
$context =Get-AzContext
$dexResourceUrl='https://database.windows.net/'
$token = [Microsoft.Azure.Commands.Common.Authentication.AzureSession]::Instance.AuthenticationFactory.Authenticate($context.Account, 
                                $context.Environment, 
                                $context.Tenant.Id.ToString(),
                                 $null, 
                                 [Microsoft.Azure.Commands.Common.Authentication.ShowDialog]::Never, 
                                 $null, $dexResourceUrl).AccessToken

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection                
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$ConnectionString="Data Source=testsql08.database.windows.net; Initial Catalog=master;"

# query the current database name
$Query="SELECT DB_NAME()"

    try 
    {
        $SqlConnection.ConnectionString = $ConnectionString
        if ($token)
        {
            $SqlConnection.AccessToken = $token
        }
        $SqlConnection.Open()

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