azure - 使用 PowerShell 添加 Azure AD 帐户作为 SQL 登录失败

Question

我们正在尝试自动添加 Azure AD 组作为 SQL 登录名到 Azure SQL 实例上运行的特定数据库。使用 SSMS 登录 SQL 实例并运行命令 CREATE USER [<aad_group_to_add>] FROM EXTERNAL PROVIDER 时它执行没有问题，添加帐户也没有问题。

我们正在使用的脚本:

Function Get-AADToken {
  [CmdletBinding()]
  [OutputType([string])]
  PARAM (
      [String]$TenantID,
      [string]$ServicePrincipalId,
      [securestring]$ServicePrincipalPwd
  )
  Try {
      # Set Resource URI to Azure Database
      $resourceAppIdURI = 'https://database.usgovcloudapi.net/'

      # Set Authority to Azure AD Tenant
      $authority = 'https://login.microsoftonline.us/' + $TenantID
      $ClientCred = [Microsoft.IdentityModel.Clients.ActiveDirectory.ClientCredential]::new($ServicePrincipalId, $ServicePrincipalPwd)
      $authContext = [Microsoft.IdentityModel.Clients.ActiveDirectory.AuthenticationContext]::new($authority)
      $authResult = $authContext.AcquireTokenAsync($resourceAppIdURI, $ClientCred)
      $Token = $authResult.Result.AccessToken
  }
  Catch {
      Throw $_
      $ErrorMessage = 'Failed to aquire Azure AD token.'
      Write-Error -Message 'Failed to aquire Azure AD token'
  }
  $Token
}

# Variables
$tenantId = '<tenant_id>'
$subscription_Id = '<subscription_id>'
$kvName = "mykv"
$kvSecret = "mysppw"
$spDisplayName = "mysp"
$environmentName = "AzureUsGovernment"

# Login to Azure Resource Management portal
Write-Host "Checking context...";
$context = Get-AzureRmContext
if($null -ne $context){
  if(!(($context.Subscription.TenantId -match $tenant_Id) -and ($context.Subscription.Id -match $subscription_Id))){
    do{
      Remove-AzureRmAccount -ErrorAction SilentlyContinue | Out-Null
      $context = Get-AzureRmContext
      }
    until($null -ne $context)
    Login-AzureRmAccount -EnvironmentName $environmentName -TenantId $tenantId -Subscription $subscription_Id
    }
  }
else{
  Login-AzureRmAccount -EnvironmentName $environmentName -TenantId $tenantId -Subscription $subscription_Id
  }

# Connect to db using specific SQL SP Account "oca-inl-sql-sp1"
$ServicePrincipalId = (Get-AzureRmADServicePrincipal -DisplayName 
$spDisplayName).ApplicationId.Guid
$sql_sp_secret = (Get-AzureKeyVaultSecret -VaultName $kvName -Name 
$kvSecret).SecretValueText
$SecureStringPassword = ConvertTo-SecureString -AsPlainText $sql_sp_secret -Force

# Run the Function to get the AD Token for the sql sp
Get-AADToken -TenantID $TenantID -ServicePrincipalId $ServicePrincipalId - ServicePrincipalPwd $SecureStringPassword -OutVariable SPNToken

# Create connection to sql server
Write-Verbose "Create SQL connectionstring"
$conn = New-Object System.Data.SqlClient.SQLConnection
$SQLServerName = "mysqlsrv"
$DatabaseName = "mydb"
$conn.ConnectionString = "Data 
Source=$SQLServerName.database.usgovcloudapi.net;Initial Catalog=$DatabaseName;Connect Timeout=30"
$conn.AccessToken = $($SPNToken)
$conn

# Create the T-SQL Querys to be executing inside of the sql connection
Write-Verbose "Connect to database and execute SQL script"
$conn.Open()

$query = "CREATE USER [<aad_group_to_add>] FROM EXTERNAL PROVIDER"

# Execute the queries using the connection created previously
$command = New-Object -TypeName System.Data.SqlClient.SqlCommand($query, $conn)     
$Result = $command.ExecuteScalar()
$Result
$conn.Close() 

运行上面的脚本时，我们收到错误 Exception calling "ExecuteScalar" with "0" argument(s): "Principal '<aad_group_to_add>' could not be found at this time

Answer 1

我们遇到了类似的问题，发现我们必须使用 AAD 用户名和密码以类似的方式添加组。我使用与您类似的代码尝试了一切可能的方法，并决定将此 powershell 作为我们 CI/CD 管道的一部分:

 $query ='
  CREATE USER [GROUPTOADD] FROM EXTERNAL PROVIDER
  ALTER ROLE db_datawriter ADD MEMBER [GROUPTOADD]
  ALTER ROLE db_datareader ADD MEMBER [GROUPTOADD]  
  GRANT CONNECT TO [GROUPTOADD]
  GRANT EXECUTE TO [GROUPTOADD]
'

  $con = "Data Source=$(DatabaseServer);Initial Catalog=$(DatabaseName);User ID=$(ADDatabaseUser);Password='$(ADDbPwd)';Connect Timeout=30;Encrypt=False;Authentication='Active Directory Password'"
Invoke-Sqlcmd -Query $query -ConnectionString $con