Edit

Share via

Tutorial: Deploy an ASP.NET app with Azure SQL database to Azure

Azure App Service provides a highly scalable, self-patching web hosting service. This tutorial shows you how to deploy a data-driven ASP.NET app in App Service and connect it to Azure SQL Database.

When you finish the tutorial, you have an ASP.NET app connected to an Azure SQL database running in Azure. The following example shows the app interface.

Screenshot that shows a published ASP.NET application in Azure App Service.

In this tutorial, you:

  • Publish a data-driven web app to Azure.
  • Create an Azure SQL database to hold the app data.
  • Connect the ASP.NET app to the Azure SQL database.
  • Configure a managed identity and Microsoft Entra ID authentication for the database connection.
  • Update the data model and redeploy the app.
  • Stream application logs from Azure to Visual Studio.

Prerequisites

  • If you don't have an Azure account, create a free account before you begin.
  • Install Visual Studio 2022 with the ASP.NET and web development and Azure development workloads.
    • You can add the workloads to an existing Visual Studio installation by selecting Get Tools and Features in the Visual Studio Tools menu.
    • Make sure you have the latest updates for Visual Studio 2022 by selecting Help > Check for updates and installing the latest version if necessary.

Create and run the app

The sample project contains a basic ASP.NET MVC create-read-update-delete (CRUD) app that uses Entity Framework Code First.

  1. Download the sample project and extract the dotnet-sqldb-tutorial-master.zip file.

  2. Open the extracted dotnet-sqldb-tutorial-master/DotNetAppSqlDb.sln file in Visual Studio.

  3. Press F5 to run the app and open it in your default browser.

    Note

    If necessary, install any missing NuGet packages.

  4. In the app, select Create New and create a couple of to-do items.

    Screenshot that shows the ASP.NET web app.

  5. Test the Edit, Details, and Delete links.

Publish the app to Azure

To publish the app to Azure, you create and configure a Publish profile that has an Azure App Service and App Service Plan to host the app. You then create an Azure SQL Server and Azure SQL database to contain the app data, and configure a database context to connect the app with the database.

  1. In Visual Studio Solution Explorer, right-click the DotNetAppSqlDb project and select Publish.

    Screenshot that shows Publish from Solution Explorer.

  2. On the Publish screen, select Azure as your target and select Next.

  3. On the next screen, make sure that Azure App Service (Windows) is selected and select Next.

Sign in and add an Azure App Service

  1. On the next Publish screen, sign in to your Microsoft account and the subscription you want to use.

  2. Next to App Service, select Create new.

    Screenshot that shows selecting Create new for App Service in the Publish pane.

Configure the Azure App Service

  1. On the App Service (Windows) screen, configure the App Service Name, Resource group, and Hosting Plan.

    Screenshot that shows creating an App Service plan.

  2. Under Name, you can keep the generated web app name, or change it to another name with characters a-z, 0-9, and -. The web app name must be unique across all Azure apps.

  3. Next to Resource group, select New, and name the resource group myResourceGroup.

    A resource group is a logical container into which Azure resources, such as web apps, databases, and storage accounts, are deployed and managed. For example, you can choose to delete the entire resource group in one simple step later.

  4. Next to Hosting Plan, select New.

    An App Service plan specifies the location, size, and features of the web server farm that hosts your app. You can save money when you host multiple apps by configuring the web apps to share a single App Service plan.

    App Service plans define:

    • Region (for example: North Europe, East US, or Southeast Asia)
    • Instance size (small, medium, or large)
    • Scale count (1 to 20 instances)
    • SKU (Free, Shared, Basic, Standard, or Premium)

  5. Complete the Hosting Plan screen, and then select OK.

    Setting Suggested value For more information
    App Service Plan myAppServicePlan App Service plans
    Location East US Azure regions
    Size Free Pricing tiers

    Screenshot that shows creating the Hosting Plan.

  6. On the App Service (Windows) screen, select Create, and wait for the Azure resources to be created.

  7. The Publish screen shows the resources you configured. Select Finish, and then select Close.

    Screenshot that shows the resources you created.

Create a server and database

