microsoft / microsoft/GitHub-Copilot-for-Azure

Replace azure-deploy skill SQL access verification with a script

Open
#2,516 1 comment 0 reactions 1 assignee Claimed by @tmeschter View on GitHub
azure-deploy skills
Dominant language
Python
Stars
250
Forks
204
Avg merge
1d 12h
Merged PRs (30d)
67

Description

## Summary

Copilot has identified a portion of a skill that is a good candidate for replacement with a script.

The candidate is the **SQL access / role-membership verification** in the `azure-deploy` skill — load the azd environment, then run a fixed `az sql db query` that confirms the managed identity exists in the database and holds the expected roles. The same verification appears in two files.

## Candidate description

After granting a managed identity database access, the skill verifies it with a deterministic query sequence:

1. Load `azd env get-values` and resolve the server/database/identity.
2. Run `az sql db query` to confirm the external-provider user exists (`SELECT name, type_desc FROM sys.database_principals WHERE name = ''`).
3. Run `az sql db query` to confirm role membership (`db_datareader` / `db_datawriter` / `db_ddladmin`) and, optionally, that application tables exist beyond `__EFMigrationsHistory`.

This is a strong script candidate because it is:
- **A fixed load-env-then-query sequence** with no branching to run.
- **Output-reducing** — the queries return rows the caller checks against a known expectation; the script can collapse the result to a pass/fail plus the matched rows.
- **Duplicated** — the same managed-identity-exists and role-membership checks appear in both `recipes/azd/verify.md` (Step 4) and `recipes/azd/sql-managed-identity.md` (Verification).

**Sketch — `verify-sql-access.{sh,ps1}`:**
- **Input:** `--server`, `--database`, `--identity-name` (or resolve from azd env), optional `--expected-roles`.
- **Output:** a pass/fail summary — `Identity present; roles: db_datareader, db_datawriter ✓` — plus the raw matched rows for context.

> Interpreting "tables exist beyond `__EFMigrationsHistory`" as migrations-applied-success is a judgment call that stays in prose. The script gathers and presents the rows; the agent interprets sufficiency.

## Affected file and lines

- [`references/recipes/azd/verify.md` — Step 4 verify SQL access (L83–L139)](https://github.com/microsoft/GitHub-Copilot-for-Azure/blob/3890cbfb65c548ce8daa96cabd1d8de63f7bbcca/plugin/skills/azure-deploy/references/recipes/azd/verify.md#L83-L139)
- [`references/recipes/azd/sql-managed-identity.md` — Verification (L168–L202)](https://github.com/microsoft/GitHub-Copilot-for-Azure/blob/3890cbfb65c548ce8daa96cabd1d8de63f7bbcca/plugin/skills/azure-deploy/references/recipes/azd/sql-managed-identity.md#L168-L202)

## Next steps

1. **Evaluate the candidate** — confirm the steps are stable and parameterizable, and that the script captures everything the skill needs.
2. **Create both a bash _and_ a PowerShell version** of the script so the skill works across platforms.
3. **Run integration tests** to verify the scripts behave correctly and the skill still completes end-to-end.

## Background Information

### Why replace regular steps with scripts

Replacing a regular, well-defined series of steps with a script can:

- **Reduce token usage** — the skill no longer needs to spell out each command and parse large command output inline; the agent invokes one script and reads a compact result.
- **Improve reliability** — the logic is written and tested once, instead of being re-derived by the agent on every run.
- **Improve determinism** — the same inputs always produce the same steps and output, removing run-to-run variation.
- **Improve speed of execution** — a single script call replaces multiple round-trips of command generation, execution, and large-output parsing.

### Authoring notes for the scripts

- **Reference scripts with markdown links**, not just a bare path to the script file.
- **Include examples** in the skill showing how to run each script (sample invocation with arguments).
- **Briefly explain what each script does** where it is referenced.
- **The script output should explain what it did**, so the agent and user can understand the result without re-inspecting raw command output.

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.