Azure SQL Database hosting integration

The Aspire Azure SQL Database hosting integration models the SQL Server as the AzureSqlServerResource type and SQL databases as the AzureSqlDatabaseResource type. To access these types and APIs for expressing them within your AppHost project, install the 📦 Aspire.Hosting.Azure.Sql NuGet package:

dotnet add package Aspire.Hosting.Azure.Sql

For an introduction to working with the Azure SQL Database hosting integration, see Get started with the Azure SQL Database integration.

Add Azure SQL server resource and database resource

In your AppHost project, call AddAzureSqlServer to add and return an Azure SQL server resource builder. Chain a call to the returned resource builder to AddDatabase, to add an Azure SQL database resource:

var builder = DistributedApplication.CreateBuilder(args);
  
var azureSql = builder.AddAzureSqlServer("azuresql")
    .AddDatabase("database");
  
var myService = builder.AddProject<Projects.MyService>()
    .WithReference(azureSql);

The preceding call to AddAzureSqlServer configures the Azure SQL server resource to be deployed as an Azure SQL Database server.

Connect to an existing Azure SQL server

You might have an existing Azure SQL Database service that you want to connect to. You can chain a call to annotate that your AzureSqlServerResource is an existing resource:

var builder = DistributedApplication.CreateBuilder(args);
  
var existingSqlServerName = builder.AddParameter("existingSqlServerName");
var existingSqlServerResourceGroup = builder.AddParameter("existingSqlServerResourceGroup");
  
var sqlserver = builder.AddAzureSqlServer("sqlserver")
    .AsExisting(existingSqlServerName, existingSqlServerResourceGroup)
    .AddDatabase("database");
  
builder.AddProject<Projects.ExampleProject>()
    .WithReference(sqlserver);
  
// After adding all resources, run the app...

For more information on treating Azure SQL resources as existing resources, see Use existing Azure resources.

Run Azure SQL server resource as a container

The Azure SQL Server hosting integration supports running the Azure SQL server as a local container. This is beneficial for situations where you want to run the Azure SQL server locally for development and testing purposes, avoiding the need to provision an Azure resource or connect to an existing Azure SQL server.

To run the Azure SQL server as a container, call the RunAsContainer method:

var builder = DistributedApplication.CreateBuilder(args);
  
var azureSql = builder.AddAzureSqlServer("azuresql")
    .RunAsContainer();
  
var azureSqlData = azureSql.AddDatabase("database");
  
var exampleProject = builder.AddProject<Projects.ExampleProject>()
    .WithReference(azureSqlData);

The preceding code configures an Azure SQL Database resource to run locally in a container.

Provisioning-generated Bicep

If you're new to Bicep, it's a domain-specific language for defining Azure resources. With Aspire, you don't need to write Bicep by-hand—the provisioning APIs generate Bicep for you. When you publish your app, the generated Bicep is output alongside the manifest file. When you add an Azure SQL Server resource, the following Bicep is generated:

@description('The location for the resource(s) to be deployed.')
param location string = resourceGroup().location
  
param principalId string
  
param principalName string
  
resource sqlServerAdminManagedIdentity 'Microsoft.ManagedIdentity/userAssignedIdentities@2023-01-31' = {
  name: take('azuresql-admin-${uniqueString(resourceGroup().id)}', 63)
  location: location
}
  
resource azuresql 'Microsoft.Sql/servers@2024-05-01-preview' = {
  name: take('azuresql-${uniqueString(resourceGroup().id)}', 63)
  location: location
  properties: {
    administrators: {
      administratorType: 'ActiveDirectory'
      login: sqlServerAdminManagedIdentity.name
      sid: sqlServerAdminManagedIdentity.properties.principalId
      tenantId: subscription().tenantId
      azureADOnlyAuthentication: true
    }
    minimalTlsVersion: '1.2'
    publicNetworkAccess: 'Enabled'
    version: '12.0'
  }
  tags: {
    'aspire-resource-name': 'azuresql'
  }
}
  
resource sqlFirewallRule_AllowAllAzureIps 'Microsoft.Sql/servers/firewallRules@2024-05-01-preview' = {
  name: 'AllowAllAzureIps'
  properties: {
    endIpAddress: '0.0.0.0'
    startIpAddress: '0.0.0.0'
  }
  parent: azuresql
}
  
resource database 'Microsoft.Sql/servers/databases@2024-05-01-preview' = {
  name: 'database'
  location: location
  properties: {
    freeLimitExhaustionBehavior: 'AutoPause'
    useFreeLimit: true
  }
  sku: {
    name: 'GP_S_Gen5_2'
  }
  parent: azuresql
}
  
output sqlServerFqdn string = azuresql.properties.fullyQualifiedDomainName
  
output name string = azuresql.name
  
output sqlServerAdminName string = azuresql.properties.administrators.login

