Update DevOps variable group via Pipeline PowerShell script with values from SQL DB

I am attempting to automate the updating of a variable group in Azure DevOps using a PowerShell script executed within a Pipeline. The goal is to retrieve values from a SQL database and dynamically update the variable group in Azure DevOps based on these values.

The process involves connecting to a SQL database, executing a query to retrieve specific values, and then constructing a JSON payload containing these values. This payload is then sent to the Azure DevOps API to update the variable group with the new values.

However, I am encountering issues with this process. Despite successfully retrieving values from the SQL database and constructing the JSON payload, the script fails when attempting to update the variable group in Azure DevOps. The error message suggests that there may be an issue with the format or content of the payload being sent to the Azure DevOps API.

The first issue I run into is this

Invoke-RestMethod : The underlying connection was closed: An unexpected error occurred on a send.
At line:65 char:13
+ $response = Invoke-RestMethod -Uri $url -Method Put -Headers $headers ...
+             ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Which I believe is resolved by running this at the start of the Script

[Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12

However, I now get this error

Invoke-RestMethod : {"$id":"1","innerException":null,"message":"Value cannot be null.\r\nParameter name:
variableGroupParameters","typeName":"System.ArgumentNullException,
mscorlib","typeKey":"ArgumentNullException","errorCode":0,"eventId":0}
At line:68 char:13
+ $response = Invoke-RestMethod -Uri $url -Method Put -Headers $headers ...

This is the URL, and I have correctly specified the variables/parameters for this, as when I hardcode the values and then search for that URL, the variable group in DevOps opens:

$url = "https://dev.azure.com/$organization/$project/_apis/distributedtask/variablegroups/$($variableGroupId)?api-version=6.0-preview.2"

I really can't figure out what the issue is, and would appreicate any assistance!

Many Thanks!

I can update Azure DevOps variable group via Pipeline PowerShell script. I use the Variablegroups - Update (api-version=7.1-preview.2).

Here is my test PowerShell script:

$orgname="your orgname"
$projectname = "your project name"
$variableGroupId = 1  # Replace with your variable group ID

$PAT=""
$base64AuthInfo = [Convert]::ToBase64String([Text.Encoding]::ASCII.GetBytes(("{0}:{1}" -f "",$PAT)))


$url = "https://dev.azure.com/$orgname/_apis/distributedtask/variablegroups/$($variableGroupId)?api-version=7.1-preview.2"
$url

$body = @"
{
    "name": "variable group 0415",
    "providerData": null,
    "type": "Vsts",
    "variables": {
        "TEST1": {
            "isSecret": false,
            "value": "abcd"
        },
        "TEST2": {
            "isSecret": false,
            "value": "123456"
        }
    },
    "variableGroupProjectReferences": [
        {
            "name": "variable group 0415",
            "projectReference": {
                "id": "",
                "name": "$projectname"
            }
        }
    ]
}
"@

$response = Invoke-RestMethod -Uri $url -Method Put -Body $body -ContentType "application/json" -Headers @{Authorization=("Basic {0}" -f $base64AuthInfo)}
$response | ConvertTO-Json

Here is my test yaml in the pipeline:

trigger:
- none

pool:
  vmImage: windows-latest

steps:
- task: PowerShell@2
  env:
      MY_ACCESS_TOKEN: $(System.AccessToken)
  inputs:
    targetType: 'inline'
    script: |
      $orgname="orgname"
      $projectname = "projectname"
      $variableGroupId = 13  # Replace with the actual variable group ID
      
      $url = "https://dev.azure.com/$orgname/_apis/distributedtask/variablegroups/$($variableGroupId)?api-version=7.1-preview.2"
      $url
      
      $body = @"
      {
        "name": "variable group 0415",
        "providerData": null,
        "type": "Vsts",
        "variables": {
            "TEST1": {
                "isSecret": false,
                "value": "AAAAA"
            },
            "TEST2": {
                "isSecret": false,
                "value": "11111"
            }
        },
        "variableGroupProjectReferences": [
            {
                "name": "variable group 0415",
                "projectReference": {
                    "id": "",
                    "name": "$projectname"
                }
            }
        ]
      }
      "@
      
      $response = Invoke-RestMethod -Uri $url -Method Put -Body $body -ContentType "application/json" -Headers @{
        "Authorization" = "Bearer $(System.AccessToken)"
      }
      $response | ConvertTO-Json

Note: To use $(System.AccessToken) instead of the PAT, you should give your build service account the Administrator role in security setting of the variable group.

Test result: