| name | data-api-builder-mcp |
| description | Enable MCP endpoints in Data API Builder so AI agents (Copilot, Claude, etc.) can query SQL databases. Use when asked to set up MCP, create .vscode/mcp.json, or give an agent database access. |
| license | MIT |
SQL MCP Server (Data API Builder MCP)
This skill powers GitHub Copilot assistance for SQL MCP Server, a feature of Data API Builder (DAB) version 1.7+ that exposes databases to AI agents via the Model Context Protocol (MCP). It provides conversational guidance for configuring, deploying, and securing SQL MCP Server for AI-powered database workflows.
Core Mental Model
SQL MCP Server enables AI agents to interact with databases through a secure, typed interface. It's Data API Builder with MCP capabilities, not a separate product. Users who need MCP capabilities but have never heard of DAB should be guided through the complete setup.
What SQL MCP Server Provides
- MCP endpoint at
/mcp that exposes database entities as MCP tools
- Six DML tools for agents:
describe_entities, create_record, read_records, update_record, delete_record, execute_entity
- Security through abstraction - agents never touch SQL directly, only work through DAB's entity layer
- Deterministic queries - no NL2SQL, only NL2DAB (safe, predictable SQL generation)
- RBAC enforcement - role-based access control applies to every tool operation
- Built-in caching - automatic result caching for
read_records operations
- Full observability - OpenTelemetry tracing, health checks, Azure monitoring
Key Architecture
AI Agent (VS Code, Foundry, Custom)
→ MCP Protocol
→ SQL MCP Server (/mcp endpoint)
→ DAB Entity Abstraction Layer
→ Database (SQL Server, PostgreSQL, MySQL, etc.)
Configuration Approach
SQL MCP Server uses the same dab-config.json as regular DAB:
data-source: Database connection settings
runtime.mcp: MCP-specific settings (enabled, path, tool controls)
entities: Exposed tables/views/stored procedures with descriptions and permissions
MCP is enabled by default when you have DAB 1.7+. You only need to configure it when you want to restrict what agents can do.
Understanding MCP vs Data API Builder
Common User Starting Points
Scenario 1: User comes with MCP needs
- User: "I want to give my AI agent access to my database via MCP"
- Approach: Explain SQL MCP Server is DAB's MCP feature, guide through full setup
Scenario 2: User knows DAB, wants MCP
- User: "I have a DAB config, how do I enable MCP?"
- Approach: Just upgrade to v1.7+ - MCP is enabled by default
Scenario 3: User confused about naming
- User: "Is SQL MCP Server different from Data API Builder?"
- Approach: Clarify it's DAB 1.7+ with MCP capabilities enabled
Terminology Guide
| Term | What It Means |
|---|
| SQL MCP Server | Marketing name for DAB's MCP capabilities |
| Data API Builder (DAB) | The underlying engine that powers everything |
| MCP | Model Context Protocol - standard for AI agent tool discovery |
| DML Tools | The six CRUD+execute operations exposed via MCP |
| Entity abstraction | DAB's security layer that protects your database schema |
Installation & Prerequisites
Version Requirements
Critical: SQL MCP Server requires Data API builder 1.7+ (currently in preview/RC).
Install DAB CLI (Prerelease)
dotnet tool install microsoft.dataapibuilder --prerelease
Or update existing:
dotnet tool update microsoft.dataapibuilder --prerelease
Verify Installation
dab --version
Prerequisites Checklist
Quick Start Workflow
For Users New to Both MCP and DAB
Recommended Path:
- Install DAB CLI (prerelease)
- Create database and sample table
- Initialize DAB config with MCP enabled (default)
- Add entities with semantic descriptions
- Start SQL MCP Server locally
- Connect from VS Code or other MCP client
- Test with agent prompts
Example: 5-Minute Setup
dab init \
--database-type mssql \
--connection-string "@env('DATABASE_CONNECTION_STRING')" \
--host-mode Development \
--config dab-config.json
dab add Products \
--source dbo.Products \
--permissions "anonymous:read" \
--description "Product catalog with pricing, inventory, and supplier information"
dab update Products \
--fields.name ProductID \
--fields.description "Unique product identifier" \
--fields.primary-key true
dab update Products \
--fields.name ProductName \
--fields.description "Display name of the product"
dab update Products \
--fields.name UnitPrice \
--fields.description "Retail price per unit in USD"
dab validate && dab start
Connect from VS Code:
Create .vscode/mcp.json:
{
"servers": {
"sql-mcp-server": {
"type": "http",
"url": "http://localhost:5000/mcp"
}
}
}
Configuration Reference
MCP Runtime Settings
Default behavior: MCP is enabled with all tools active. Only configure when restricting.
{
"runtime": {
"mcp": {
"enabled": true,
"path": "/mcp",
"description": "Optional server description for clients",
"dml-tools": {
"describe-entities": true,
"create-record": true,
"read-records": true,
"update-record": true,
"delete-record": true,
"execute-entity": true
}
}
}
}
CLI Configuration Commands
dab configure --runtime.mcp.enabled true
dab configure --runtime.mcp.path "/mcp"
dab configure --runtime.mcp.dml-tools.delete-record false
dab configure --runtime.mcp.dml-tools.create-record false
dab configure --runtime.mcp.description "Production inventory database MCP endpoint"
Entity-Level MCP Control
Default: Entities participate in MCP automatically. Only configure to exclude or restrict.
{
"entities": {
"Products": {
"mcp": {
"dml-tools": true
}
},
"SensitiveData": {
"mcp": {
"dml-tools": false
}
},
"AuditLogs": {
"mcp": {
"dml-tools": {
"create-record": true,
"read-records": true,
"update-record": false,
"delete-record": false
}
}
}
}
}
The Six DML Tools
SQL MCP Server exposes exactly six tools to AI agents:
1. describe_entities
Purpose: Returns all entities the current role can access, including fields, types, descriptions, and allowed operations.
Key Points:
- Doesn't query the database - reads from in-memory config
- Shows only entities/fields the current role can see
- Critical first step for agents to understand available data
- Includes semantic descriptions you add via
--description flags
Example Response:
{
"entities": [
{
"name": "Products",
"description": "Product catalog with pricing and inventory",
"fields": [
{
"name": "ProductId",
"type": "int",
"isKey": true,
"description": "Unique product identifier"
},
{
"name": "UnitPrice",
"type": "decimal",
"description": "Retail price in USD"
}
],
"operations": ["read_records", "update_record"]
}
]
}
2. create_record
Purpose: Insert new rows into tables.
Requirements:
- Entity must have
create permission for current role
- Validates against entity schema
- Enforces create policies
- Returns created record with generated values (e.g., auto-increment IDs)
3. read_records
Purpose: Query tables and views with filtering, sorting, pagination, and field selection.
Features:
- Builds deterministic SQL from structured parameters (NL2DAB, not NL2SQL)
- Applies read permissions and field projections
- Enforces row-level security policies
- Automatic caching - results cached per DAB caching config
4. update_record
Purpose: Modify existing rows.
Requirements:
- Requires primary key and fields to update
- Validates PK exists
- Enforces update permissions and policies
- Only updates fields current role can modify
5. delete_record
Purpose: Remove existing rows.
Requirements:
- Requires primary key
- Validates PK exists
- Enforces delete permissions and policies
Warning: Many production deployments disable this tool globally to prevent accidental data loss.
6. execute_entity
Purpose: Execute stored procedures.
Features:
- Supports input parameters and output results
- Validates parameters against procedure signature
- Enforces execute permissions
- Passes parameters safely (prevents SQL injection)
Semantic Descriptions (Critical for AI)
Why descriptions matter: AI agents rely on context. Without descriptions, agents only see technical names like ProductID. With descriptions, they understand it's "Unique identifier for each product in the catalog."
Entity Descriptions
dab add Products \
--source dbo.Products \
--permissions "anonymous:*" \
--description "Product catalog with pricing, inventory, and supplier information"
dab update Products \
--description "Product catalog with pricing, inventory, and supplier information"
Field Descriptions
dab update Products \
--fields.name UnitPrice \
--fields.description "Retail price per unit in USD"
dab update Products \
--fields.name ProductID \
--fields.description "Unique identifier for each product" \
--fields.primary-key true
dab update Products \
--fields.name UnitsInStock \
--fields.description "Current inventory count available for purchase"
Stored Procedure Parameter Descriptions
dab add GetOrdersByDateRange \
--source dbo.usp_GetOrdersByDateRange \
--source.type stored-procedure \
--permissions "authenticated:execute" \
--description "Retrieves all orders placed within a specified date range" \
--parameters.name "StartDate,EndDate,CustomerID" \
--parameters.description "Beginning of date range (inclusive),End of date range (inclusive),Optional customer ID filter (null returns all customers)" \
--parameters.required "true,true,false" \
--parameters.default ",,null"
Description Best Practices
Do:
- Be specific: "Customer shipping address" not "Address"
- Include units: "Price in USD", "Weight in kilograms"
- Mention formats: "ISO 8601 date format", "E.164 phone format"
- Explain business rules: "Negative values indicate credit balance"
- Note optional fields: "Optional; null returns all results"
Don't:
- Use only technical jargon
- Duplicate field names: "ProductID is the product ID" adds no value
- Write novels - keep to 1-2 sentences
- Ignore nullable fields - mention when null has special meaning
Security & Authentication
Two Authentication Directions
Inbound (Client → SQL MCP Server): How AI agents authenticate to your MCP endpoint
Outbound (SQL MCP Server → Database): How DAB authenticates to your database
Outbound Authentication (to Database)
Option 1: SQL User/Password (Development)
dab init \
--database-type mssql \
--connection-string "@env('SQL_CONNECTION_STRING')"
Environment variable:
SQL_CONNECTION_STRING=Server=tcp:myserver.database.windows.net,1433;Database=mydb;User ID=myuser;Password=mypass;Encrypt=True;
Option 2: Managed Identity (Recommended for Azure)
Server=tcp:myserver.database.windows.net,1433;Database=mydb;Authentication=Active Directory Managed Identity;
For User-Assigned Managed Identity (UAMI):
Server=tcp:myserver.database.windows.net,1433;Database=mydb;Authentication=Active Directory Managed Identity;User Id=<uami-client-id>;
Inbound Authentication (from Clients)
Option 1: Anonymous (Development Only)
dab configure --runtime.host.authentication.provider AppService
Option 2: Microsoft Entra ID / JWT (Production)
dab configure \
--runtime.host.authentication.provider EntraId
dab configure \
--runtime.host.authentication.jwt.audience "api://<app-id>"
dab configure \
--runtime.host.authentication.jwt.issuer "https://login.microsoftonline.com/<tenant-id>/v2.0"
dab update Products --permissions "authenticated:read"
Option 3: API Gateway (Key-Based)
- SQL MCP Server doesn't support API keys directly
- Front the
/mcp endpoint with Azure API Management or similar gateway
- Gateway handles key validation, forwards to SQL MCP Server
RBAC (Role-Based Access Control)
Every DML tool operation enforces RBAC rules:
- Which entities are visible
- Which operations are allowed (create/read/update/delete/execute)
- Which fields are included/excluded
- Whether row-level policies apply
dab add Products --source dbo.Products --permissions "anonymous:read"
dab update Products --fields.exclude "Cost,Margin,SupplierID"
dab update Products --permissions "authenticated:*"
dab update Products --permissions "admin:*"
Deployment Scenarios
Local Development (VS Code)
Steps:
- Run
dab start in terminal
- Create
.vscode/mcp.json in workspace
- Configure MCP server in VS Code
- Test with Copilot Chat
MCP Config:
{
"servers": {
"sql-mcp-server": {
"type": "http",
"url": "http://localhost:5000/mcp"
}
}
}
Azure Container Apps
Key Steps:
- Create Azure SQL Database
- Configure
dab-config.json
- Create Dockerfile with embedded config
- Build and push to Azure Container Registry
- Deploy to Container Apps with connection string secret
- Get public MCP endpoint URL
Dockerfile:
FROM mcr.microsoft.com/azure-databases/data-api-builder:1.7.83-rc
COPY dab-config.json /App/dab-config.json
⚠️ ANTI-PATTERN: Never use Azure Files, storage accounts, or volume mounts for dab-config.json. Always build a custom Docker image with the config embedded and push to ACR. Storage mounts add latency, failure modes, and unnecessary complexity.
Deploy:
az containerapp create \
--name sql-mcp-server \
--resource-group rg-sql-mcp \
--environment sql-mcp-env \
--image <acr>.azurecr.io/sql-mcp-server:1 \
--target-port 5000 \
--ingress external \
--secrets "mssql-connection-string=<connection-string>" \
--env-vars "MSSQL_CONNECTION_STRING=secretref:mssql-connection-string"
.NET Aspire
Integration Pattern:
- Add DAB as Aspire component
- Configure via
appsettings.json or environment
- MCP endpoint available within Aspire service mesh
- Use with Aspire-hosted AI agents
Microsoft AI Foundry
Connection Steps:
- Deploy SQL MCP Server (Container Apps or other hosting)
- In Foundry project: Add a tool → Custom → Model Context Protocol
- Set remote MCP endpoint URL
- Configure authentication (Unauthenticated, Entra ID, or OAuth passthrough)
- Test with agent prompts
Common Conversational Patterns
User: "I want to give my AI agent database access"
Ask:
- Do you have a database set up?
- Have you heard of Data API Builder (DAB)?
- What database type? (SQL Server, PostgreSQL, MySQL, Cosmos DB)
- Where will the agent run? (VS Code, Azure AI Foundry, custom client)
- What tables/views should the agent access?
Guide through:
- Install DAB CLI 1.7+ (prerelease)
- Initialize config with database connection
- Add entities with descriptions
- Start locally for testing
- Deploy to Azure if needed
User: "How do I enable MCP on my existing DAB config?"
Answer:
- MCP is enabled by default in DAB 1.7+
- Just upgrade:
dotnet tool update microsoft.dataapibuilder --prerelease
- Restart with
dab start - /mcp endpoint is live
- Only configure if you want to restrict tools or entities
User: "My agent can't see my tables"
Troubleshoot:
- Is entity added to config? (
dab add)
- Does current role have permissions? (check
--permissions)
- Is entity excluded from MCP? (check
mcp.dml-tools: false)
- Is tool disabled globally? (check
runtime.mcp.dml-tools)
- Did agent call
describe_entities first?
User: "How do I prevent agents from deleting data?"
Options:
- Global:
dab configure --runtime.mcp.dml-tools.delete-record false
- Per-entity: Set
mcp.dml-tools.delete-record: false in entity config
- RBAC: Don't grant
delete action to agent's role
User: "Can agents write raw SQL?"
Answer:
- No. SQL MCP Server intentionally doesn't support NL2SQL.
- Agents use structured DML tools that generate deterministic SQL via DAB's query builder.
- This prevents SQL injection and ensures safety/predictability.
- Think of it as NL2DAB (natural language → DAB API → safe SQL).
User: "How do I add descriptions for better AI understanding?"
Guide:
- Entity level: Use
--description in dab add or dab update
- Field level: Use
--fields.name and --fields.description in dab update
- Parameters: Use
--parameters.name and --parameters.description for stored procedures
- Include units, formats, business rules, and context
Example:
dab update Products \
--fields.name UnitPrice \
--fields.description "Retail price per unit in USD (includes tax)"
User: "How do I deploy to production?"
Recommend:
- Azure Container Apps (simplest Azure hosting)
- Azure App Service (alternative)
- Kubernetes (advanced)
Key considerations:
- Use managed identity for database auth
- Enable Microsoft Entra ID for inbound auth
- Disable
delete-record tool if appropriate
- Configure CORS and rate limiting
- Enable Application Insights monitoring
- Store connection strings in Azure Key Vault
Validation & Testing
Validate Config Before Starting
dab validate && dab start
Validation checks:
- JSON schema correctness
- Entity configuration validity
- Database connectivity
- Environment variable resolution
- Permissions and policies
Test MCP Endpoint
Health check:
curl http://localhost:5000/health
List tools:
Test with AI Agent
VS Code Copilot Chat Examples:
"Which products have low inventory?"
"Show me all products under $50"
"What categories do we have?"
"How many units of Product X are in stock?"
Monitoring & Observability
Built-in Features
OpenTelemetry Tracing:
- Every DML tool operation is traced
- Correlate across distributed systems
- Export to Application Insights, Jaeger, etc.
Health Checks:
/health endpoint for liveness/readiness
- Per-entity health validation
- Performance threshold monitoring
Logging:
- Structured logs to Azure Log Analytics
- Application Insights integration
- Local file logs in containers
Enable Application Insights
dab configure --runtime.telemetry.application-insights.connection-string "@env('APPLICATIONINSIGHTS_CONNECTION_STRING')"
Migration & Upgrade Paths
Upgrading Existing DAB to MCP
Steps:
- Upgrade CLI:
dotnet tool update microsoft.dataapibuilder --prerelease
- Update config file version (if needed)
- Add entity/field descriptions for AI context
- Test MCP endpoint:
dab start
- Connect from MCP client
No breaking changes - MCP is additive to existing REST/GraphQL endpoints.
From Other MCP Database Solutions
Key differences:
- SQL MCP Server uses entity abstraction (safer than direct schema exposure)
- No NL2SQL - deterministic query generation only
- Built-in RBAC, caching, monitoring
- Single config for REST/GraphQL/MCP
Migration approach:
- Map existing schema to DAB entities
- Add semantic descriptions
- Configure equivalent permissions
- Test agent workflows
- Switch MCP client connection
Troubleshooting Guide
Problem: "MCP endpoint returns 404"
Checks:
- Is DAB version 1.7+? Run
dab --version
- Is MCP enabled? Check
runtime.mcp.enabled: true
- Correct path? Default is
/mcp, check runtime.mcp.path
- Did you restart after config changes?
Problem: "Agent can't see any entities"
Checks:
- Are entities added? Run
dab validate
- Does role have permissions? Check entity
permissions config
- Are entities excluded from MCP? Check
mcp.dml-tools: false
- Did agent call
describe_entities first?
Problem: "Connection string not resolving"
Checks:
- Is environment variable set? Check
echo $DATABASE_CONNECTION_STRING
- Using correct syntax?
@env('VAR_NAME')
- Is
.env file in working directory? (local dev)
- For Azure: Are secrets configured in Container Apps settings?
Problem: "Validation fails with database error"
Checks:
- Can you connect to database with connection string?
- Are tables/views/stored procedures spelled correctly?
- Do they exist in the specified schema? (e.g.,
dbo.Products)
- For views: Did you specify
--source.key-fields?
Problem: "Agent queries are slow"
Solutions:
- Enable caching:
dab update <entity> --cache.enabled true --cache.ttl 300
- Add database indexes on frequently queried columns
- Review Application Insights for slow queries
- Consider scaling up Container Apps CPU/memory
Problem: "Authentication fails from Foundry"
Checks:
- Does auth mode match? (Foundry setting vs DAB
runtime.host.authentication)
- For Entra ID: Are
audience and issuer correct?
- Is token being passed correctly?
- Check Container Apps logs for auth errors
Quick Reference: CLI Commands
Initialization
dab init --database-type mssql --connection-string "@env('CONNECTION_STRING')" --host-mode Development
Add Entities
dab add Products --source dbo.Products --permissions "anonymous:read" --description "Product catalog"
dab add ProductSummary --source dbo.vw_ProductSummary --source.type view --source.key-fields "ProductId" --permissions "anonymous:read"
dab add GetProducts --source dbo.usp_GetProducts --source.type stored-procedure --permissions "anonymous:execute" --graphql.operation query
Add Descriptions
dab update Products --description "Product catalog with pricing and inventory"
dab update Products --fields.name UnitPrice --fields.description "Retail price in USD"
dab update Products --fields.name ProductID --fields.description "Unique identifier" --fields.primary-key true
dab add GetOrdersByDate \
--source dbo.usp_GetOrdersByDate \
--source.type stored-procedure \
--permissions "authenticated:execute" \
--parameters.name "StartDate,EndDate" \
--parameters.description "Start date (inclusive),End date (inclusive)" \
--parameters.required "true,true"
Configure MCP
dab configure --runtime.mcp.enabled true
dab configure --runtime.mcp.path "/mcp"
dab configure --runtime.mcp.dml-tools.delete-record false
dab configure --runtime.mcp.description "Production inventory MCP endpoint"
Configure Authentication
dab configure --runtime.host.authentication.provider EntraId
dab configure --runtime.host.authentication.jwt.audience "api://<app-id>"
dab configure --runtime.host.authentication.jwt.issuer "https://login.microsoftonline.com/<tenant-id>/v2.0"
dab update Products --permissions "authenticated:*"
Validation & Start
dab validate
dab start
dab start --verbose
dab start --LogLevel Debug
Key Differences from Regular DAB
| Aspect | Regular DAB | SQL MCP Server |
|---|
| Primary Use | REST/GraphQL APIs | AI agent database access |
| Client Type | Web apps, mobile apps | AI agents, copilots |
| Protocol | HTTP REST, GraphQL | MCP over HTTP or stdio |
| Version | 1.0+ | 1.7+ (preview) |
| Endpoint | /api, /graphql | /mcp |
| Query Method | OData filters, GraphQL queries | DML tool calls |
| Descriptions | Optional | Critical for AI understanding |
| Default State | REST + GraphQL enabled | REST + GraphQL + MCP enabled |
When to Recommend SQL MCP Server
Good fit:
- User wants AI agents to access database safely
- User needs controlled CRUD operations for agents
- User requires RBAC for agent database access
- User wants caching and monitoring built-in
- User needs MCP-compatible database endpoint
Not a good fit:
- User needs raw SQL execution from agents (use NL2SQL tools instead)
- User needs schema modification (DDL) from agents
- User only needs REST or GraphQL APIs (use regular DAB)
Migration candidates:
- Existing DAB users adding AI agent capabilities
- Custom MCP database servers wanting enterprise features
- NL2SQL implementations wanting deterministic queries
References & Resources
Official Documentation
Quickstarts
DAB Core Documentation
Related Skills
- See
data-api-builder.md for comprehensive DAB CLI guidance
- See
dab-cli.skill.md for detailed command reference
Consistency Rules
- Always install prerelease version for v1.7+ features
- Require
.gitignore with .env, **\bin, and **\obj entries before adding secrets
- Descriptions are critical for AI agent success - always recommend adding them
- MCP is enabled by default - only configure when restricting
- Security first - recommend managed identity + Entra ID for production
- Use entity abstraction - never recommend exposing raw schema to agents
- Validate before start - always
dab validate && dab start
- Environment variables for secrets - use
@env('VAR_NAME') pattern
- No NL2SQL - explain the NL2DAB deterministic approach
- RBAC applies to all tools - every operation respects role permissions
- Stdio for local, HTTP for remote - use appropriate transport
Version Notes
Current State (v1.7.x-rc):
- SQL MCP Server is in preview
- Must use
--prerelease flag to install
- Documentation and implementation may change
- Not yet in
:latest Docker tag
When GA releases:
- Will be included in stable releases
:latest Docker tag will include MCP
- May have config schema changes
Always check: dab --version to confirm 1.7+