{
  "nbformat": 4,
  "nbformat_minor": 5,
  "metadata": {
    "kernelspec": {
      "display_name": "Python 3",
      "language": "python",
      "name": "python3"
    },
    "language_info": {
      "name": "python",
      "version": "3.13.0"
    },
    "blog_metadata": {
      "topic": "How to expose governed data to copilots with REST, GraphQL, and MCP from one Azure service",
      "slug": "how-to-expose-governed-data-to-copilots-with-rest-graphql-an",
      "generated_by": "LinkedIn Post Generator + Azure OpenAI",
      "generated_at": "2026-05-05T21:08:01.661Z"
    }
  },
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "# How to expose governed data to copilots with REST, GraphQL, and MCP from one Azure service\n",
        "\n",
        "This notebook turns the blog post into a hands-on validation workflow using Python. It demonstrates the core pattern of exposing governed data once through a controlled Azure service boundary, then validating REST, GraphQL, and MCP-style access paths against that same boundary.\n",
        "\n",
        "The emphasis is on governance, identity, least privilege, and observability rather than protocol hype. Where the original post used PowerShell or Mermaid, this notebook adapts those ideas into Python-friendly validation steps and architecture notes."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "%pip install requests python-dotenv"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "import os\n",
        "import json\n",
        "import uuid\n",
        "from textwrap import dedent\n",
        "\n",
        "import requests\n",
        "from dotenv import load_dotenv"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## Reference architecture\n",
        "\n",
        "The blog's main idea is to centralize governed data exposure behind one Azure service boundary. REST, GraphQL, and MCP-style tool interfaces should converge on the same App Service-backed logic, identity model, and audit path.\n",
        "\n",
        "Below, we render the architecture as text so it can be inspected directly in the notebook."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "architecture = dedent(\"\"\"\n",
        "Governed exposure pattern\n",
        "-------------------------\n",
        "Enterprise app / Copilot / Agent\n",
        "    -> Azure API Management\n",
        "        -> REST endpoint\n",
        "        -> GraphQL endpoint\n",
        "        -> MCP Server endpoint\n",
        "            -> Azure App Service data service\n",
        "                -> Microsoft Entra managed identity\n",
        "                    -> Azure SQL / governed data\n",
        "\n",
        "Cross-cutting controls:\n",
        "- APIM policies: auth, rate limit, logging\n",
        "- Observability: App Insights / structured logs\n",
        "- One service boundary, one identity model, one audit path\n",
        "\"\"\")\n",
        "\n",
        "print(architecture)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## Required environment variables\n",
        "\n",
        "Set these variables before running the live validation cells:\n",
        "\n",
        "- `API_BASE_URL` — Base URL for the governed API, for example `https://api.contoso.azure-api.net/data`\n",
        "- `API_BEARER_TOKEN` — Entra access token for the client/app calling REST and GraphQL\n",
        "- `MCP_ENDPOINT` — MCP-style tool endpoint, for example `https://sql-mcp.contoso.azurewebsites.net/mcp/tools/queryCustomer`\n",
        "- `MCP_BEARER_TOKEN` — Bearer token for the MCP-style endpoint; can be the same as `API_BEARER_TOKEN` if appropriate\n",
        "\n",
        "Optional:\n",
        "\n",
        "- `CUSTOMER_ID` — Defaults to `CUST-1001`\n",
        "- `REQUEST_TIMEOUT` — Defaults to `15`\n",
        "\n",
        "You can place them in a `.env` file for local testing."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## Load configuration\n",
        "\n",
        "This setup cell loads environment variables and prints a sanitized configuration summary. It helps validate that the notebook is ready to call the governed service boundary without exposing secrets."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "load_dotenv()\n",
        "\n",
        "CONFIG = {\n",
        "    \"API_BASE_URL\": os.getenv(\"API_BASE_URL\", \"https://api.contoso.azure-api.net/data\"),\n",
        "    \"API_BEARER_TOKEN\": os.getenv(\"API_BEARER_TOKEN\", \"\"),\n",
        "    \"MCP_ENDPOINT\": os.getenv(\"MCP_ENDPOINT\", \"https://sql-mcp.contoso.azurewebsites.net/mcp/tools/queryCustomer\"),\n",
        "    \"MCP_BEARER_TOKEN\": os.getenv(\"MCP_BEARER_TOKEN\", \"\"),\n",
        "    \"CUSTOMER_ID\": os.getenv(\"CUSTOMER_ID\", \"CUST-1001\"),\n",
        "    \"REQUEST_TIMEOUT\": int(os.getenv(\"REQUEST_TIMEOUT\", \"15\")),\n",
        "}\n",
        "\n",
        "sanitized = {\n",
        "    k: (v[:12] + \"...\" if \"TOKEN\" in k and v else \"<missing>\" if \"TOKEN\" in k and not v else v)\n",
        "    for k, v in CONFIG.items()\n",
        "}\n",
        "\n",
        "print(json.dumps(sanitized, indent=2))"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## Compare governed REST and GraphQL calls\n",
        "\n",
        "This example validates the blog's claim that REST and GraphQL are not rival bets when they sit behind the same governed service. The client ergonomics differ, but both calls should resolve through the same authorization boundary, business logic, and observability path."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "base_url = CONFIG[\"API_BASE_URL\"].rstrip(\"/\")\n",
        "token = CONFIG[\"API_BEARER_TOKEN\"]\n",
        "customer_id = CONFIG[\"CUSTOMER_ID\"]\n",
        "timeout = CONFIG[\"REQUEST_TIMEOUT\"]\n",
        "\n",
        "if not token:\n",
        "    print(\"Skipping live REST/GraphQL calls because API_BEARER_TOKEN is not set.\")\n",
        "else:\n",
        "    headers = {\"Authorization\": f\"Bearer {token}\"}\n",
        "\n",
        "    rest = requests.get(\n",
        "        f\"{base_url}/customers/{customer_id}?include=openOrders\",\n",
        "        headers=headers,\n",
        "        timeout=timeout,\n",
        "    )\n",
        "    print(\"REST status:\", rest.status_code)\n",
        "    try:\n",
        "        print(\"REST shape:\")\n",
        "        print(json.dumps(rest.json(), indent=2))\n",
        "    except Exception:\n",
        "        print(rest.text)\n",
        "\n",
        "    graphql_query = \"\"\"\n",
        "    query GetCustomer($id: ID!) {\n",
        "      customer(id: $id) {\n",
        "        id\n",
        "        name\n",
        "        riskTier\n",
        "        openOrders { id total status }\n",
        "      }\n",
        "    }\n",
        "    \"\"\"\n",
        "    graphql = requests.post(\n",
        "        f\"{base_url}/graphql\",\n",
        "        headers={**headers, \"Content-Type\": \"application/json\"},\n",
        "        json={\"query\": graphql_query, \"variables\": {\"id\": customer_id}},\n",
        "        timeout=timeout,\n",
        "    )\n",
        "    print(\"\\nGraphQL status:\", graphql.status_code)\n",
        "    try:\n",
        "        print(\"GraphQL shape:\")\n",
        "        print(json.dumps(graphql.json(), indent=2))\n",
        "    except Exception:\n",
        "        print(graphql.text)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## Validate a governed REST call with correlation headers\n",
        "\n",
        "This example focuses on operational discipline. It adds a correlation ID and a scenario header so requests are easier to trace through API Management, App Service logs, and downstream audit systems."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "endpoint = f\"{CONFIG['API_BASE_URL'].rstrip('/')}/orders?top=5&status=Open\"\n",
        "token = CONFIG[\"API_BEARER_TOKEN\"]\n",
        "\n",
        "if not token:\n",
        "    print(\"Skipping live REST validation because API_BEARER_TOKEN is not set.\")\n",
        "else:\n",
        "    headers = {\n",
        "        \"Authorization\": f\"Bearer {token}\",\n",
        "        \"x-correlation-id\": str(uuid.uuid4()),\n",
        "        \"x-client-scenario\": \"copilot-order-summary\",\n",
        "    }\n",
        "\n",
        "    response = requests.get(endpoint, headers=headers, timeout=10)\n",
        "    print(\"Status:\", response.status_code)\n",
        "    response.raise_for_status()\n",
        "\n",
        "    payload = response.json()\n",
        "    print(\"Correlation ID used:\", headers[\"x-correlation-id\"])\n",
        "    print(\"Orders:\")\n",
        "    for order in payload.get(\"value\", []):\n",
        "        print(f\"{order.get('id')} | {order.get('customerName')} | {order.get('total')}\")"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## Invoke an MCP-style tool over HTTP\n",
        "\n",
        "This example demonstrates the agent-facing side of the pattern. The key architectural point is that the tool should expose constrained, governed access through the service boundary rather than allowing direct database access from the agent runtime."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "mcp_endpoint = CONFIG[\"MCP_ENDPOINT\"]\n",
        "mcp_token = CONFIG[\"MCP_BEARER_TOKEN\"] or CONFIG[\"API_BEARER_TOKEN\"]\n",
        "customer_id = CONFIG[\"CUSTOMER_ID\"]\n",
        "\n",
        "if not mcp_token:\n",
        "    print(\"Skipping live MCP-style call because MCP_BEARER_TOKEN/API_BEARER_TOKEN is not set.\")\n",
        "else:\n",
        "    payload = {\n",
        "        \"arguments\": {\n",
        "            \"customerId\": customer_id,\n",
        "            \"projection\": [\"name\", \"riskTier\", \"openOrders\"],\n",
        "        }\n",
        "    }\n",
        "    headers = {\n",
        "        \"Authorization\": f\"Bearer {mcp_token}\",\n",
        "        \"Content-Type\": \"application/json\",\n",
        "    }\n",
        "\n",
        "    result = requests.post(mcp_endpoint, headers=headers, json=payload, timeout=20)\n",
        "    print(\"Status:\", result.status_code)\n",
        "    result.raise_for_status()\n",
        "\n",
        "    tool_result = result.json()\n",
        "    print(\"Tool:\", tool_result.get(\"tool\"))\n",
        "    print(\"Governed output:\")\n",
        "    print(json.dumps(tool_result.get(\"content\"), indent=2))"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## Managed identity enablement for App Service\n",
        "\n",
        "The original post used PowerShell to enable a system-assigned managed identity on App Service. In this notebook, we generate the equivalent Azure CLI command from Python so the step can be reviewed, copied, and executed in a controlled environment.\n",
        "\n",
        "This is a governance-critical step because it supports secretless service-to-database access."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "resource_group = \"rg-governed-data\"\n",
        "app_name = \"data-service-prod\"\n",
        "\n",
        "cli_command = f\"az webapp identity assign --resource-group {resource_group} --name {app_name}\"\n",
        "\n",
        "print(\"Azure CLI command to enable system-assigned managed identity:\")\n",
        "print(cli_command)\n",
        "\n",
        "print(\"\\nExpected outcome:\")\n",
        "print(\"- Managed identity enabled for the App Service\")\n",
        "print(\"- Principal ID returned for later database authorization\")"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## Configure non-secret App Service settings\n",
        "\n",
        "The blog recommends storing non-secret configuration separately from credentials. This cell builds a Python dictionary representing the desired App Service settings and prints an Azure CLI command pattern that avoids embedding database passwords."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "resource_group = \"rg-governed-data\"\n",
        "app_name = \"data-service-prod\"\n",
        "app_settings = {\n",
        "    \"SQL_SERVER_FQDN\": \"contoso-sql.database.windows.net\",\n",
        "    \"SQL_DATABASE_NAME\": \"GovernedSales\",\n",
        "    \"GRAPHQL_ENABLED\": \"true\",\n",
        "    \"AGENT_TOOLS_MODE\": \"restricted\",\n",
        "    \"AUDIT_MODE\": \"structured\",\n",
        "}\n",
        "\n",
        "settings_args = \" \".join([f'{k}={v}' for k, v in app_settings.items()])\n",
        "cli_command = (\n",
        "    f\"az webapp config appsettings set --resource-group {resource_group} \"\n",
        "    f\"--name {app_name} --settings {settings_args}\"\n",
        ")\n",
        "\n",
        "print(\"Desired app settings:\")\n",
        "print(json.dumps(app_settings, indent=2))\n",
        "print(\"\\nAzure CLI command:\")\n",
        "print(cli_command)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## Least-privilege Azure SQL access for the managed identity\n",
        "\n",
        "This step translates the blog's illustrative T-SQL into a notebook-friendly validation artifact. The goal is to create a contained database user from the managed identity, grant read access, and explicitly deny write operations."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "principal_display_name = \"data-service-prod\"\n",
        "\n",
        "tsql = dedent(f\"\"\"\n",
        "CREATE USER [{principal_display_name}] FROM EXTERNAL PROVIDER;\n",
        "ALTER ROLE db_datareader ADD MEMBER [{principal_display_name}];\n",
        "DENY INSERT, UPDATE, DELETE TO [{principal_display_name}];\n",
        "\"\"\")\n",
        "\n",
        "print(\"Run the following T-SQL as an Entra-enabled admin against the target Azure SQL database:\\n\")\n",
        "print(tsql)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## Validate reader-only role membership and explicit permissions\n",
        "\n",
        "After granting access, you should verify that the managed identity has only the intended rights. This query inspects role membership and any direct permissions assigned to the database principal."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "principal_display_name = \"data-service-prod\"\n",
        "\n",
        "validation_sql = dedent(f\"\"\"\n",
        "SELECT dp.name, rp.name AS role_name\n",
        "FROM sys.database_role_members drm\n",
        "JOIN sys.database_principals rp ON drm.role_principal_id = rp.principal_id\n",
        "JOIN sys.database_principals dp ON drm.member_principal_id = dp.principal_id\n",
        "WHERE dp.name = '{principal_display_name}';\n",
        "\n",
        "SELECT permission_name, state_desc\n",
        "FROM sys.database_permissions p\n",
        "JOIN sys.database_principals dp ON p.grantee_principal_id = dp.principal_id\n",
        "WHERE dp.name = '{principal_display_name}';\n",
        "\"\"\")\n",
        "\n",
        "print(\"Validation SQL:\\n\")\n",
        "print(validation_sql)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## Request flow and trust sequence\n",
        "\n",
        "The blog emphasizes that trust must extend below the API surface. This cell renders the operational sequence as text so you can validate the intended control flow from request approval to managed identity token acquisition to least-privilege query execution and audited response."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "sequence = dedent(\"\"\"\n",
        "Request trust sequence\n",
        "----------------------\n",
        "1. Copilot / Agent sends REST, GraphQL, or MCP request with bearer token\n",
        "2. API Management applies auth, rate limit, and logging policies\n",
        "3. Approved request is forwarded to Azure App Service\n",
        "4. App Service requests an access token using managed identity\n",
        "5. Entra returns a token scoped for Azure SQL\n",
        "6. App Service executes a least-privilege query against Azure SQL\n",
        "7. Result set is filtered and shaped for the consumer\n",
        "8. Response is returned with auditability and correlation\n",
        "\"\"\")\n",
        "\n",
        "print(sequence)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## Governance checklist for enterprise copilots\n",
        "\n",
        "Protocol selection is only part of the design. This final validation artifact turns the blog's governance guidance into a practical checklist you can use when reviewing a copilot integration."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "checklist = [\n",
        "    \"Azure SQL roles are least-privilege and reader-only where appropriate\",\n",
        "    \"Hosted services use managed identities instead of embedded secrets\",\n",
        "    \"API scopes are narrow and aligned to consumer needs\",\n",
        "    \"GraphQL resolvers enforce authorization and query limits\",\n",
        "    \"MCP-style tools expose constrained operations only\",\n",
        "    \"Tenant consent and app registration hygiene are reviewed\",\n",
        "    \"Conditional Access is applied where appropriate\",\n",
        "    \"Structured logs avoid leaking secrets or tokens\",\n",
        "    \"Correlation IDs are propagated end-to-end\",\n",
        "    \"APIM policies complement, but do not replace, service authorization logic\",\n",
        "]\n",
        "\n",
        "for i, item in enumerate(checklist, start=1):\n",
        "    print(f\"{i}. {item}\")"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## Next Steps\n",
        "\n",
        "This notebook validated the central architectural idea: one governed Azure service can expose the same trusted data through REST, GraphQL, and MCP-style interfaces without creating separate security models for each consumer. The practical controls that make the pattern work are managed identity, least-privilege SQL access, APIM policy enforcement, resolver/tool authorization, and structured auditability.\n",
        "\n",
        "Next steps:\n",
        "\n",
        "1. Replace the placeholder URLs and tokens with your real Azure endpoints.\n",
        "2. Run the live REST, GraphQL, and MCP-style validation cells against a non-production environment.\n",
        "3. Implement correlation IDs and structured logging across APIM and App Service.\n",
        "4. Review SQL permissions to confirm reader-only access where expected.\n",
        "5. Decide which layer you will centralize first in your environment: the governed data service, the identity model, or the policy gateway."
      ]
    }
  ]
}