Before you can create a database, you need a logical SQL server. A logical SQL server is a logical construct that contains a group of databases managed as a group.

  1. On the Publish screen for the DotNetAppSqlDb app, in the Service Dependencies section, select the ellipsis ... next to SQL Server Database, and select Connect.

    Note

    Be sure to configure the SQL Database from the Publish tab, not the Connected Services tab.

    Screenshot that shows configuring the SQL Database dependency.

  2. On the Connect to dependency screen, select Azure SQL Database and then select Next.

  3. On the Configure Azure SQL Database screen, select Create new.

  4. On the Azure SQL Database screen, next to Database server, select New.

  5. Change the server name to a value you want. The server name must be unique across all servers in Azure SQL.

  6. Select OK.

    Screenshot that shows creating the server.

  7. On the Azure SQL Database screen, keep the default generated Database Name. Select Create and wait for the database resources to be created.

    Screenshot that shows configuring the database.

  8. When the database resources are created, select Next.

  9. On the Connect to Azure SQL Database screen, select Finish.

    Screenshot of the screen with messagea about configuring managed identity for the connection to work.

    Note

    If you see Local user secrets files instead, make sure you used the Publish page, not the Connected Services page, to configure SQL Database.

Your Azure SQL Database connection is now set up to use Managed Identity for Azure services, a secure method of connecting your app to your Azure resources that doesn't use secrets or passwords. You now need to set the appropriate permissions on the SQL user corresponding with this managed identity for the connection to work.

Configure managed identity

When the Azure SQL Database creation wizard set up the Azure SQL server with a managed identity and Entra ID Default authentication, it added your Entra ID account as the Azure SQL admin. If you're signed in to the same account in Visual Studio, you can use the same connection string to connect to the database in both Visual Studio and Azure.

  1. From the Tools menu, select NuGet Package Manager > Package Manager Console.

  2. In the Package Manager Console, install the following packages:

    Install-Package Microsoft.Data.SqlClient
Install-Package Microsoft.EntityFramework.SqlServer

  3. In a PowerShell command line, run the following command to sign in to SQL Database, replacing <server-name> with your server name and <entra-id-user> with the Microsoft Entra user name you used to set up the database in Visual Studio. That Entra user has admin access to the database server by default.

    sqlcmd -S <servername>.database.windows.net -d DotNetAppSqlDb_db -U <entra-id-user> -G -l 30

    Follow the prompts to sign in.

  4. At the SQL prompt, run the following commands to grant the minimum permissions your app needs, replacing <app-name> with your app name.

    CREATE USER [<app-name>] FROM EXTERNAL PROVIDER;
ALTER ROLE db_datareader ADD MEMBER [<app-name>];
ALTER ROLE db_datawriter ADD MEMBER [<app-name>];
ALTER ROLE db_ddladmin ADD MEMBER [<app-name>];
GO

Update the database context

The app uses a database context to connect with the database, which is referenced in the Models/MyDatabaseContext.cs file. In this section, you update the code to refer to the Entity Framework 6 SQL Server provider, which depends on the modern Microsoft.Data.SqlClient ADO.NET provider.

The Entity Framework 6 provider replaces the built-in System.Data.SqlClient SQL Server provider, and includes support for Microsoft Entra ID authentication methods. For more information, see Microsoft.EntityFramework.SqlServer}.

[DbConfigurationType(typeof(MicrosoftSqlDbConfiguration))] works locally to use Microsoft.Data.SqlClient for the database context, but because System.Data.SqlClient is hardcoded as the provider in Azure App Service, you need to extend MicrosoftSqlDbConfiguration to redirect references to System.Data.SqlClient to Microsoft.Data.SqlClient instead.

  1. In web.config, remove the entityFramework/providers/provider section and line: <provider invariantName="System.Data.SqlClient" .../>.

  2. In Models/MyDatabaseContext.cs, add the following class:

        public class AppServiceConfiguration : MicrosoftSqlDbConfiguration
    {
        public AppServiceConfiguration()
        {
            SetProviderFactory("System.Data.SqlClient", Microsoft.Data.SqlClient.SqlClientFactory.Instance);
            SetProviderServices("System.Data.SqlClient", MicrosoftSqlProviderServices.Instance);
            SetExecutionStrategy("System.Data.SqlClient", () => new MicrosoftSqlAzureExecutionStrategy());
        }
    }

  3. Add the following attribute to the MyDatabaseContext class declaration:

    [DbConfigurationType(typeof(AppServiceConfiguration))]

