How do I authenticate Azure App Service with Azure SQL without using passwords?

Question

I have created an Azure SQL Server database and an Azure App Service, now I want to authenticate the application with the database, without using SQL authentication (no passwords).

If I was doing this outside of Azure, I would need to create a login on the SQL Server instance, using an Active Directory user, and then create a linked user on the database. I would then be running that app server as that AD user to make use of Windows authentication.

I am hoping for a solution that is entirely in Bicep, but would also accept using the Az and/or dbatools Powershell modules.

NOTE: My solution is required to be executed locally, before commit.

If my Bicep and SQL (CREATE USER "my-site" FROM EXTERNAL PROVIDER) are correct, then the problem I need solved is to figure out how to run this both locally and on our build servers.

Database and app creation in Bicep

Here is the Bicep code that I currently have (with renamed resources):

param location string = resourceGroup().location

resource sqlServer 'Microsoft.Sql/servers@2022-08-01-preview' = {
  location: location
  name: 'my-sql-server'
  resource sqlServerAdmin 'administrators' = {
    name: 'ActiveDirectory'
    properties: {
        administratorType: 'ActiveDirectory'
        login: adGroupThatIBelongTo
        sid: someGuid
    }
  }
}

resource database 'Microsoft.Sql/servers/databases@2022-08-01-preview' = {
    location: location
    name: 'my-database'
    parent: sqlServer
    sku: {
        name: 'S0'
        tier: 'Standard'
    }
}

var sqlConnectionString = join([
        'Server=tcp:${sqlServer.name}${az.environment().suffixes.sqlServerHostname},1433'
        'Initial Catalog=${database.name}'
        'Persist Security Info=False'
        'MultipleActiveResultSets=False'
        'Encrypt=True'
        'TrustServerCertificate=False'
        'Connection Timeout=30;'
    ], ';')

resource serverFarm 'Microsoft.Web/serverfarms@2022-09-01' = {
    location: location
    kind: 'linux'
    name: 'my-server-farm'
    sku: {
        name: 'F1'
    }
}

resource site 'Microsoft.Web/sites@2022-03-01' = {
    identity: {
        type: 'SystemAssigned'
    }
    location: location
    name: 'my-site'
    properties: {
        serverFarmId: serverFarm.id
        siteConfig: {
            connectionStrings: [
                {
                    connectionString: sqlConnectionString
                    name: 'db'
                    type: 'SQLAzure'
                }
            ]
        }
    }
}

The Bicep code is run with New-AzResourceGroupDeployment (from Az.Resources).

Local connection

In an attempt to use my local tools, I also added my local IP address to the firewall exceptions for the SQL Server within the Azure portal.

I try to connect to my database server with dbatools:

$con = Connect-DbaInstance -SqlInstance my-sql-server.database.windows.net

It gives me the following error (with nothing after returned error):

ConnectionError: C:\Program Files\WindowsPowerShell\Modules\dbatools\1.1.145\allcommands.ps1:97471
 Line |
97471 |          throw $records[0]
      |          ~~~~~~~~~~~~~~~~~
      | Error connecting to [my-sql-server.database.windows.net]: Federated service at
      | https://autologon.microsoftazuread-sso.com/my-tenant/winauth/trust/2005/windowstransport?client-request-id=....
      | returned error:

I fixed the error by creating a token, following an answer on DBA Stack Exchange:

$t = Get-AzAccessToken -ResourceUri "https://database.windows.net"

$con = Connect-DbaInstance -AccessToken $t -Database my-database -SqlInstance my-sql-server.database.windows.net

Create user

From my reading, I gather the next step would be the following:

Invoke-DbaQuery -Database my-database -SqlInstance $con -Query 'CREATE USER "my-site" FROM EXTERNAL PROVIDER'

Answer 1

So this is a tricky one.

First as I believe you discovered that Bicep cannot insert the user into the SQL Database. What will need to happen is the App Service MSI or User Assigned Identity. I would recommend the UID as SQL Server will cache the thumbprint for the MSI which if the MSI gets deleted recreate will not pick up the change. If preferring MSI then would recommend to always run a DROP if exists and Recreate.

I would recommend the App Service Name be passed in/setup for access via the [SqlAzureDacpacDeployment@1]. Though the name is dacpac can run inline scripts or sql scripts to provision the access for yoru App Service. (https://learn.microsoft.com/en-us/azure/devops/pipelines/tasks/reference/sql-azure-dacpac-deployment-v1?view=azure-pipelines) and as a perquisite the ADO service principle executing the pipeline will need to be in your adGroupThatIBelongTo.