{ "nbformat": 4, "nbformat_minor": 5, "metadata": { "kernelspec": { "display_name": "json", "language": "json", "name": "json" }, "language_info": { "name": "json", "version": "1.0.0" }, "blog_metadata": { "topic": "From Data API Builder to Governed APIs: Exposing SQL Safely in Azure", "slug": "from-data-api-builder-to-governed-apis-exposing-sql-safely-i", "generated_by": "LinkedIn Post Generator + Azure OpenAI", "generated_at": "2026-07-01T22:48:20.641Z" } }, "cells": [ { "cell_type": "markdown", "metadata": {}, "source": [ "# From Data API Builder to Governed APIs: Exposing SQL Safely in Azure\n", "\n", "This notebook turns the blog post into a hands-on validation guide for designing a governed API layer over Azure SQL. The core pattern is Azure SQL as the system of record, Data API Builder as the rapid exposure layer, Azure API Management as the governance plane, and Microsoft Entra ID for identity and access control." ] }, { "cell_type": "code", "metadata": {}, "source": [ "%pip install requests azure-identity" ], "execution_count": null, "outputs": [] }, { "cell_type": "code", "metadata": {}, "source": [ "import os\n", "import json\n", "import textwrap\n", "from pprint import pprint\n", "\n", "import requests\n", "from azure.identity import DefaultAzureCredential" ], "execution_count": null, "outputs": [] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Target architecture reference\n", "\n", "This cell captures the intended request flow. Consumers authenticate with Microsoft Entra ID, Azure API Management validates and governs traffic, Data API Builder executes only approved operations, and Azure SQL stays behind the API boundary." ] }, { "cell_type": "code", "metadata": {}, "source": [ "architecture = {\n", " \"flowchart\": {\n", " \"direction\": \"TD\",\n", " \"nodes\": [\n", " {\"id\": \"A\", \"label\": \"Client App\"},\n", " {\"id\": \"B\", \"label\": \"Azure API Management\"},\n", " {\"id\": \"C\", \"label\": \"Data API Builder\"},\n", " {\"id\": \"D\", \"label\": \"Azure SQL Database\"}\n", " ],\n", " \"edges\": [\n", " {\"from\": \"A\", \"to\": \"B\", \"label\": \"Entra ID token\"},\n", " {\"from\": \"B\", \"to\": \"C\", \"label\": \"Validate JWT / rate limit / transform\"},\n", " {\"from\": \"C\", \"to\": \"D\", \"label\": \"Managed Identity or SQL auth from secret\"},\n", " {\"from\": \"D\", \"to\": \"C\", \"label\": \"result\"},\n", " {\"from\": \"C\", \"to\": \"B\", \"label\": \"response\"},\n", " {\"from\": \"B\", \"to\": \"A\", \"label\": \"governed API response\"}\n", " ]\n", " }\n", "}\n", "\n", "print(json.dumps(architecture, indent=2))" ], "execution_count": null, "outputs": [] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## SQL contract surface\n", "\n", "Start by curating SQL objects before exposing anything through generated endpoints. The pattern below uses a base table, a view for reads, and a stored procedure for controlled writes." ] }, { "cell_type": "code", "metadata": {}, "source": [ "sql_contract = {\n", " \"language\": \"sql\",\n", " \"script\": \"-- SQL objects for a least-privilege API surface with a read model and controlled write path\\nCREATE TABLE dbo.Orders (\\n OrderId INT IDENTITY PRIMARY KEY,\\n CustomerId INT NOT NULL,\\n Amount DECIMAL(10,2) NOT NULL,\\n CreatedAt DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME()\\n);\\nGO\\nCREATE VIEW dbo.vOrders AS\\nSELECT OrderId, CustomerId, Amount, CreatedAt FROM dbo.Orders;\\nGO\\nCREATE PROCEDURE dbo.CreateOrder\\n @CustomerId INT,\\n @Amount DECIMAL(10,2)\\nAS\\nBEGIN\\n SET NOCOUNT ON;\\n INSERT INTO dbo.Orders(CustomerId, Amount) VALUES (@CustomerId, @Amount);\\n SELECT SCOPE_IDENTITY() AS OrderId;\\nEND;\"\n", "}\n", "\n", "print(json.dumps(sql_contract, indent=2))" ], "execution_count": null, "outputs": [] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Data API Builder configuration\n", "\n", "This configuration exposes only a curated read model and a stored procedure action. The important design choice is using the view `dbo.vOrders` instead of the base table so the external contract is less tightly coupled to internal schema changes." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Required variables and secrets\n", "\n", "- `SQL_CONNECTION_STRING`: connection string for Azure SQL or lower-environment SQL target\n", "- Prefer managed identity or identity-based access where supported by your hosting model and driver\n", "- In production, avoid embedding secrets directly in configuration files" ] }, { "cell_type": "code", "metadata": {}, "source": [ "dab_config = {\n", " \"$schema\": \"https://github.com/Azure/data-api-builder/releases/latest/download/dab.draft.schema.json\",\n", " \"data-source\": {\n", " \"database-type\": \"mssql\",\n", " \"connection-string\": \"@env('SQL_CONNECTION_STRING')\"\n", " },\n", " \"runtime\": {\n", " \"rest\": {\"enabled\": True, \"path\": \"/api\"},\n", " \"host\": {\"mode\": \"production\", \"cors\": {\"origins\": [\"https://portal.contoso.com\"]}}\n", " },\n", " \"entities\": {\n", " \"Orders\": {\n", " \"source\": \"dbo.vOrders\",\n", " \"permissions\": [{\"role\": \"authenticated\", \"actions\": [\"read\"]}]\n", " },\n", " \"CreateOrder\": {\n", " \"source\": {\"type\": \"stored-procedure\", \"object\": \"dbo.CreateOrder\"},\n", " \"permissions\": [{\"role\": \"authenticated\", \"actions\": [\"execute\"]}]\n", " }\n", " }\n", "}\n", "\n", "print(json.dumps(dab_config, indent=2))" ], "execution_count": null, "outputs": [] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Validate schema-coupling risk\n", "\n", "This simple validation compares a safer view-backed configuration with a more tightly coupled table-backed configuration. It helps illustrate why exposing views is a better default for stable contracts." ] }, { "cell_type": "code", "metadata": {}, "source": [ "comparison = {\n", " \"safe_pattern\": {\n", " \"entity\": \"Orders\",\n", " \"source\": \"dbo.vOrders\",\n", " \"operations\": [\"read\"],\n", " \"benefits\": [\n", " \"stable aliases\",\n", " \"hidden internal columns\",\n", " \"reduced schema coupling\"\n", " ]\n", " },\n", " \"risky_pattern\": {\n", " \"entity\": \"Orders\",\n", " \"source\": \"dbo.Orders\",\n", " \"operations\": [\"read\", \"create\", \"update\", \"delete\"],\n", " \"risks\": [\n", " \"publishes internal schema\",\n", " \"broad mutation surface\",\n", " \"breaking changes from column renames\"\n", " ]\n", " }\n", "}\n", "\n", "print(json.dumps(comparison, indent=2))" ], "execution_count": null, "outputs": [] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Azure API Management infrastructure definition\n", "\n", "API Management should be treated as a first-class platform component. This Bicep example provisions an APIM instance and outputs the gateway URL plus the backend URL intended for Data API Builder." ] }, { "cell_type": "code", "metadata": {}, "source": [ "bicep_template = {\n", " \"language\": \"bicep\",\n", " \"script\": \"param location string = resourceGroup().location\\nparam apimName string\\nparam publisherEmail string\\nparam publisherName string\\nparam dabBackendUrl string\\n\\nresource apim 'Microsoft.ApiManagement/service@2023-05-01-preview' = {\\n name: apimName\\n location: location\\n sku: { name: 'Consumption', capacity: 0 }\\n properties: {\\n publisherEmail: publisherEmail\\n publisherName: publisherName\\n }\\n}\\n\\noutput gatewayUrl string = 'https://${apim.name}.azure-api.net'\\noutput dabBackend string = dabBackendUrl\"\n", "}\n", "\n", "print(json.dumps(bicep_template, indent=2))" ], "execution_count": null, "outputs": [] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## App settings and APIM named value configuration\n", "\n", "This example shows how to externalize Data API Builder settings and register the backend URL in API Management. Exact CLI flags and managed identity details can vary, so validate against current Azure documentation before production use." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Required variables and secrets\n", "\n", "- `ResourceGroup`\n", "- `WebAppName`\n", "- `ApimName`\n", "- `SqlConnectionString`\n", "- If using managed identity, verify your hosting model, SQL driver support, and Azure SQL permissions" ] }, { "cell_type": "code", "metadata": {}, "source": [ "powershell_config = {\n", " \"language\": \"powershell\",\n", " \"script\": \"param(\\n [string]$ResourceGroup = \\\"rg-governed-api\\\",\\n [string]$WebAppName = \\\"dab-contoso\\\",\\n [string]$ApimName = \\\"apim-contoso\\\",\\n [string]$SqlConnectionString = \\\"Server=tcp:sqlsrv.database.windows.net;Database=appdb;Authentication=Active Directory Managed Identity;\\\"\\n)\\n\\naz webapp config appsettings set `\\n --resource-group $ResourceGroup `\\n --name $WebAppName `\\n --settings SQL_CONNECTION_STRING=\\\"$SqlConnectionString\\\" DAB_ENVIRONMENT=\\\"Production\\\"\\n\\naz apim nv create `\\n --resource-group $ResourceGroup `\\n --service-name $ApimName `\\n --named-value-id backend-url `\\n --display-name \\\"backend-url\\\" `\\n --value \\\"https://$WebAppName.azurewebsites.net/api\\\" `\\n --secret false\"\n", "}\n", "\n", "print(json.dumps(powershell_config, indent=2))" ], "execution_count": null, "outputs": [] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## APIM inbound policy\n", "\n", "This policy validates a Microsoft Entra ID JWT, applies rate limiting, forwards traffic to Data API Builder, and rewrites the route. It demonstrates the governance boundary at the edge." ] }, { "cell_type": "code", "metadata": {}, "source": [ "apim_policy = {\n", " \"language\": \"xml\",\n", " \"policy\": \"\\n \\n \\n \\n \\n \\n api://governed-orders-api\\n \\n \\n \\n \\n \\n \\n \\n \\n\"\n", "}\n", "\n", "print(json.dumps(apim_policy, indent=2))" ], "execution_count": null, "outputs": [] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Public API operation definition\n", "\n", "This operation definition shows a cleaner consumer-facing route in API Management. The public contract can remain stable even if backend paths or entity names evolve." ] }, { "cell_type": "code", "metadata": {}, "source": [ "apim_operation = {\n", " \"name\": \"get-orders\",\n", " \"properties\": {\n", " \"displayName\": \"Get Orders\",\n", " \"method\": \"GET\",\n", " \"urlTemplate\": \"/orders\",\n", " \"description\": \"Public governed endpoint for reading orders\",\n", " \"request\": {\n", " \"queryParameters\": []\n", " },\n", " \"responses\": [\n", " {\"statusCode\": 200, \"description\": \"Successful response\"},\n", " {\"statusCode\": 401, \"description\": \"Unauthorized\"}\n", " ]\n", " }\n", "}\n", "\n", "print(json.dumps(apim_operation, indent=2))" ], "execution_count": null, "outputs": [] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Consumer validation through the governed path\n", "\n", "This client example validates the architecture the way real consumers will use it: acquire a token for the API audience and call API Management, not SQL directly." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Required variables and secrets\n", "\n", "- `APIM_ORDERS_URL`: for example `https://apim-contoso.azure-api.net/orders`\n", "- `API_SCOPE`: for example `api://governed-orders-api/.default`\n", "- Azure credentials available to `DefaultAzureCredential` in your environment" ] }, { "cell_type": "code", "metadata": {}, "source": [ "scope = os.getenv(\"API_SCOPE\", \"api://governed-orders-api/.default\")\n", "url = os.getenv(\"APIM_ORDERS_URL\", \"https://apim-contoso.azure-api.net/orders\")\n", "\n", "print(json.dumps({\"scope\": scope, \"url\": url}, indent=2))\n", "\n", "try:\n", " token = DefaultAzureCredential().get_token(scope).token\n", " headers = {\"Authorization\": f\"Bearer {token}\", \"Accept\": \"application/json\"}\n", " response = requests.get(url, headers=headers, timeout=30)\n", " print(\"status_code=\", response.status_code)\n", " if response.headers.get(\"content-type\", \"\").startswith(\"application/json\"):\n", " payload = response.json()\n", " print(json.dumps(payload, indent=2)[:4000])\n", " else:\n", " print(response.text[:2000])\n", " response.raise_for_status()\n", "except Exception as exc:\n", " print(\"Request could not be completed in this environment.\")\n", " print(type(exc).__name__, str(exc))" ], "execution_count": null, "outputs": [] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Sequence reference for end-to-end flow\n", "\n", "This structured representation mirrors the governed request lifecycle from token acquisition through SQL execution and response return." ] }, { "cell_type": "code", "metadata": {}, "source": [ "sequence = {\n", " \"sequenceDiagram\": [\n", " {\"from\": \"Consumer App\", \"to\": \"Entra ID\", \"message\": \"Request access token\"},\n", " {\"from\": \"Entra ID\", \"to\": \"Consumer App\", \"message\": \"Bearer token\"},\n", " {\"from\": \"Consumer App\", \"to\": \"API Management\", \"message\": \"GET /orders with token\"},\n", " {\"from\": \"API Management\", \"to\": \"API Management\", \"message\": \"Validate JWT + apply policy\"},\n", " {\"from\": \"API Management\", \"to\": \"Data API Builder\", \"message\": \"Forward governed request\"},\n", " {\"from\": \"Data API Builder\", \"to\": \"Azure SQL\", \"message\": \"Execute allowed query/proc\"},\n", " {\"from\": \"Azure SQL\", \"to\": \"Data API Builder\", \"message\": \"Result set\"},\n", " {\"from\": \"Data API Builder\", \"to\": \"API Management\", \"message\": \"JSON response\"},\n", " {\"from\": \"API Management\", \"to\": \"Consumer App\", \"message\": \"Governed API response\"}\n", " ]\n", "}\n", "\n", "print(json.dumps(sequence, indent=2))" ], "execution_count": null, "outputs": [] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Governance checklist\n", "\n", "Use this checklist to validate whether your implementation is ready for consumers." ] }, { "cell_type": "code", "metadata": {}, "source": [ "governance_checklist = {\n", " \"sql_layer\": [\n", " \"Expose views for reads\",\n", " \"Use stored procedures for controlled writes\",\n", " \"Grant only required SQL permissions\"\n", " ],\n", " \"dab_layer\": [\n", " \"Expose only selected entities\",\n", " \"Prefer read-only entities by default\",\n", " \"Use curated sources instead of base tables\"\n", " ],\n", " \"apim_layer\": [\n", " \"Validate JWTs at the edge\",\n", " \"Apply rate limits and quotas\",\n", " \"Publish stable public routes\",\n", " \"Use versions and revisions\"\n", " ],\n", " \"validation\": [\n", " \"Test valid token access\",\n", " \"Test invalid token rejection\",\n", " \"Test unpublished route denial\",\n", " \"Test resilience to internal schema changes\",\n", " \"Confirm logging and traceability\"\n", " ]\n", "}\n", "\n", "print(json.dumps(governance_checklist, indent=2))" ], "execution_count": null, "outputs": [] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## When this pattern fits\n", "\n", "This pattern is strongest for CRUD-heavy operational data exposure, internal platform APIs, partner APIs with clear boundaries, and governed access for AI or conversational scenarios. It is weaker when you need complex orchestration, rich domain workflows, heavy custom authorization logic, or response shapes that diverge significantly from relational entities." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Next Steps\n", "\n", "- Start with one bounded domain and curate SQL objects first.\n", "- Expose only selected entities in Data API Builder.\n", "- Put Azure API Management in front before onboarding real consumers.\n", "- Use Microsoft Entra ID, least privilege, versioning, and observability as non-optional controls.\n", "- Treat generated endpoints as an accelerator for delivery, not the final governance story." ] } ] }