Deploy the ASP.NET app

  1. At the top of the Publish tab, select Publish. Your ASP.NET app deploys to Azure, and your default browser launches to the URL of the deployed app.

  2. To test the app, add a few to-do items.

    Screenshot that shows the published ASP.NET application in Azure App Service.

Congratulations! Your data-driven ASP.NET application is running live in Azure App Service.

Use SQL Server Object Explorer

You can use Visual Studio SQL Server Object Explorer to easily explore and manage your Azure SQL database. In SQL Server Object Explorer, you can perform most common database operations, such as running queries or creating tables, views, and stored procedures.

Allow client connection from your computer

By default, the Azure server allows connections to its databases only from Azure services, such as your Azure app. The new database opened its firewall to the App Service app you created.

To access the database from your local computer, such as from Visual Studio, the Azure server must open the firewall to allow access for the machine's public IP address.

If prompted to add access for your local client, make sure to select the option to Allow your computer's public IP address. This option creates a firewall rule to allow the public IP address of your local computer. The dialog box is already populated with your computer's current IP address.

If you don't get a prompt to add access for your local computer, you can go to your Azure SQL database in the Azure portal and select Set server firewall on the top menu bar. On the Networking page under Firewall rules, select the option to Add your client IPv4 address.

Note

If your internet service provider changes your public IP address, you need to reconfigure the firewall to access the Azure database again.

Connect to the Azure SQL database locally

  1. From the View menu, select SQL Server Object Explorer.

  2. At the top of the SQL Server Object Explorer window, select the icon to Add SQL Server.

  3. On the Connect screen, your connection appears under the Azure node. Complete the information for your Server Name, User Name, Password, and Database Name, and select Connect.

    Screenshot that shows configuring the database connection from Visual Studio.

  4. Once Visual Studio finishes configuring the connection for your SQL Database instance, your database appears in SQL Server Object Explorer. Expand <your connection name> > Databases > <your database name> > to see the data.

  5. Expand Tables, right-click the ToDoes table, and select View Data to interact with the database data.

    Screenshot that shows exploring SQL Database objects.

Update the app with Code First Migrations

You can use familiar tools in Visual Studio to update your database and app in Azure. In this step, you use Code First Migrations in Entity Framework to change your database schema and publish the change to Azure.

For more information about using Entity Framework Code First Migrations, see Getting Started with Entity Framework 6 Code First using MVC 5.

Update your data model

Open Models\Todo.cs in the code editor. Add the following property to the ToDo class:

public bool Done { get; set; }

Run Code First Migrations locally

Run a few commands to make updates to your local database.

  1. From the Tools menu, select NuGet Package Manager > Package Manager Console.

  2. In the Package Manager Console window, enable Code First Migrations:

    Enable-Migrations

  3. Add a migration:

    Add-Migration AddProperty

  4. Update the local database:

    Update-Database

  5. Press Ctrl+F5 to run the app. Test the Edit, Details, and Create New links.

If the application loads without errors, Code First Migrations succeeded. However, your page still looks the same because your application logic isn't using this new property yet.

Use the new property

Make some changes in your code to see the Done property in action. For this tutorial, you change only the Index and Create views to use the new property.

  1. Open Controllers\TodosController.cs, and in the Create() method on line 52, add Done to the list of properties in the Bind attribute. Your Create() method signature should look like the following code:

    public ActionResult Create([Bind(Include = "Description,CreatedDate,Done")] Todo todo)

  2. Open Views\Todos\Create.cshtml, and in the Razor code, note the <div class="form-group"> element that uses model.Description and the <div class="form-group"> element that uses model.CreatedDate.

    After these two elements, add the following <div class="form-group"> element that uses model.Done:

    <div class="form-group">
    @Html.LabelFor(model => model.Done, htmlAttributes: new { @class = "control-label col-md-2" })
    <div class="col-md-10">
        <div class="checkbox">
            @Html.EditorFor(model => model.Done)
            @Html.ValidationMessageFor(model => model.Done, "", new { @class = "text-danger" })
        </div>
    </div>
