sql-action V2 does not appear to be working with on-premises SQL Server about sql-action HOT 14 OPEN

@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.

from sql-action.

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.

from sql-action.

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?

from sql-action.

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.

from sql-action.

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

from sql-action.

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

from sql-action.

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?

from sql-action.

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.

from sql-action.

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...

from sql-action.

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

from sql-action.

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

from sql-action.

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

from sql-action.

Any update on this item?

from sql-action.

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

from sql-action.