sql-action V2 does not appear to be working with on-premises SQL Server

[kevchant]

I have mentioned this to Drew Skwiers-Koballa casually in the past but thought better raise it here for visibility.

I seem to have issues using sql-action V2 to deploy to SQL Server on-premises. I am using a self-hosted GitHub Runner that works perfectly well with sql-action v1.3.

One of the types of errors I get is below, but it changes depending on which connection string format I use:

"Error: Failed to add firewall rule. Unable to detect client IP Address. unable to open tcp connection with host 'localhost:1433': dial tcp [::1]:1433: connectex: No connection could be made because the target machine actively refused it."

Maybe there is a workaround for using it with v2 that is not documented. However, for now I workaround it by using v1.3.

[dzsquared]

@kevchant is the connection string format including the server as 'localhost:1433'? you may want to switch it to 'localhost,1433' (difference being the comma before the port number)

is the github runner also the SQL server? That error message suggests the runner is trying to open a connection to localhost. If it is indeed localhost, I wonder if the loopback IP would work.

[kevchant]

The GitHub runner is running on the local computer and SQL Server is running on a local docker container.

The port syntaxis the same as you recommend, which works with sql-action v1.3.

I have also tried various permutations, including the old dot syntax. Will rotate through all of them again when I get the chance to rule them all out.

[dzsquared]

I setup a test environment to probe this further - an Ubuntu 22.04 VM. Installed Docker (+ dotnet, sqlpackage, bzip2, go-sqlcmd) and started a container for mssql/server:2022-latest on port 1433. Launched the self-hosted GitHub runner on the VM.

From a repo, I added this workflow:

on: [workflow_dispatch]

jobs:
  build:
    runs-on: self-hosted
    steps:
    - uses: actions/checkout@v3
    - uses: azure/[email protected]
      with:
        connection-string: ${{ secrets.LOCAL_SQL }}
        path: './ProductsTutorial.sqlproj'
        action: 'publish'

where the LOCAL_SQL secret is Server=localhost,1433;User ID=sa;Password=P@ssw0rd;Initial Catalog=ProductsTutorial;Encrypt=False;

if I switch to localhost:1433, the workflow does fail but the error doesn't appear until the sqlpackage step and it's an instance cannot be found error. In moving to sql-action v2, we added connection string parsing from node-mssql such that AAD auth types could be handled. https://github.com/tediousjs/node-mssql/blob/1b12250d20f4df9168a9f96ce6c8bf46888ba390/lib/base/connection-pool.js#L101

if I remove Encrypt=False, I get an error in the sqlpackage step for Unable to connect to target server 'localhost'. Please verify the connection information such as the server name, login credentials, and firewall rules for the target server.

All of these erorrs are from past the firewall/access check, that is resulting in your runner attempting to add a firewall rule. Are you able to connect to the SQL container from a CLI like sqlcmd directly on the machine?

[kevchant]

Sorry, forgot to reply to this earlier in the week. I have issues with various permutations of connection strings to no avail.

However, I can connect using sqlcmd just fine. Plus, I can connect OK with sql-action v1.3.

Let me know if you want me to test anything else.

[github-actions[bot]]

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

[Jarod1662]

I'm getting the same issue, any advice would be welcome...

[sstorey-bma]

Running v2.2 with a locally deployed Windows Github Action runner.

When using the connection string format (per the guidance note https://learn.microsoft.com/en-us/sql/connect/ado-net/connection-string-syntax?view=sql-server-ver16) for the connection string, the github action fails.

The connection string syntax also mirrors the format as specified on the sqlpackage web page (https://learn.microsoft.com/en-us/dotnet/api/microsoft.data.sqlclient.sqlconnection.connectionstring?view=sqlclient-dotnet-standard-5.1)

Persist Security Info=False;Integrated Security=true;Database="${{ vars.SQL_DATABASE }}";Server="${{ vars.SQL_SERVER }}";Encrypt=false;TrustServerCertificate=True;Connection Timeout=30;

It throws an error, asking me to provide a User ID. I however should not need to provide a User ID if Im using windows auth. Anyway, if I provide the credentials as it requests in the connection string for the User ID/Password (based on the windows account) it clearly skips the error check, but then is unable to connect.

If I replace the details with a SQL User ID and Password, it works correctly and the pre-checks, build and sqlpackge all run sucessfully.

I have also tried including the /ua argument (UniversalAuthentication) for SqlPackage, this did not resolve the issue either.

- uses: azure/[email protected]      
  with:        
    connection-string: 'Persist Security Info=False;Integrated Security=true;Database="${{ vars.SQL_DATABASE }}";Server="${{ vars.SQL_SERVER }}";Encrypt=false;TrustServerCertificate=True;Connection Timeout=30;'
    path: '${{ github.workspace }}\myproj.sqlproj'
    action: "publish"
    build-arguments: '-p:NETCoreTargetsPath="${{ vars.SSDT_PATH }}" -p:SystemDacpacsLocation="${{ vars.SSDT_PATH }}" --configuration ${{ vars.BUILD_CONFIGURATION }}'
    arguments: '/ua:True'

The github action runner is running using a Windows Account, which is privileged (as necessary) to successfully run the build/sql package.

If I run sqlpackage locally (using the Windows Account), it works correctly and uses windows auth. If I run sqlcmd -E locally (using the Windows Account), it also connects successfully.

Look forward to a response.

@kevchant / @dzsquared have you seen this issue before?

[ukphillips]

I am having a similar issue deploying to on-prem, but to a named instance on the server, so MYSQLSERVER\MyInstance was my Server\DataSource. It looks like there was a change at some point to how the ip check happens to see if a firewall rule needs to be opened...which is assumed if the server is unreachable. This IMO is a poor assumption and there should be something else from a setting perspective to indicate this is the scenario.

This line seems to be the issue:

https://github.com/Azure/sql-action/blob/v2.2/src/SqlUtils.ts#L126C8-L126C8

only the server is used to connect, which is not considering the instance which would be set on the connectionConfig.options.instanceName property. This is causing the call to fail out. I think I can revert to an earlier version as mentioned in this thread to get the support in the short term, but something that should be addressed.

[kevchant]

Hi all, sorry I have been on vacation. What happens if you use sqlaction 1.3?

Because that has been my workaround until this has been resolved...

[sstorey-bma]

Unfortunately unable to get windows auth with local runner to work with either v1.3 or v2+

[ukphillips]

@kevchant reverting to 1.3 did work for me, thanks!

[github-actions[bot]]

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

[sstorey-bma]

Any update on this item?

[github-actions[bot]]

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

[shs-jmic]

@sstorey-bma - Were you able to find any resolution for this issue. We are running into exactly same issue with SQL Server on a VM. It works fine with Azure SQL Server and Azure SQL Managed Instance.

I tried on both v1.3 and v2.3. Below is the connection string used and error message in different version.

Server=tcp:<>,1433;Initial Catalog=<>;Persist Security Info=False;User ID=<>;Password=<>;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Authentication='Active Directory Password';

Run azure/[email protected] Error: Failed to add firewall rule. Unable to detect client IP Address. mssql: login error: Login failed for user ''.

Run azure/[email protected] 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 '<>'..