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:

Aspire CLI

Aspire CLI — Add Aspire.Hosting.Azure.Sql package

aspire add azure-sql

The Aspire CLI is interactive, be sure to select the appropriate search result when prompted:

Aspire CLI — Example output prompt

Select an integration to add:

> azure-sql (Aspire.Hosting.Azure.Sql)
> Other results listed as selectable options...

Note

If there's only one result, the CLI selects it automatically. You'll still need to confirm the version.

apphost.cs (C# file-based app)

C# — AppHost.cs

#:package Aspire.Hosting.Azure.Sql@*

PackageReference (*.csproj)

XML — Add Aspire.Hosting.Azure.Sql package reference

<PackageReference Include="Aspire.Hosting.Azure.Sql" Version="*" />

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:

C# — AppHost.cs

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.

Caution

When you call AddAzureSqlServer, it implicitly calls AddAzureProvisioning—which adds support for generating Azure resources dynamically during app startup. The app must configure the appropriate subscription and location. For more information, see Local provisioning: Configuration.

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:

C# — AppHost.cs

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:

C# — AppHost.cs

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.

Tip

The RunAsContainer method is useful for local development and testing. The API exposes an optional delegate that enables you to customize the underlying SqlServerServerResource configuration. For example, you can add a data volume or data bind mount. For more information, see the Aspire SQL Server hosting integration section.

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:

Generated Bicep — azuresql.bicep

@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:

AppHost.cs

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:

C# — AppHost.cs

var builder = DistributedApplication.CreateBuilder(args);

var sql = builder.AddAzureSqlServer("sql")
    .ClearDefaultRoleAssignments();

Caution

When you call ClearDefaultRoleAssignments, the deployment script no longer adds the app’s managed identity access to the database. You must ensure your application has the appropriate database access configured separately.

Specify a custom subnet

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

C# — AppHost.cs

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:

C# — AppHost.cs

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

Caution

The storage account must be network-accessible from the deployment script’s Azure Container Instance and must have AllowSharedKeyAccess enabled so the deployment script can mount a file share in the storage account.

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:

Property Name	Description
Host	The fully qualified domain name of the Azure SQL Server
Port	The SQL Server port (1433 for Azure)
Uri	The connection URI, with the format mssql://{Host}:{Port}
JdbcConnectionString	JDBC connection string with the format jdbc:sqlserver://{Host}:{Port};encrypt=true;trustServerCertificate=false

Azure SQL database resource

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

Property Name	Description
DatabaseName	The name of the database
Uri	The connection URI, with the format mssql://{Host}:{Port}/{DatabaseName}
JdbcConnectionString	JDBC connection string with the format jdbc:sqlserver://{Host}:{Port};database={DatabaseName};encrypt=true;trustServerCertificate=false

Note

Aspire exposes each property as an environment variable named [RESOURCE]_[PROPERTY]. For instance, the Uri property of a resource called db1 becomes DB1_URI. The client should add a valid authentication property for the JDBC connection string like authentication=ActiveDirectoryDefault or authentication=ActiveDirectoryManagedIdentity.