Azure SQL Grant Access for AD User using PowerShell and ServicePrincipal

Question

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?

Hi Console, Did you solved it now? If my answer is helpful for you, hope you can accept(mark) it as answer. This can be beneficial to other community members. Thank you. — Leon Yue
Hi @LeonYue I cannot grant the said permissions, therefore I am not able to validate if your approach works. — quadroid

Leon Yue Leon Yue · Accepted Answer · 2020-07-02T02:01:34

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

But others have found some workaround to solve the issue:

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

Assign a server identity to the Azure SQL logical server

Set-AzSqlServer -ResourceGroupName  -ServerName <Server name> -AssignIdentity
Grant the Directory Readers permissions to the server identity.

Please reference this blogs:

Hope this helps.

Azure SQL Grant Access for AD User using PowerShell and ServicePrincipal

1 Answers