The preceding Bicep provisions an Azure SQL Server with a managed identity administrator, TLS 1.2 minimum, and a General Purpose Serverless database with the Azure free offer enabled.

The generated Bicep is a starting point and is influenced by changes to the provisioning infrastructure in C#. Customizations to the Bicep file directly will be overwritten, so make changes through the C# provisioning APIs to ensure they are reflected in the generated files.

Customize provisioning infrastructure

All Aspire Azure resources are subclasses of the AzureProvisioningResource type. This type enables the customization of the generated Bicep by providing a fluent API to configure the Azure resources using the ConfigureInfrastructure API. For example, you can configure the sku, version, and more. The following example demonstrates how to customize the Azure SQL Database resource:

var builder = DistributedApplication.CreateBuilder(args);
  
var sql = builder.AddAzureSqlServer("sql")
    .ConfigureInfrastructure(infra =>
    {
        var sqlServer = infra.GetProvisionableResources()
            .OfType<SqlServer>()
            .Single();
  
        sqlServer.MinTlsVersion = SqlMinimalTlsVersion.Tls1_3;
  
        var database = infra.GetProvisionableResources()
            .OfType<SqlDatabase>()
            .Single();
  
        database.Sku = new SqlSku { Name = "HS_Gen5_2" }; // Hyperscale
    })
    .AddDatabase("db");

The preceding code:

• Chains a call to the ConfigureInfrastructure API:
  • The infra parameter is an instance of the AzureResourceInfrastructure type.
  • The provisionable resources are retrieved by calling GetProvisionableResources.
  • The SqlServer is configured with TLS 1.3 minimum.
  • The SqlDatabase is configured with a Hyperscale SKU instead of the default serverless.

For more information, see Customize Azure resources. For the full list of configurable properties, see the Azure.Provisioning.Sql API documentation.

Admin deployment script

When you deploy an Azure SQL Server resource, Aspire runs a deployment script that grants your application's managed identity access to the SQL database. This script executes on an Azure Container Instance (ACI) and connects to the SQL Server to create the necessary database user and role assignments.

Private endpoint considerations

When you add a private endpoint to the Azure SQL Server resource, public network access is disabled on the SQL Server. In order for the deployment script to execute successfully, the ACI it runs on needs to be able to access the SQL Server through the private network. This requires:

• A subnet delegated to ACI, so the container runs inside the virtual network.
• An Azure Storage account, so ACI can mount a file share for the deployment script contents and logs.

Aspire automatically creates both of these resources when a private endpoint is detected on the Azure SQL Server. Specifically, it allocates a minimal subnet at an open address in the top of the virtual network range and creates a new storage account with the appropriate settings.

Customize the deployment script behavior

You can modify the default behavior in the following ways:

Disable the deployment script

Call ClearDefaultRoleAssignments to disable the deployment script entirely, which also removes the default subnet and storage resources:

var builder = DistributedApplication.CreateBuilder(args);
  
var sql = builder.AddAzureSqlServer("sql")
    .ClearDefaultRoleAssignments();

Specify a custom subnet

Call WithAdminDeploymentScriptSubnet to provide your own subnet for the deployment script container. Aspire automatically delegates the specified subnet to ACI:

var builder = DistributedApplication.CreateBuilder(args);
  
var vnet = builder.AddAzureVirtualNetwork("vnet");
var peSubnet = vnet.AddSubnet("pe-subnet", "10.0.2.0/24");
var aciSubnet = vnet.AddSubnet("aci-subnet", "10.0.3.0/29");
  
var sql = builder.AddAzureSqlServer("sql")
    .WithAdminDeploymentScriptSubnet(aciSubnet);
var db = sql.AddDatabase("db");
  
peSubnet.AddPrivateEndpoint(sql);

Specify a custom storage account

Call WithAdminDeploymentScriptStorage to provide your own storage account for the deployment script. Aspire automatically creates a StorageFileDataPrivilegedContributor role assignment from the deployment script's identity to this storage account:

var builder = DistributedApplication.CreateBuilder(args);
  
var vnet = builder.AddAzureVirtualNetwork("vnet");
var peSubnet = vnet.AddSubnet("pe-subnet", "10.0.2.0/24");
  
var storage = builder.AddAzureStorage("scriptstorage");
var sql = builder.AddAzureSqlServer("sql")
    .WithAdminDeploymentScriptStorage(storage);
var db = sql.AddDatabase("db");
  
peSubnet.AddPrivateEndpoint(sql);

Connection properties

When you reference Azure SQL Server resources using WithReference, the following connection properties are made available to the consuming project:

Azure SQL Server resource

The Azure SQL Server resource exposes the following connection properties:

Azure SQL database resource

The Azure SQL database resource inherits all properties from its parent Azure SQL Server resource and adds: