How to configure access of a Azure App Service Web App to Azure SQL DB?

Question

I'm trying out Azure. I'm hosting a C# .NET Core 8 MVC Web Application as a Azure App Service WebApp (Free Plan) and it should access a Azure SQL Server Database (Free Plan).

When the WebApp was created a User Managed Service Identity was created as well.

This UMSI has successful access to the Azure Key Vault by assigning it to the Role "Key Vault Secrets User".
UMSI is failing to access the SQL Server Database and I am not sure how to configure the access.I only found the Role "SQL Server Contributor" but nothing for the access to the database itself.

When the WepApp tries to access the Database im getting a Error as below

SqlException: ManagedIdentityCredential authentication failed: Service request failed. Status: 400 (Bad Request)

I do have the connection string in the app settings defined as below

"Server=tcp:sqlserveraddress.database.windows.net,1433;Initial Catalog=SQLDBName;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;Authentication="Active Directory Default";"

Does anyone know how to grant the User Managed Service Identity access to the database itself? The Database doesn't have a IAM Screen.

Can't believe I do need to use TSQL to administrate add the USMI as a user and login with permissions?

EDIT: I've created a user for the UMSI and granted it db_datareader, db_datawriter, db_ddladmin as the App has to create tables from the migration. Didnt help. Error is still the same.

I tried this, adding the UMSI through create user but it doesn't change anything. — Tim
– Tim, Commented Feb 8, 2024 at 12:22
How are you specifying the managed identity to use in the SQL connection string? — juunas
– juunas, Commented Feb 8, 2024 at 12:28
I do have the connection string in the app settings "Server=tcp:sqlserveraddress.database.windows.net,1433;Initial Catalog=SQLDBName;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;Authentication="Active Directory Default";" — Tim
– Tim, Commented Feb 8, 2024 at 12:29

Thiago Custodio · Accepted Answer · 2024-02-08 17:09:10Z

0

Based on your comments, you already added the managed identity user as external login.

Besides that, double check the connection string. It should be as following:

Server=tcp:[server-name].database.windows.net,1433;Initial Catalog=[catalog];Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;

Also, assuming you're using Entity Framework, add this to your DbContext:

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
    var connectionString = _configuration.GetConnectionString("Default");
    var sqlConnection = new SqlConnection(connectionString);

    var tokenCredential = new DefaultAzureCredential();
    sqlConnection.AccessToken = tokenCredential.GetToken(new TokenRequestContext(new[] { "https://database.windows.net/.default" })).Token;


    optionsBuilder.UseSqlServer(sqlConnection);
}

answered Feb 8, 2024 at 17:09

Thiago Custodio

18.4k9 gold badges50 silver badges99 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Tim Over a year ago

Thanks, there is no OnConfiguring in the .NET 8 - I only have a Program.cs left. Startup.cs is gone. I was lacking a System Managed Identity. Why do you need the AccessToken Credential?

Thiago Custodio Over a year ago

this is how I've been doing. Managed identity will get an access token from Entra (Azure AD), which will be used to authenticate the operation.

Tim Over a year ago

Thanks. Somehow with .NET 8, System Managed Identity + SQL create user / grant db_reader it is now working, at least im getting to the point where its offering the "Apply Migration" button. Now I am struggeling to run the ef database migration on azure itself.

Tim · Accepted Answer · 2024-02-08 17:16:33Z

0

Thanks to Bhavani for referring me to this

I had to enable and create a System Managed Identity. Only the System Managed Identity is then created within Entra / ADD. After this the Application was able to access the Database. The User Managed Identity is not created in Entra / ADD.

answered Feb 8, 2024 at 17:16

Tim

1,3095 gold badges24 silver badges43 bronze badges

Collectives™ on Stack Overflow

How to configure access of a Azure App Service Web App to Azure SQL DB?

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related