Comments (10)
@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.
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.
Related Issues (20)
- Feature request: Decouple using managed identity from updating sql network settings HOT 8
- Error: There was an error when attempting to execute the process 'sqlpackage'. HOT 3
- Changes on SQL Server firewall don't show up in Activity Logs HOT 1
- [Security] Use role "SQL Server Contributor" instead of "Contributor" HOT 3
- Failed to add firewall rule HOT 4
- Example `path: './sqlscripts/*.sql'` doesn't work HOT 3
- Upgrade to support node 20 HOT 3
- a sp has 300+rows, returns Error SQL72045: Script execution error. HOT 5
- Error: No credentails found. Add an Azure login script before this action. HOT 3
- Support SqlPackage.exe at a specific path HOT 1
- Azure/[email protected] with user-managed Identity is checking access on master DB and fails with token-identified principal HOT 3
- Running SQL script doesn't respect GO batch terminators HOT 1
- Az login issue HOT 3
- [dev] main.js built from Linux different than one built from Windows
- Can't use quotes in the connection string HOT 1
- Issues with Nordic Characters HOT 2
- Does not support dropping a coumn when the table is not empty. HOT 1
- SQLCMD v1.0.0 can't connect to Azure SQL Database due to SSL handshake error
- Breaking change to arguments in v2.3 HOT 9
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from sql-action.