Principal 'xyz' could not be resolved: how can I add a managed identity to Azure SQL Server when running under a Service Principal?

0

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).

sql-server
azure-active-directory
azure-sql-server
azure-managed-identity
asked on Stack Overflow Jun 15, 2020 by Rookian • edited Jun 23, 2020 by Rookian

0 Answers

Nobody has answered this question yet.


User contributions licensed under CC BY-SA 3.0