I am trying to use setup-ssis-devops-tools to deploy SSIS packages from a github actions file to my SSISDB in Azure SQL.
Previously, I successfully deployed using SQL authentication, but I want to switch to a more secure approach using Active Directory - Integrated (ADINT).

What I've Done So Far

• Configured OpenID Connect (OIDC) between GitHub Actions and an Azure application.
• Granted the Azure application the necessary permissions to deploy SSIS packages (I think).
• Successfully logged into Azure in the GitHub Actions workflow.

Issue

Everything works fine until the SSISDeploy.exe step, where I encounter the following error:

Failed to connect to the SQL Server 'XXXXXXXXXXXXX': Failed to connect to server XXXXXXXXXXXXX. Deploy failed: Failed to connect to server XXXXXXXXXXXXX. Error: Process completed with exit code 1.

The server address is correct, and I have used it successfully with SQL authentication before. However, I cannot find any clear documentation or examples showing how to authenticate using AD Integrated (ADINT) in SSISDeploy.exe.

• How can I configure SSISDeploy.exe to use AD Integrated (ADINT) in GitHub Actions?
• What is the correct syntax for passing authentication parameters for ADINT?
• Are there any additional Azure or SQL Server settings required to make this work?

Below is my workflow file for reference:

name: Build and deploy SSIS-package

on:
  push:
    branches:
      - XXXXXXXX
    paths:
      - 'XXXXX/**'

permissions:
  id-token: write
  contents: read


jobs:
  build_and_deploy:
    runs-on: windows-latest
    environment: development
    name: XXXXXXXXXX
    steps:
      - name: Checkout repository
        uses: actions/checkout@v2
      
      - name: Setup SQL Server SSIS devops tools
        uses: jonlabelle/[email protected]

      - name: Azure Login
        uses: azure/login@v1
        with:
          client-id: ${{ secrets.AZURE_CLIENT_ID }}
          tenant-id: ${{ secrets.AZURE_TENANT_ID }}
          subscription-id: ${{ secrets.AZURE_SUBSCRIPTION_ID }}
        
      # Build packages and deploy
      - name: Build SSIS-packages
        run: |
          SSISBuild.exe `
          -p:${{ github.workspace }}\XXX\XXXX.dtproj `
          -o:${{ github.workspace }}\XXXX\XXXX
      # Deploy to SSISDB-catalog: XXXXX
      - name: Deploy SSIS-packages
        run: |
          SSISDeploy.exe `
          -s:${{ github.workspace }}\XXXXX\XXXXX\XXXXX\XXXXX.ispac `
          -d:"catalog;/SSISDB/XXXXXX;${{ vars.SQL_SERVER_NAME }}" `
          -at:ADINT

Any guidance, documentation links, or examples would be greatly appreciated!

I am trying to use setup-ssis-devops-tools to deploy SSIS packages from a github actions file to my SSISDB in Azure SQL.
Previously, I successfully deployed using SQL authentication, but I want to switch to a more secure approach using Active Directory - Integrated (ADINT).

What I've Done So Far

• Configured OpenID Connect (OIDC) between GitHub Actions and an Azure application.
• Granted the Azure application the necessary permissions to deploy SSIS packages (I think).
• Successfully logged into Azure in the GitHub Actions workflow.

Issue

Everything works fine until the SSISDeploy.exe step, where I encounter the following error:

Failed to connect to the SQL Server 'XXXXXXXXXXXXX': Failed to connect to server XXXXXXXXXXXXX. Deploy failed: Failed to connect to server XXXXXXXXXXXXX. Error: Process completed with exit code 1.

The server address is correct, and I have used it successfully with SQL authentication before. However, I cannot find any clear documentation or examples showing how to authenticate using AD Integrated (ADINT) in SSISDeploy.exe.

• How can I configure SSISDeploy.exe to use AD Integrated (ADINT) in GitHub Actions?
• What is the correct syntax for passing authentication parameters for ADINT?
• Are there any additional Azure or SQL Server settings required to make this work?

Below is my workflow file for reference:

name: Build and deploy SSIS-package

on:
  push:
    branches:
      - XXXXXXXX
    paths:
      - 'XXXXX/**'

permissions:
  id-token: write
  contents: read


jobs:
  build_and_deploy:
    runs-on: windows-latest
    environment: development
    name: XXXXXXXXXX
    steps:
      - name: Checkout repository
        uses: actions/checkout@v2
      
      - name: Setup SQL Server SSIS devops tools
        uses: jonlabelle/[email protected]

      - name: Azure Login
        uses: azure/login@v1
        with:
          client-id: ${{ secrets.AZURE_CLIENT_ID }}
          tenant-id: ${{ secrets.AZURE_TENANT_ID }}
          subscription-id: ${{ secrets.AZURE_SUBSCRIPTION_ID }}
        
      # Build packages and deploy
      - name: Build SSIS-packages
        run: |
          SSISBuild.exe `
          -p:${{ github.workspace }}\XXX\XXXX.dtproj `
          -o:${{ github.workspace }}\XXXX\XXXX
      # Deploy to SSISDB-catalog: XXXXX
      - name: Deploy SSIS-packages
        run: |
          SSISDeploy.exe `
          -s:${{ github.workspace }}\XXXXX\XXXXX\XXXXX\XXXXX.ispac `
          -d:"catalog;/SSISDB/XXXXXX;${{ vars.SQL_SERVER_NAME }}" `
          -at:ADINT

