MCP: Connecting AI to Your Database
Use Model Context Protocol to connect AI agents and tools directly to SQL Server and Fabric databases — giving them schema awareness and the ability to query data safely.
Giving AI tools direct access to your database
Imagine giving a new colleague a tour of the office.
Without a tour, they wander around guessing where things are. With a tour, they know: “Customer data is in this cabinet, financial reports are over there, and you need a key for the vault.” That is what MCP does for AI tools — it gives them a guided tour of your database so they know what tables exist, what columns they contain, and how to query them properly.
MCP (Model Context Protocol) is an open standard that connects AI tools (like GitHub Copilot or custom agents) to data sources. For SQL, it means Copilot can see your actual schema and write queries that work with your real tables — not generic guesses.
What is MCP?
MCP follows a client-server architecture:
| Component | Role | Examples |
|---|---|---|
| MCP Client | The AI tool that consumes data | GitHub Copilot, VS Code, Copilot Studio, Foundry agents |
| MCP Server | Exposes data and tools via the MCP protocol | SQL MCP Server (via Data API Builder), Fabric MCP Server |
| Transport | How client and server communicate | HTTP/SSE (remote), stdio (local) |
The MCP server tells the client: “Here are the tables I have, here are the columns, and here are the queries you can run.” The client uses this information to generate accurate, schema-aware SQL.
SQL MCP Server (via Data API Builder)
Microsoft provides an MCP server for SQL databases built on Data API Builder (DAB). It exposes your database schema and query capabilities to any MCP-compatible client.
How it works
┌─────────────────┐ MCP Protocol ┌──────────────────┐
│ GitHub Copilot │ ◄──────────────────► │ SQL MCP Server │
│ (MCP Client) │ │ (Data API │
│ │ │ Builder) │
└─────────────────┘ └────────┬─────────┘
│
┌────────▼─────────┐
│ SQL Server / │
│ Azure SQL / │
│ Fabric SQL DB │
└──────────────────┘Connecting to SQL MCP Server endpoints
- Install the SQL MCP Server (part of Data API Builder tooling)
- Configure the connection — specify your SQL Server endpoint and authentication
- Register in your MCP client — add the server endpoint to Copilot or VS Code settings
In VS Code settings (.vscode/mcp.json or user settings):
{
"mcpServers": {
"sql-server": {
"type": "http",
"url": "http://localhost:5000/mcp",
"headers": {
"Authorization": "Bearer ${env:SQL_MCP_TOKEN}"
}
}
}
}What the SQL MCP Server exposes
| MCP Capability | What It Provides |
|---|---|
| Resources | Database schema — tables, views, columns, data types, relationships |
| Tools | CRUD operations — query, create, update, delete according to configured entity permissions |
| Prompts | Pre-built prompt templates for common SQL tasks |
Connecting to Fabric lakehouse
For Fabric SQL databases and lakehouses, MCP connectivity allows AI tools to understand and query your Fabric data:
- Fabric workspace must have Copilot enabled
- SQL analytics endpoint provides the connection for MCP
- Authentication uses Microsoft Entra ID (Azure AD)
The Fabric MCP endpoint gives AI tools access to:
- Lakehouse tables and views
- SQL database objects
- Data warehouse schemas
Configuring MCP tool options in Copilot
When using MCP with GitHub Copilot Chat, you can:
Select tools
In the chat panel, use @ to reference available MCP tools:
@sql-server— query your connected SQL database@fabric— query your Fabric lakehouse
Configure model options
Switch between available AI models in the chat session:
- Different models have different strengths (speed vs reasoning)
- Some models handle SQL better than others
- Model selection affects token limits and response quality
| Feature | Without MCP | With MCP |
|---|---|---|
| Schema awareness | Copilot guesses table/column names | Copilot knows your exact schema |
| Query accuracy | Generic suggestions, often wrong table names | Schema-accurate queries using real tables |
| Data type awareness | May suggest wrong data types | Knows column types, suggests appropriate functions |
| Relationship awareness | Cannot know foreign key relationships | Understands JOINs between your tables |
| Security | No access control on what Copilot sees | MCP server controls which objects are exposed |
Scenario: Leo's MCP setup at SearchWave
Leo Torres at SearchWave connects the SQL MCP Server to his VS Code instance. Now when he asks Copilot: “Write a query to find the top 10 products by search frequency this month,” Copilot generates a query using SearchWave’s actual table names (search_events, products, search_metrics) with correct column names and JOINs.
Without MCP, Copilot would have guessed generic names like Products and Searches — requiring Leo to fix every table and column reference manually.
Leo also connects to the Fabric lakehouse MCP endpoint for analytics queries against the company’s data warehouse, giving Copilot visibility into both the OLTP database and the analytics layer.
Exam tip: MCP security boundaries
The MCP server controls what the AI client can see and do. Key security principles:
- Least privilege — configure DAB entities to expose only the tables, views, and operations the AI needs
- Read-only when appropriate — restrict entities to read operations unless write access is explicitly needed
- Authentication — MCP connections should use managed identity or token-based auth, not embedded credentials
- Audit — log all queries executed through MCP for compliance
The exam may ask about securing MCP endpoints — the answer usually involves restricting which objects are exposed and using proper authentication.
Dev at PixelForge Studios wants GitHub Copilot to generate accurate queries against the company's SQL database. Currently, Copilot suggests generic table names that do not match the actual schema. What should Dev configure?
Next up: Encryption: Always Encrypted and Column-Level — protect sensitive data at rest and in transit with SQL Server’s encryption features.