4

I want to add a managed identity (coming from an App Service) to Azure SQL Server.

I created an AAD group where a group of my team and the Service Principal is part of.

AzureSqlAdminGroup = TeamGroup + Service Principal

This AAD group is added as an Azure SQL admin during the provisioning of the Azure SQL Server.

When I run CreateSqlUserFromManagedIdentity under my personal account everything works fine. Whereas when I run the code under a service principal, SQL Server tells me that it can not resolve the managed identity of my app service and that the service principal doesn't have the permissions to do so.

System.Data.SqlClient.SqlException (0x80131904): Principal 'xyz' could not be resolved. Error message: ''
2020-06-10T16:34:12.6605990Z Cannot add the principal 'xyz', because it does not exist or you do not have permission.
2020-06-10T16:34:12.6606728Z Cannot add the principal 'xyz', because it does not exist or you do not have permission.
2020-06-10T16:34:12.6607420Z Cannot add the principal 'xyz', because it does not exist or you do not have permission.

Code:

public async Task CreateSqlUserFromManagedIdentity(string managedIdentityName, params string[] roles)
{
    var credential = new DefaultAzureCredential(new DefaultAzureCredentialOptions
    {
        ExcludeVisualStudioCredential = true,
        ExcludeVisualStudioCodeCredential = true
    });

    var accessToken = await credential.GetTokenAsync(new TokenRequestContext(new[] { "https://database.windows.net/.default" }));

    var sqlConnectionStringBuilder = new SqlConnectionStringBuilder(_connectionString);
    var stringBuilder = new StringBuilder();

    stringBuilder.AppendLine($"IF DATABASE_PRINCIPAL_ID('{managedIdentityName}') IS NULL");
    stringBuilder.AppendLine("BEGIN");
    stringBuilder.AppendLine($"\tCREATE USER [{managedIdentityName}] FROM EXTERNAL PROVIDER;");
    stringBuilder.AppendLine("END");
    Console.WriteLine($"Adding Managed Identity '{managedIdentityName}' to '{sqlConnectionStringBuilder.DataSource}\\{sqlConnectionStringBuilder.InitialCatalog}' with roles ...");

    foreach (var role in roles)
    {
        Console.WriteLine($"\t{role}");
        stringBuilder.AppendLine($"ALTER ROLE {role} ADD MEMBER [{managedIdentityName}];");
    }

    await using var sqlConnection = new SqlConnection(_connectionString) { AccessToken = accessToken.Token };
    await sqlConnection.OpenAsync();

    var sqlCommand = sqlConnection.CreateCommand();
    sqlCommand.CommandText = stringBuilder.ToString();
    await sqlCommand.ExecuteNonQueryAsync();
    ConsoleEx.WriteSuccessLine("successfully");
}

How can I add a Managed Identity to Azure SQL Server when running under a Service Principal?

Clarification:

xyz is the Managed Identity I want to add as a user in Azure SQL. I am running the code under a service principal (which fails).

Rookian
  • 19,841
  • 28
  • 110
  • 180
  • I am needing some clarification. Is `xyz` the Managed Identity you are attempting to add? Or is `xyz` the service principle you are running the script as (CreateSqlUserFromManagedIdentity)? It appears the service principle you are running the script as is part of the SQL AD Admin group, is that correct? I think that your issue is `xyz` does not exist in your directory, and when you go to perform the check if exists, you are checking the database but not the directory (external provider) the AAD authentication directory configured for the this Azure SQL Database. – Mike Ubezzi Jun 19 '20 at 17:38
  • Or the service principle running the script needs some privileges on the directory but, I do see the following: "To create an Azure AD-based contained database user (other than the server administrator that owns the database), connect to the database with an Azure AD identity, as a user with at least the `ALTER ANY USER` permission. Then use the following Transact-SQL syntax: `CREATE USER FROM EXTERNAL PROVIDER;` – Mike Ubezzi Jun 19 '20 at 17:43
  • Finally, adding this troubleshooting guide: https://techcommunity.microsoft.com/t5/azure-sql-database/troubleshooting-problems-related-to-azure-ad-authentication-with/ba-p/1062991 – Mike Ubezzi Jun 19 '20 at 17:50
  • @MikeUbezziMSFT added clarification section in my question. As far as I can say, it is not supported to run the code under a service principal. It would be nice to have an official statement from Microsoft about this missing feature. It is only possible to run under a user principal or give Azure SQL Server itself a Managed Identity with Directory Readers role. – Rookian Jun 23 '20 at 09:37
  • @MikeUbezziMSFT We could find a way see here: https://github.com/MicrosoftDocs/sql-docs/issues/2323#issuecomment-652907219 – Rookian Jul 02 '20 at 09:52
  • The service principal executing the command needs the Azure AD role "Directory Readers" to be able to retrieve the `xyz` managed identity information. Let me know if that works for you. – Thomas Mar 02 '22 at 01:17

0 Answers0