</div>

  3. Open Views\Todos\Index.cshtml, and just above the empty <th></th> element, add the following Razor code:

    <th>
    @Html.DisplayNameFor(model => model.Done)
</th>

  4. Above the <td> element that contains the Html.ActionLink() helper methods, add another <td> element with the following Razor code:

    <td>
    @Html.DisplayFor(modelItem => item.Done)
</td>

  5. Save all files, and press Ctrl+F5 to run the app.

  6. In the app, add a to-do item and select Done. The item should appear on your home page as a completed item. The Edit view doesn't show the Done field, because you didn't change the Edit view.

Enable Code First Migrations in Azure

Now that your code change works, including database migration, you can publish it to your Azure app and update your Azure SQL database with Code First Migrations too.

  1. In Visual Studio Solution Explorer, right-click your project and select Publish.

  2. Select More actions > Edit to open the publish settings.

    Screenshot that shows opening publish settings.

  3. In the MyDatabaseContext dropdown, select the database connection for your Azure SQL database.

  4. Select Execute Code First Migrations (runs on application start), and then select Save.

    Screenshot that shows Enable Code First Migrations in Azure app.

Publish your changes

Now that you enabled Code First Migrations in your Azure app, publish your code changes.

  1. On the Publish page, select Publish.

  2. In the published web app, try adding more to-do items again and selecting Done, and they should appear on your home page as completed items.

    All your existing to-do items are still displayed. When you republish your ASP.NET application, existing data in your Azure SQL database isn't lost. Also, Code First Migrations only changes the data schema and leaves your data intact.

    Screenshot that shows the Azure app after Code First Migration.

Stream application logs

You can stream tracing messages directly from your Azure app to Visual Studio.

Open Controllers\TodosController.cs, and note that each action starts with a Trace.WriteLine() method. This code shows you how to add trace messages to your Azure app.

Enable log streaming

  1. On the Visual Studio Publish page, scroll down to the Hosting section.

  2. Select the ellipsis ... at upper right and select View streaming logs.

    Screenshot that shows Enable log streaming.

    The logs are now streamed into the Output window.

    Screenshot that shows Log streaming in the Output window.

    You don't see any trace messages yet, because when you first select View streaming logs, your Azure app sets the trace level to Error, which logs only error events using the Trace.TraceError() method.

Change trace levels

  1. To change the trace levels to output other trace messages, in the Hosting section of the Publish page, select the ... at upper right and then select Open in Azure portal.

  2. On the Azure portal page for your app, select App Service logs under Monitoring in the left menu.

  3. Under Application logging (Filesystem), select Verbose under Level, and then select Save.

    Tip

    You can experiment with different trace levels to see what types of messages are displayed for each level. For example, the Information level includes all logs created by Trace.TraceInformation(), Trace.TraceWarning(), and Trace.TraceError(), but not logs created by Trace.WriteLine().

  4. In your browser, go to your Azure to-do list application again and navigate around the app. Trace messages like the following examples now stream to the Output window in Visual Studio.

    Application:2025-05-12T23:41:11  PID[17108] Verbose     GET /Todos/Index
Application:2025-05-12T23:42:04  PID[17108] Verbose     GET /Todos/Index
Application:2025-05-12T23:42:06  PID[17108] Verbose     POST /Todos/Create
Application:2025-05-12T23:42:07  PID[17108] Verbose     GET /Todos/Index

Stop log streaming

To stop the log-streaming service, select the Stop monitoring icon in the Output window.

Screenshot that shows Stop log streaming.

Clean up resources

In the preceding steps, you created Azure resources in a resource group. If you don't expect to need these resources in the future, you can delete them by deleting the resource group.

  1. From your web app's Overview page in the Azure portal, select the myResourceGroup link under Resource group.
  2. On the resource group page, make sure that the listed resources are the ones you want to delete.
  3. Select Delete resource group, type myResourceGroup in the text box, and then select Delete.
  4. Confirm again by selecting Delete.

Related content

Go to the next tutorial to learn how to use managed identity to improve Azure SQL Database connection security.

Tutorial: Connect to SQL Database from App Service without secrets using a managed identity