Bug with firewall provisioning when using OIDC

[stephlocke]

I have created a pipeline

name: PR Environment Preview
on:
  pull_request:
    types: [opened, synchronize, reopened, edited]

permissions:
  contents: read
  id-token: write # Needed for Azure login with OIDC

env:
  DATABASE_SERVER: dbservername.database.windows.net'
  # DATABASE_SERVER: 'dbservername'
  DATABASE: 'dbname'
jobs:
  deploy_pr_database:
    name: Deploy PR Database
    environment: Production
    runs-on: ubuntu-latest

    steps:
      - name: Checkout code
        uses: actions/checkout@v4
      
      - name: Azure Login
        uses: azure/login@v2
        with:
          client-id: ${{ secrets.AZURE_CLIENT_ID }}
          tenant-id: ${{ secrets.AZURE_TENANT_ID }}
          subscription-id: ${{ secrets.AZURE_SUBSCRIPTION_ID }}
      
      - name: Deploy PR-specific Database
        uses: azure/arm-deploy@v1
        id: pr-db-deploy
        with:
          scope: resourcegroup
          resourceGroupName: ${{ secrets.AZURE_RESOURCE_GROUP }}
          template: ./infra/per-deployment-platform/pr-db.bicep
          parameters: >
            namePrefix=db
            environmentName=dev
            githubRef=${{ github.ref }}

      - name: Install sqlpackage
        run: dotnet tool install -g microsoft.sqlpackage
      - name: Deploy Database
        uses: azure/[email protected]
        with:
          connection-string: 'Server=${{env.DATABASE_SERVER}};Initial Catalog=${{env.DATABASE}};Authentication=Active Directory Default;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;'
          arguments: '-U ${{ secrets.AZURE_CLIENT_ID }} --authentication-method ActiveDirectoryAzCli' #per sql-action issue #262 
          path: db.sqlproj
          action: publish

Permissions the workload identity has:

• Is part of the entra admin group for the server
• Is a contributor on the rg
• Has SQL DB Contributor and SQL Server Contributor on the DB Server

What is working:

• The db bicep deploy works based on the az cli login with the federated workload identity associated with this pipeline.
• sql-action first errored because sqlpackage wasn't installed so there is a step to install and it's pulling version '170.1.61' which is latest stable - sql-action is executing as a result

What isn't working:

• When I deploy and run this without the azure sql db server .database.windows.net it successfully creates a firewall rule for an internal IP but can't connect to the DB
• When I use the fqdn I get the following error:

Error: Failed to add firewall rule. Unable to detect client IP Address. mssql: login error: Login failed for user '<token-identified principal>'. mssql: login error: Login failed for user '<token-identified principal>'.

Any idea(s) why the firewall creation is working when the connection string isn't correct (albeit with an internal IP), but I'm getting login failures when the connection string is correct for creating the firewall?

[stephlocke]

Running the action is debug mode

sqlcmd
sqlcmd_debug
##[debug]Caching tool go-sqlcmd 1.6.0 x64
##[debug]source dir: /home/runner/work/_temp/0d789991-a7a8-4cd2-b6f4-14e9954476f3
##[debug]destination /opt/hostedtoolcache/go-sqlcmd/1.6.0/x64
##[debug]finished caching tool
##[debug]Get action inputs.
##[debug]Validating if client has access to 'master' on 'sqlserver.database.windows.net'.
##[debug]The process 'sqlcmd' failed with exit code 1
##[debug]SqlCmd stderr: mssql: login error: Login failed for user '<token-identified principal>'.
##[debug]mssql: login error: Login failed for user '<token-identified principal>'.
##[debug]
##[debug]Validating if client has access to 'db' on 'sqlserver.database.windows.net'.
##[debug]The process 'sqlcmd' failed with exit code 1
##[debug]SqlCmd stderr: mssql: login error: Login failed for user '<token-identified principal>'.
##[debug]mssql: login error: Login failed for user '<token-identified principal>'.
##[debug]
Error: Failed to add firewall rule. Unable to detect client IP Address. mssql: login error: Login failed for user '<token-identified principal>'.
mssql: login error: Login failed for user '<token-identified principal>'.

[stephlocke]

Using a username and password was the workaround to get this working unfortunately.