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 GitHubhttps://github.com/Azure/sql-action/issues/174#issuecomment-1506158128, or unsubscribehttps://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 @dzsquaredhttps://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 GitHubhttps://github.com/Azure/sql-action/issues/174#issuecomment-1507438260, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AYLTXYAEUB6EE5NFHEKXZM3XBBA2VANCNFSM6AAAAAAWZQ4K7E. You are receiving this because you authored the thread.Message ID: @.**@.>>

[github-actions[bot]]

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[bot]]

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

[ttaher]

Hello, i got this issue as well recently and i have an action that add the current Ip to my allowed ip list that can access my resource and IP was listed but still getting this error and preventing me to deploy database changes

[dzsquared]

@ttaher - you likely have an authentication issue to the SQL database, in the July release we added skip-firewall-check: as an option that you can use to validate the root cause in your case

[ttaher]

yes, I used it and it works now thanks a lot