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'

Assuming you have authenticating using managed identity and all this is working fine on Azure, to test this locally, your id would need to have atleast the same access as the app . You can then sign in to Azure on VScode and leave the code unchanged. Vscode will use your authentication to check the access available and execute the code as is. Check this link for more details https://learn.microsoft.com/en-us/javascript/api/overview/azure/identity-vscode-readme?view=azure-node-latest#visual-studio-code-authentication — Anupam Chand, Jul 04 '23 at 02:50

score 0 · Answer 1 · answered Jul 03 '23 at 15:18

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.

Thank you for your answer. To confirm, "MSI" is "Managed Service Identity"? Your answer appears to require Azure Pipelines, but I am trying to run this locally (before commit). I'm also struggling to translate the MSI/UAI usage into actual code, so I'm unsure how to approach what you describe in your first paragraph. — MattMS, Jul 04 '23 at 01:01
I've updated my question to capture my requirements more clearly. If I can better understand your solution then I will still reference it as a possible approach. — MattMS, Jul 04 '23 at 01:05

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

Database and app creation in Bicep

Local connection

Create user

1 Answers1