Connect to SQL Database from App Service by using a managed identity – System-assigned.

Running App Service with Azure SQL based

Fix list:
The steps in this guide has been tested with “.NET 6.0”

dotnet tool install -g dotnet-ef
dotnet ef migrations add InitialCreate
dotnet ef database update
dotnet add package Microsoft.Data.SqlClient --version 4.0.1
dotnet add package Azure.Identity --version 1.5.0

appsettings.json :

{
  "Logging": {
    "LogLevel": {
      "Default": "Information"
    }
  },
  "AllowedHosts": "*",
  "ConnectionStrings": {
    "CoredBConnection": "Server=tcp:mydatabase-server-sqlsrv.database.windows.net;Authentication=Active Directory Default; Database=CoredB;"
  }
} 
az webapp identity assign --resource-group my-002-rg --name webapp-core-sql-018
CREATE USER [webapp-core-sql-018] FROM EXTERNAL PROVIDER;
ALTER ROLE db_datareader ADD MEMBER [webapp-core-sql-018];
ALTER ROLE db_datawriter ADD MEMBER [webapp-core-sql-018];
ALTER ROLE db_ddladmin ADD MEMBER [webapp-core-sql-018];
GO

Program.cs :

public void ConfigureServices(IServiceCollection services)
{
   services.AddControllersWithViews();
   services.AddDbContext<MyDatabaseContext>(options =>
            options.UseSqlServer(Configuration.GetConnectionString("CoredBConnection")));
}

See Also:

Web App & Azure SQL Templates:

  • https://docs.microsoft.com/en-us/azure/azure-sql/database/arm-templates-content-guide?tabs=single-database
  • https://docs.microsoft.com/en-us/azure/app-service/samples-resource-manager-templates
  • https://azure.microsoft.com/en-au/resources/templates/web-app-sql-database/

Comments

comments

About fujute

Fukiat Julnual's role as Technical Evangelist is to support awareness for Microsoft Azure , engages with Technical Community and help IT Professionals to discover the technical value of innovations in Microsoft platform.