Reports

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.com, the Organization to your GitHub organization name (e.g., myorg), 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:myorg/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: ![enter image description here]

79548054