Search code examples
azurepowershellazure-sql-databasepowershell-coreservice-principal

Azure SQL Grant Access for AD User using PowerShell and ServicePrincipal

I try to create a User for an Azure SQL Server. I want to use the AzureAD users.

This is the code:

$accessToken = $(az account get-access-token --resource https://database.windows.net/ --query accessToken -o tsv --subscription ${data.azurerm_client_config.current.subscription_id})
$sqlConnection = New-Object System.Data.SqlClient.SqlConnection
$sqlConnection.ConnectionString = "Data Source = ${azurerm_sql_server.server.fully_qualified_domain_name}; Initial Catalog = ${azurerm_sql_database.sqldb.name}"
$sqlConnection.AccessToken = $accessToken
$sqlConnection.Open()
$sqlCmd = New-Object System.Data.SqlClient.SqlCommand
$sqlCmd.Connection = $sqlConnection
$sqlCmd.CommandText = "CREATE USER [someone.name_domain.com#EXT#@rootdomain.onmicrosoft.com] FROM EXTERNAL PROVIDER"
$sqlCmd.ExecuteNonQuery()

The azure commandline which is used to generate the token is authenticated using a service principal. The service principal is defined as the azurerm_sql_active_directory_administrator using an AAD-Group. I get the following Error

MethodInvocationException: 
Line |
  11 |  $sqlCmd.ExecuteNonQuery()
     |  ~~~~~~~~~~~~~~~~~~~~~~~~~
     | Exception calling "ExecuteNonQuery" with "0" argument(s): "Principal 'someone.name_domain.com#EXT#@rootdomain.onmicrosoft.com' could not be resolved. Error message: ''"

If I change the SqlCommand to: SELECT * FROM sys.database_principals. I can query the results. Therefore I assume the Authentication itself is not the Problem. On the other hand, when logged into the azure cli with my active directory user (which is part of the same AAD-Group) instead of the service principal, the CREATE USER ... Command works and the user is created.

I want to run the code in automation therefore I need it to work with my service principal. Is there anything that can be done to make this work?

Solution

  • Azure SQL database doesn't allow service principal to create user for now.

    But others have found some workaround to solve the issue: enter image description here

    Microsoft MSFT confirm that it's correct and the official tutorial will come soon:

    1. Assign a server identity to the Azure SQL logical server

      Set-AzSqlServer -ResourceGroupName  -ServerName <Server name> -AssignIdentity

    2. Grant the Directory Readers permissions to the server identity.

    Please reference this blogs:

    1. CREATE USER FROM EXTERNAL PROVIDER by Service Principal
    2. https://github.com/MicrosoftDocs/sql-docs/issues/4959

    Hope this helps.