Unable to use it with on-prem SQL Server

[galvesribeiro]

Hello!

I'm trying to use this action to deploy some SQL Script to a SQL Container which is spin up as a services container at build time so we can validate the build with unit tests on Github Enterprise on a self-hosted GitHub Actions agent.

The SQL container is defined like this:

    services:
      sql:
        image: mcr.microsoft.com/mssql/server:2022-latest
        env:
          ACCEPT_EULA: Y
          SA_PASSWORD: yourStrong(!)Password
        ports:
          - 1433

The actions setup is as follow:

- uses: azure/sql-action@v1.3
        with:        
          connection-string: ${{ env.SQL_CS }}
          sql-file: 'src/*.sql'
        env:
          SQL_CS: "Server=localhost,${{ job.services.sql.ports[1433] }};Initial Catalog=MyDB;User Id=sa;Password=yourStrong(!)Password"

The error is shown like this:

Am I missing something? The readme says it is supposed to work with SQL Server, not just with Azure SQL. Can someone shed a light on it?

Thanks!

[dzsquared]

@galvesribeiro - I suspect that the issue here is the sidecar/services container is still starting up when the sql-action step tries to execute against it.

The below example includes a step to wait for the SQL engine in the container to be ready for connections.

    # service/sidecar container for azure-sql-edge
    services:
      mssql:
        image: mcr.microsoft.com/azure-sql-edge:latest
        env:
          ACCEPT_EULA: 1
          SA_PASSWORD: P@ssw0rd
        ports:
          - 1433:1433

    steps:
      - name: 'Checkout GitHub Action'
        uses: actions/checkout@v2

      - name: 'wait for sql to be ready'
        run: |
          set +o pipefail +e
          for i in {1..60};
          do
              sqlcmd -S localhost -U sa -P P@ssw0rd -d master -Q "select getdate()"
              if [ $? -eq 0 ]
              then
                  echo "sql server ready"
                  break
              else
                  echo "not ready yet..."
                  sleep 1
              fi
          done
          set -o pipefail -e
      # deploy the sql project
      - uses: azure/sql-action@v1.3
        with:
          connection-string: "Server=localhost;Initial Catalog=contoso-insurance;User ID=sa;Password=P@ssw0rd;"  # the local connection string
          project-file: './sql/build2022.sqlproj' # the SQLproj file
      
      - name: run a query
        run: |
          sqlcmd -S localhost -U sa -P P@ssw0rd -d contoso-insurance -Q "SELECT MonthCode, MonthName, Year, Month, CSAT from reporting.CSATByMonth for json auto"

[RonnyRen]

I ran into the same error.
The workaround is installing sqlcmd tools manually on the runner first and then you can use this action. It doesn't work as some GitHub actions that would install required packages/tools.

[dzsquared]

Good point - yes, this action requires sqlcmd to be installed for v1.x

Requirements in the readme: https://github.com/Azure/sql-action#environments

• sqlcmd
• sqlpackage (for sqlproj or dacpac deployment)
• dotnet (for sqlproj build)

[galvesribeiro]

Yep, it turns out that the problem was the tool not being installed as @RonnyRen suggested.

I was hoping this action to check if it is installed and install it if not. sqlcmd is only installed on Hosted agents. We use GitHub Enterprise Server so there is nothing on the containers :/

Thanks!

[dzsquared]

The in-progress v2 moves from sqlcmd to go-sqlcmd and will check/install go-sqlcmd as needed - #108

cc @zijchen (interest in keeping the check and install for go-sqlcmd to catch the custom agents that don't have it installed)