Bug with firewall provisioning when using OIDC #264
Description
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: publishPermissions 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.netit 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?