Any guidance, documentation links, or examples would be greatly appreciated!

• sql-server
• ssis
• azure-active-directory
• github-actions

• Could you please let me know your machine is domain-joined r not – Pratik Lad Commented Mar 27 at 11:13
• @PratikLad I'm not too sure what machine you are referring to. The workflow file is running on a GitHub Runner and the server is in the azure cloud. And just to clarify, when I previously ran this workflow file (with SQL Username and password), it worked fine. – Theo Commented Mar 28 at 11:24

1 Answer 1

Deploy SSIS Packages Using Active Directory - Integrated (ADINT) in a GitHub Actions file? Error: Failed to connect to the SQL Server 'XXXXXXXXXXXXX': Failed to connect to server XXXXXXXXXXXXX. Deploy failed: Failed to connect to server XXXXXXXXXXXXX. Error: Process completed with exit code 1.

The error suggests that the SQL Server connection is failing when using OIDC. However, I have successfully connected to the server using OIDC.

Follow the below steps which I have tried with:

Step:1 To set up OIDC for authentication with SQL Server using Microsoft Entra ID, start by registering an application in the Microsoft Entra portal. Navigate to App registrations, then click New registration, and provide a name for the app. After registration, note down the Application (client) ID and Directory (tenant) ID.

Step:2 In the Microsoft Entra ID App Registration, navigate to Certificates & Secrets > Federated Credentials, and click + Add Federated Credential. Configure the Federated Credential details by setting the Issuer to https://token.actions.githubusercontent, the Organization to your GitHub anization name (e.g., my), and the Repository to your GitHub repository name (e.g., ssis-deploy). Set the Entity type to Environment, and the GitHub Environment Name to your specific environment (e.g., production). For the Subject Identifier, use repo:my/ssis-deploy:environment:production, replacing it with your specific repository and environment details, then click Add.

Step:3 To grant the GitHubDeploySSIS App Registration access to Azure SQL (SSISDB), navigate to your Azure SQL Server, go to Microsoft Entra ID admin, click Set admin, select GitHubDeploySSIS, then click Select and finally click Save.

Step:4 To set up your GitHub repository, first create a new repository named ssis-deploy (or your preferred name) and make it private. Add a README file for documentation. Next, go to the Settings of your GitHub repository, navigate to Secrets and Variables > Actions > New repository secret, and add the following secrets: AZURE_CLIENT_ID (your Azure client ID), AZURE_SUBSCRIPTION_ID (your Azure subscription ID from the Azure portal), and AZURE_TENANT_ID (your Azure tenant ID).

Step:5 To set up a GitHub Actions workflow for testing the connection, create a new file under .github/workflows in your GitHub repository (e.g., azure-connection-test.yml) with the following content:

name: Azure Login Test

on:
  workflow_dispatch:

permissions:
  id-token: write
  contents: read  # required for actions/checkout

jobs:
  login-test:
    runs-on: windows-latest
    steps:
      - name: Checkout code
        uses: actions/checkout@v4
        
      -name: Azure Login via OIDC
        uses: azure/login@v1
        with:
          client-id: ${{ secrets.AZURE_CLIENT_ID }}
          tenant-id: ${{ secrets.AZURE_TENANT_ID }}
          subscription-id: ${{ secrets.AZURE_SUBSCRIPTION_ID }}
          
      -name: Run Azure CLI command
        run: az account show`

This workflow will trigger manually using workflow_dispatch, log into Azure using OIDC, and run the az account show command to verify the connection.

Step:6

To trigger the workflow in GitHub Actions, go to your GitHub repository, click on the Actions tab, find the workflow Azure Login Test, then click Run workflow and click the Run workflow button.

Step:7 After the workflow runs, go to the Actions tab in your GitHub repository, find the workflow run, and click on it to view the details. As shown in the below Image: