Getting Failed to add firewall rule error during SQL DB deployment using GitHub Hosted Runner

[tmfahim]

Getting Failed to add firewall rule error during SQL DB deployment using GitHub Hosted Runner although 'Allow Azure Services And resources to access this server" is checked in the SQL server

Error: Failed to add firewall rule. Unable to detect client IP Address. Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : Login failed for user 'MCSSQLADMIN'..
Error: The process 'C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\170\Tools\Binn\SQLCMD.exe' failed with exit code 1

[zijchen]

Could you share a snippet of your yaml definition with the Azure login and sql-action tasks?

[dzsquared]

The error portion Error: Microsoft ODBC Driver 17 for SQL Server suggests this is an older version of sql-action. Will you try your pipeline with an updated version (v2.1.1 is latest)

[tmfahim]

Hi there, Please see below. release: # Set the dependency for the build job needs: BuildDacpac # The type of runner that the job will run on runs-on: windows-latest environment: PRD permissions: id-token: write contents: read # Steps represent a sequence of tasks that will be executed as part of the job steps: - name: download SQL Pool artifact # Downloads artifact to dacpac folder uses: ***@***.******@***.***> with: name: SQLPool # Login using Azure credentials - name: 'OIDC Login to Azure Public Cloud with AzPowershell' uses: ***@***.*** with: client-id: ${{ secrets.CLIENT_ID }} subscription-id: ${{ secrets.SUBSCRIPTION_ID }} tenant-id: ${{ secrets.TENANT_ID }} enable-AzPSSession: true - name: Azure SQL Deploy uses: ***@***.******@***.***> with: # Name of the SQL Server server-name: ${{ vars.SQL_SERVER }} # The connection string, including authentication information for the Azure SQL Database connection-string: '${{ secrets.AZURE_SQL_CONNECTION_STRING }}' # Path to the Azure SQL database dacpac file dacpac-package: 'uda-mcs-sql-db-cloud.dacpac' # Additional arguments arguments: '/p:IncludeCompositeObjects=true' With Regards, Fahim Amin From: Z Chen ***@***.***> Sent: Wednesday, April 12, 2023 6:55 PM To: Azure/sql-action ***@***.***> Cc: Amin, Fahim ***@***.***>; Author ***@***.***> Subject: Re: [Azure/sql-action] Getting Failed to add firewall rule error during SQL DB deployment using GitHub Hosted Runner (Issue #174) External Email - Think Before You Click Could you share a snippet of your yaml definition with the Azure login and sql-action tasks? — Reply to this email directly, view it on GitHub<#174 (comment)>, or unsubscribe<https://github.com/notifications/unsubscribe-auth/AYLTXYECYUT4PP5XV3RP2T3XA5FHHANCNFSM6AAAAAAWZQ4K7E>. You are receiving this because you authored the thread.Message ID: ***@***.******@***.***>>

[zijchen]

Formatted yml:

  release:
    # Set the dependency for the build job
    needs: BuildDacpac

    # The type of runner that the job will run on
   runs-on: windows-latest
    environment: PRD
    permissions:
      id-token: write
      contents: read

    # Steps represent a sequence of tasks that will be executed as part of the job
    steps:
     - name: download SQL Pool artifact
        # Downloads artifact to dacpac folder
        uses: ***@***.******@***.***>
        with:
          name: SQLPool

      # Login using Azure credentials
      - name: 'OIDC Login to Azure Public Cloud with AzPowershell'
        uses: ***@***.***
        with:
         client-id: ${{ secrets.CLIENT_ID }}
         subscription-id: ${{ secrets.SUBSCRIPTION_ID }}
         tenant-id: ${{ secrets.TENANT_ID }}
         enable-AzPSSession: true

      - name: Azure SQL Deploy
        uses: ***@***.******@***.***>
        with:
          # Name of the SQL Server
          server-name: ${{ vars.SQL_SERVER }}
          # The connection string, including authentication information for the Azure SQL Database
          connection-string: '${{ secrets.AZURE_SQL_CONNECTION_STRING }}'
          # Path to the Azure SQL database dacpac file
          dacpac-package: 'uda-mcs-sql-db-cloud.dacpac'
          # Additional arguments
          arguments: '/p:IncludeCompositeObjects=true'

[zijchen]

Looks like the Actions steps themselves were masked (maybe the email client thought they were email addresses), but as @dzsquared mentioned, could you try using sql-action@v2.1.1?

      - name: Azure SQL Deploy
        uses: azure/sql-action@v2.1.1
        with:
          # The connection string, including authentication information for the Azure SQL Database
          connection-string: '${{ secrets.AZURE_SQL_CONNECTION_STRING }}'
          # Path to the Azure SQL database dacpac file
          path: 'uda-mcs-sql-db-cloud.dacpac'
          action: 'Publish'
          # Additional arguments
          arguments: '/p:IncludeCompositeObjects=true'

[tmfahim]

Hi Chen, Thanks for your help. This actions now works if I allow Azure Services. However if I disable Azure Services on Azure SQL firewall and follow the procedure to add firewall, it fails at Azure Login action proving subscription not found error. I have done all the configuration and setup Federated credential on Azure AD. Surprisingly this same login action with same yaml code and similar federation works fine for another pipeline. Not sure if what is wrong here. Any help will be appreciated. Thanks. ***@***.*** ***@***.*** ***@***.*** With Regards, Fahim Amin From: Z Chen ***@***.***> Sent: Thursday, April 13, 2023 12:30 PM To: Azure/sql-action ***@***.***> Cc: Amin, Fahim ***@***.***>; Author ***@***.***> Subject: Re: [Azure/sql-action] Getting Failed to add firewall rule error during SQL DB deployment using GitHub Hosted Runner (Issue #174) External Email - Think Before You Click Looks like the Actions steps themselves were masked (maybe the email client thought they were email addresses), but as @dzsquared<https://github.com/dzsquared> mentioned, could you try using ***@***.******@***.***>? - name: Azure SQL Deploy uses: ***@***.******@***.***> with: # The connection string, including authentication information for the Azure SQL Database connection-string: '${{ secrets.AZURE_SQL_CONNECTION_STRING }}' # Path to the Azure SQL database dacpac file path: 'uda-mcs-sql-db-cloud.dacpac' action: 'Publish' # Additional arguments arguments: '/p:IncludeCompositeObjects=true' — Reply to this email directly, view it on GitHub<#174 (comment)>, or unsubscribe<https://github.com/notifications/unsubscribe-auth/AYLTXYAEUB6EE5NFHEKXZM3XBBA2VANCNFSM6AAAAAAWZQ4K7E>. You are receiving this because you authored the thread.Message ID: ***@***.******@***.***>>

[github-actions]

This issue is idle because it has been open for 14 days with no activity.

[catoa]

I'm also encountering this issue when trying to deploy a SQL managed instance.

[github-actions]

This issue is idle because it has been open for 14 days with no activity.