Performing validation with a local docker instance instead of Azure DB #483

Workflow file for this run

.github/workflows/pr-check.yml at 401ff3b

	name: pr-check

	# Tests PR code against a local SQL Server instance so no Azure credentials are required.
	# This workflow uses the pull_request trigger (not pull_request_target), so fork PRs run
	# with no secrets and no elevated permissions.
	#
	# - Linux runners: spin up SQL Server 2022 in a Docker container with SA auth.
	# - Windows runners: use the pre-installed SQL Server Express with Integrated Security.

	on:
	pull_request:

	permissions: {}

	jobs:
	test:
	strategy:
	fail-fast: false
	matrix:
	os: [ubuntu-latest, windows-latest]
	runs-on: ${{ matrix.os }}
	permissions:
	contents: read
	checks: write

	env:
	TEST_DB: SqlActionTest
	MSSQL_IMAGE: mcr.microsoft.com/mssql/server:2022-latest

	defaults:
	run:
	shell: bash

	steps:
	- name: Checkout PR
	uses: actions/checkout@11bd71901bbe5b1630ceea73d27597364c9af683 # v4.2.2
	with:
	ref: ${{ github.event.pull_request.head.sha }}

	# --- Linux setup: Docker container + SA auth with rotated password ---

	- name: Start SQL Server container (Linux)
	if: runner.os == 'Linux'
	run: \|
	docker run -d --name sqlserver \
	-e "ACCEPT_EULA=Y" \
	-e "MSSQL_SA_PASSWORD=Bootstrap1!" \
	-p 1433:1433 \
	"$MSSQL_IMAGE"

	- name: Wait for SQL Server to be ready (Linux)
	if: runner.os == 'Linux'
	run: \|
	for i in $(seq 1 30); do
	if docker exec sqlserver /opt/mssql-tools18/bin/sqlcmd \
	-S localhost -U sa -P 'Bootstrap1!' -C -Q 'SELECT 1' >/dev/null 2>&1; then
	echo "SQL Server is ready"
	exit 0
	fi
	echo "Waiting for SQL Server... ($i/30)"
	sleep 5
	done
	echo "SQL Server did not become ready in time"
	docker logs sqlserver
	exit 1

	- name: Rotate SA password and set connection string (Linux)
	if: runner.os == 'Linux'
	run: \|
	SA_PASSWORD="$(openssl rand -base64 18 \| tr -d '/+=')Aa1!"
	docker exec sqlserver /opt/mssql-tools18/bin/sqlcmd \
	-S localhost -U sa -P 'Bootstrap1!' -C \
	-Q "ALTER LOGIN sa WITH PASSWORD='${SA_PASSWORD}'"
	echo "BASE_CS=Server=localhost;User ID=sa;Password=${SA_PASSWORD};TrustServerCertificate=True;" >> "$GITHUB_ENV"

	# --- Windows setup: install SQL Server Express, then enable SQL auth with sa ---

	- name: Generate SA password (Windows)
	if: runner.os == 'Windows'
	run: \|
	SA_PASSWORD="$(openssl rand -base64 18 \| tr -d '/+=')Aa1!"
	echo "::add-mask::${SA_PASSWORD}"
	echo "SA_PASSWORD=${SA_PASSWORD}" >> "$GITHUB_ENV"

	- name: Install SQL Server Express (Windows)
	if: runner.os == 'Windows'
	shell: pwsh
	run: \|
	choco install sql-server-express -y --no-progress
	Get-Service \| Where-Object { $_.Name -like 'MSSQL*' } \| Format-Table

	- name: Enable mixed-mode auth and sa login (Windows)
	if: runner.os == 'Windows'
	shell: pwsh
	run: \|
	# Find sqlcmd.exe (installed by Express)
	$sqlcmd = (Get-ChildItem 'C:\Program Files\Microsoft SQL Server' -Recurse -Filter sqlcmd.exe -ErrorAction SilentlyContinue \|
	Select-Object -First 1).FullName
	if (-not $sqlcmd) { throw "sqlcmd.exe not found after SQL Express install" }
	Write-Host "Using sqlcmd at: $sqlcmd"

	# Connect with Windows auth (runner is local admin), switch to mixed mode
	& $sqlcmd -S 'localhost\SQLEXPRESS' -E -b -Q @"
	EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE',
	N'Software\Microsoft\MSSQLServer\MSSQLServer',
	N'LoginMode', REG_DWORD, 2;
	"@
	if ($LASTEXITCODE -ne 0) { throw "Failed to set LoginMode" }

	# Restart so LoginMode change takes effect
	Restart-Service 'MSSQL$SQLEXPRESS' -Force

	# Enable the sa login and set its password
	& $sqlcmd -S 'localhost\SQLEXPRESS' -E -b -Q "ALTER LOGIN sa ENABLE; ALTER LOGIN sa WITH PASSWORD='$env:SA_PASSWORD';"
	if ($LASTEXITCODE -ne 0) { throw "Failed to enable/set sa login" }

	# Verify SQL auth works
	& $sqlcmd -S 'localhost\SQLEXPRESS' -U sa -P $env:SA_PASSWORD -b -Q "SELECT @@VERSION"
	if ($LASTEXITCODE -ne 0) { throw "SQL auth verification failed" }

	- name: Set connection string (Windows)
	if: runner.os == 'Windows'
	run: \|
	echo "BASE_CS=Server=localhost\SQLEXPRESS;User ID=sa;Password=${SA_PASSWORD};TrustServerCertificate=True;" >> "$GITHUB_ENV"

	# --- Common build and test steps ---

	- name: Build GitHub Action
	run: npm ci --ignore-scripts && npm run build

	- name: Setup .NET
	uses: actions/setup-dotnet@v4
	with:
	dotnet-version: '8.x'

	- name: Install SqlPackage
	run: dotnet tool install -g microsoft.sqlpackage

	# Deploy a DACPAC with only a table to server (sqlpackage creates the DB if needed)
	- name: Test DACPAC Action
	uses: ./
	with:
	connection-string: '${{ env.BASE_CS }}Initial Catalog=${{ env.TEST_DB }};'
	path: ./__testdata__/sql-action.dacpac
	action: 'publish'
	skip-firewall-check: true

	# Build and publish sqlproj that should create a new view
	- name: Test Build and Publish
	uses: ./
	with:
	connection-string: '${{ env.BASE_CS }}Initial Catalog=${{ env.TEST_DB }};'
	path: ./__testdata__/TestProject/sql-action.sqlproj
	action: 'publish'
	skip-firewall-check: true

	# Execute testsql.sql via script action on server
	- name: Test SQL Action
	uses: ./
	with:
	connection-string: '${{ env.BASE_CS }}Initial Catalog=${{ env.TEST_DB }};'
	path: ./__testdata__/testsql.sql
	skip-firewall-check: true

	- name: Cleanup Test Database
	if: always()
	uses: ./
	with:
	connection-string: '${{ env.BASE_CS }}Initial Catalog=master;'
	path: ./__testdata__/cleanup.sql
	arguments: '-v DbName="${{ env.TEST_DB }}"'
	skip-firewall-check: true

	- name: Stop SQL Server container (Linux)
	if: always() && runner.os == 'Linux'
	run: docker rm -f sqlserver \|\| true

Navigation Menu

Uh oh!

Performing validation with a local docker instance instead of Azure DB #483

Workflow file

Performing validation with a local docker instance instead of Azure DB #483

Uh oh!

Workflow file for this run