Feature request: Decouple using managed identity from updating sql network settings

[audunsolemdal]

I am trying to apply migrations from a self hosted runner. The runner has network access to the sql server via private endpoint and does not need any new network openings. The SQL server denies public access.

It seems as the sql-action is hard coded to attempt adding network rules if azure/login is found

(...)
  deployToDev:
    needs: [buildandtest]
    runs-on: [self-hosted, my-runner] # Deployment must happen through a self-hosted agent with network access.
    environment: "dev"
    permissions:
      id-token: write
      contents: read
      packages: read
    steps:
      #Run migrations

      - uses: azure/login@v1
        with:
          client-id: ${{ vars.AZURE_SQL_CLIENT_ID }}
          tenant-id: ${{ vars.TENANT_ID }}
          subscription-id: ${{ vars.AZURE_SUBSCRIPTION_ID }}

      - name: "Download migrations script"
        uses: actions/download-artifact@v3
        with:
          name: "migrations"
          path: "./SQLScripts"

      - name: Run migrations script
        uses: azure/sql-action@v2
        with:
          connection-string: "Server=xxxxl;Initial Catalog=yyyyy;Authentication=Active Directory Default; Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;"
          path: "./SQLScripts/migrations.sql"

I end up with a

The client 'zzzzzzzz' with object id 'zzzzzzzzzzzzzzzz' does not have authorization to perform action 'Microsoft.Sql/servers/firewallRules/write' over scope (..)

I would like there to be an input var to skip this step entirely, as I have no interest in ad-hoc openings towards the sql server.

I also tried not granting the managed identity access to any subscriptions, setting this

      - uses: azure/login@v1
        with:
          client-id: ${{ vars.AZURE_SQL_CLIENT_ID }}
          tenant-id: ${{ vars.TENANT_ID }}
          allow-no-subscriptions: true

this however failed with this error:

Error: The subscription 'MY-TENANT-ID could not be found.
Error: {"statusCode":404,"message":"The subscription 'MY-TENANT-ID' could not be found.","code":"SubscriptionNotFound"}

[maskati]

This is definitely unexpected. Any changes to Azure resource state should be explicitly opt-in rather than implicit as a result of some earlier run action.

[github-actions]

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

[audunsolemdal]

bump.

[github-actions]

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

[dzsquared]

The action attempts to add a firewall rule if it cannot login to the SQL instance. It first tries the master db, then the user db from the connection string.
https://github.com/Azure/sql-action/blob/master/src/SqlUtils.ts#L26

You may get more insights to the initial login failures with debug logging enabled: https://docs.github.com/en/actions/monitoring-and-troubleshooting-workflows/enabling-debug-logging

[github-actions]

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

[benjamin-hodgson]

The action attempts to add a firewall rule if it cannot login to the SQL instance

It appears this is not functioning correctly though - in my build I can successfully deploy the database (as a managed identity) but I still get the warning about the firewall.

I'd like to be able to opt out of the firewall behaviour altogether (per #205 (comment)) - I don't like that the action silently attempts to change a security-sensitive azure config with no way to opt out

[github-actions]

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