Deploy to Azure SQL database via private endpoint about sql-action HOT 10 CLOSED

@dzsquared, adding this so it can help others as well. So I have two Self-Hosted agents running in Azure and was getting random successes running my pipeline and saw failures were on the same VM. Checking differences between them, I realized the one VM where things were was working did not have an Identity assigned as opposed to the other, where it had both System and User Assigned identities. On my YAML, I was passing the Object ID of another Identity, the one with Federated credentials for my Github repo.
Once I removed VM's identity, the pipeline run without problem. So this is not a problem with your action but instead might be with azure/login@v1, as I'm passing AZURE_CLIENT_ID as a secret but it is actually using VM's ID for authentication.

      - name: login to azure
        uses: azure/login@v1
        with:
          client-id: ${{ secrets.AZURE_CLIENT_ID }}
          tenant-id: ${{ secrets.AZURE_TENANT_ID }}
          subscription-id: ${{ secrets.AZURE_SUBSCRIPTION_ID }}

So the way I see it, these are my options to overcome this issue:

• Keep azure/login@v1 and run the VM without Identity assignments, which will authenticate using secrets.AZURE_CLIENT_ID;
• Keep azure/login@v1, change secrets AZURE_CLIENT_ID to match VM's System/User assigned identity and add required SQL roles;
• Remove azure/login@v1 and add required SQL roles for VM's System/User assigned identity;

Hope this helps someone else with the same problem.

Happy Holidays!

from sql-action.

@ruimatosVFX

I had a similar setup. Removing the Identity solved the issue. Thank you!

from sql-action.

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

from sql-action.

The firewall check is a check for connectivity to the server you provided - if the action tries the conection and succeeds, it doesn't attempt to add a firewall rule.
You may need to provide a connection string that includes the private endpoint address.

For deeper info, enabling debug logging may help: https://docs.github.com/en/actions/monitoring-and-troubleshooting-workflows/enabling-debug-logging

from sql-action.

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

from sql-action.

Having the same issue here attempting to publish a dacpac package to SQL.
My current setup is a self hosted Ubuntu agent with SQL Server having a private endpoint and set to only accept connections from selected networks.
I've created a Managed Identity with Federated Credentials and set this MSI with permissions, both on SQL and Azure as defined here: https://github.com/Azure/sql-action/blob/master/CONNECTION.md#azure-active-directory-managed-identity-authentication.
My steps include azure/login@v1 in order to get the authentication token and then Azure/[email protected]. It fails with 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>'.

I've SSH 'ed to the VM and nslookup properly resolves SQL server name to its private IP address.

adminuser@vm-gitrunner002-dev:~$ nslookup sql-REDACTED-shared-dev.database.windows.net
Server:         127.0.0.53
Address:        127.0.0.53#53

Non-authoritative answer:
sql-REDACTED-shared-dev.database.windows.net   canonical name = sql-REDACTED-shared-dev.privatelink.database.windows.net.
Name:   sql-REDACTED-shared-dev.privatelink.database.windows.net
Address: 10.20.14.134

Here's the output of the failed deployment step:

Run Azure/[email protected]
  with:
    connection-string: Server=***;Initial Catalog=***;Authentication=Active Directory Default; Encrypt=True;TrustServerCertificate=False;Connection Timeout=[30](https://github.com/REDACTED/REDACTED-Database-SQL/actions/runs/7287901099/job/19864189621#step:9:30);
    path: ./REDACTED-Database-SQL.dacpac
    action: publish
    arguments: /p:BlockOnPossibleDataLoss=False /v:Environment=DEV
  env:
    AZURE_HTTP_USER_AGENT: 
    AZUREPS_HOST_ENVIRONMENT: 
    SQLCMDINI: ./REDACTED-Database-SQL.dacpac.SQLCMDVariables
##[debug]isExplicit: 1.0.0
##[debug]explicit? true
##[debug]checking cache: /actions-runner/_work/_tool/go-sqlcmd/1.0.0/x64
##[debug]Found tool in cache go-sqlcmd 1.0.0 x64
##[debug]Get action inputs.
##[debug]Validating if client has access to 'master' on '***'.
##[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 '***' on '***'.
##[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>'.`

If I change the connection string to use another method of authentication like SQL Server Admin username/password it runs well so it's not connectivity issue, but this defeats the purpose of using an MSI for authentication.

Server=tcp:my-sql-server.database.windows.net,1433;Initial Catalog={your-database};Persist Security Info=False;User ID={admin-name};Password={your-password};MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;

From my perspective, the firewall rule makes no sense to be created if server 's name resolves to a private IP address range. I would honesty believe the action to create the firewall rule should be a parameter and optional.

from sql-action.

Thanks for posting details @ruimatosVFX - looking into ways to reproduce this error.
Checking that you've provided the workflow with access to the token (https://docs.github.com/en/actions/deployment/security-hardening-your-deployments/configuring-openid-connect-in-azure#adding-permissions-settings)

You would have a block like this at the top of your workflow:

permissions:
      id-token: write
      contents: read

from sql-action.

@dzsquared , yes, I do have those settings defined on my YAML.

from sql-action.

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

from sql-action.

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

from sql-action.