How I Used SQL and Claude to Build My Azure DevOps Environment Settings File in Minutes

AI, DevOps / By

I needed to set up an environment settings file for a Power Platform pipeline the other day. The file tells Azure DevOps which connections to use when deploying a solution — and to fill it in, you need the connection ID for each connection reference.

The standard approach is to go into make.powerapps.com, click Connections in the left nav, open each connection one by one, and copy the GUID from the URL.

It works. But with six or eight connection references in a solution, you’re making a lot of clicks for something that should take thirty seconds.

Then it hit me: all of this data is sitting in the connectionreference table in Dataverse. I can just query it — and then let Claude format the output.

So I tried it. Ran a SQL query in SQL 4 CDS, copied the results, pasted them into Claude with a prompt. It handed back a ready-to-use JSON file. The whole thing took about two minutes.

What the settings file needs

The environment settings JSON looks like this:

{
  "EnvironmentVariables": [],
  "ConnectionReferences": [
    {
      "LogicalName": "",
      "ConnectionId": "",
      "ConnectorId": ""
    }
  ],
  "CopilotAgents": []
}

For each connection reference you need three values: the logical name, the connection ID, and the connector ID. All three are in Dataverse.

Step 1: Find your logical names

Before you run the query, you need the logical names of the connection references in your solution. Two ways to get them.

From the solution view: Open your solution in make.powerapps.com, filter by Connection References, and read the names from the list.

Screenshot of the solution view in make.powerapps.com — filtered to Connection References, showing the logical names in the Name column

Or with a quick SQL query: Run this with no filter to get everything in the environment at once:

SELECT 
    cr.connectionreferencelogicalname,
    cr.connectionid,
    cr.connectorid
FROM 
    connectionreference cr
ORDER BY 
    cr.connectionreferencelogicalname

Copy the logical names you need, then use them in the next step.

Step 2: Query with SQL 4 CDS

I use SQL 4 CDS by Mark Carrington for this — an XrmToolBox plugin that lets you query Dataverse with standard SQL. If you don’t have XrmToolBox yet, grab it at xrmtoolbox.com.

Connect to your environment, open a query window, and run this with your own logical names in the IN list:

NOTE: The name of the connection references names may have been changed during the blog post, but the principles are the same.

SELECT 
    cr.connectionreferenceid,
    cr.connectionreferencelogicalname,
    cr.connectionid,
    cr.connectorid,
    cr.statecode,
    su.fullname AS owner_name,
    su.internalemailaddress AS owner_email
FROM 
    connectionreference cr
LEFT JOIN 
    systemuser su ON cr.ownerid = su.systemuserid
WHERE 
    cr.connectionreferencelogicalname IN (
        'fe_DataverseCommonConnection',
        'fe_TeamsCommonConnection',
        'fe_PowerQueryDataflowsCommonConnection',
        'fe_OutlookCommonConnection'
    )

I added owner_name and owner_email so I can check that each connection is owned by a service account and not a named person. If you see a personal email in a production environment, sort that out before go-live.

One thing to watch: if connectionid comes back empty for a row, the connection hasn’t been authorized in that environment yet. Someone needs to go into the maker portal and authorize it before the pipeline will work.

Step 3: Let Claude format it

Select all rows in the results grid and copy them. Open a new Claude chat, paste the results, and use this prompt:

I have query results from a Dataverse connectionreference table.
Convert them into this JSON structure:

{
  "EnvironmentVariables": [],
  "ConnectionReferences": [
    {
      "LogicalName": "",
      "ConnectionId": "",
      "ConnectorId": ""
    }
  ],
  "CopilotAgents": []
}

Map the columns as follows:
- connectionreferencelogicalname → LogicalName
- connectionid → ConnectionId
- connectorid → ConnectorId

One object per row. Keep EnvironmentVariables and CopilotAgents as empty arrays.
Return only the JSON, no explanation.

Here are the results:
[PASTE YOUR QUERY RESULTS HERE]

Claude hands back a clean JSON block ready to drop straight into your settings file.

Step 4: Add it to your pipeline

Save the JSON as a file and add it to your repository. In the Azure DevOps pipeline, reference it in the deployment stage under the Import solution task — in the Environment Variables and Connection References field.

If you’re deploying to multiple environments, you’ll want a separate settings file for each one. The logical names stay the same — only the connection IDs change.

Here’s how to do it

What you need:

  • Claude — ideally with a project set up for your platform context
  • SQL 4 CDS (XrmToolBox plugin)|

The steps:

  1. Find your connection reference logical names in the solution view or via SQL
  2. Run the query in SQL 4 CDS against your target environment
  3. Copy the results grid
  4. Paste into Claude with the prompt above
  5. Copy the returned JSON into your settings file
  6. Add the file to your repository
  7. Reference it in the Import solution task in your pipeline
  8. Deploy and verify ✅

Give it a try next time you’re setting up a new environment. You might be surprised how fast it goes.

For More Content See the Latest Posts