Copilot Studio Azure SQL MCP

Azure SQL MCP服务器-完整指南

将Microsoft Copilot Studio连接到Azure SQL数据库的模型上下文协议（MCP）服务器。支持12个工具，用于查询、CRUD操作、模式检查、搜索和图表可视化。

______________________________________________________________________

目录

1. 先决条件
2. 安装
3. 配置
4. 代码修复（重要）
5. 运行服务器
6. 接触互联网
7. Copilot工作室设置
8. 全部12个工具参考
9. CRUD操作
10. 图表可视化
11. 示例查询和用例
12. 生产部署
13. API密钥验证
14. 故障排除
15. 安全检查列表

______________________________________________________________________

建筑

alt text

1.先决条件

• Python 3.8+
• SQL Server的ODBC驱动程序18 — 点击此处下载
• Azure SQL数据库 包含服务器主机名、数据库名称、用户名和密码
• Cloudflare隧道 （用于本地测试）-- winget install Cloudflare.cloudflared

安装ODBC驱动程序

窗户： 从上面的链接下载并安装。

macOS：

brew tap microsoft/mssql-release https://github.com/Microsoft/homebrew-mssql-release
brew update
brew install msodbcsql18 mssql-tools18

Linux（Ubuntu/Debian）：

curl https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add -
curl https://packages.microsoft.com/config/ubuntu/$(lsb_release -rs)/prod.list | sudo tee /etc/apt/sources.list.d/mssql-release.list
sudo apt-get update
sudo ACCEPT_EULA=Y apt-get install -y msodbcsql18

______________________________________________________________________

2.安装

git clone 
cd azure-sql-mcp-server
python -m venv venv
venv\Scripts\activate          # Windows
# source venv/bin/activate     # macOS/Linux
pip install -r requirements.txt

______________________________________________________________________

3.配置

创建一个 .env 项目根目录中的文件：

AZURE_SQL_SERVER=your-server.database.windows.net
AZURE_SQL_DATABASE=your-database-name
AZURE_SQL_USERNAME=your-username
AZURE_SQL_PASSWORD=your-password
AZURE_SQL_DRIVER=ODBC Driver 18 for SQL Server

永不承诺 .env 版本控制。 添加到 .gitignore.

______________________________________________________________________

4.代码修复

MCP SDK需要特定的配置。将这3个修复应用于 azure_sql_mcp.py:

修复1：寿命函数签名

# ❌ BEFORE
@asynccontextmanager
async def app_lifespan():

# ✅ AFTER — FastMCP passes the server instance
@asynccontextmanager
async def app_lifespan(server: FastMCP):

修复2：构造函数上的主机和端口

# ❌ BEFORE
mcp = FastMCP("azure_sql_mcp", lifespan=app_lifespan)

# ✅ AFTER — host/port go on the constructor, NOT on run()
mcp = FastMCP("azure_sql_mcp", host="0.0.0.0", port=8000, lifespan=app_lifespan)

修复3:HTTP传输

# ❌ BEFORE
if __name__ == "__main__":
    mcp.run()

# ✅ AFTER — streamable-http (with hyphen) for Copilot Studio
if __name__ == "__main__":
    mcp.run(transport="streamable-http")

______________________________________________________________________

5.运行服务器

cd azure-sql-mcp-server
.\venv\Scripts\Activate.ps1       # Windows
python azure_sql_mcp.py

您应该看到：

INFO: Initializing Azure SQL MCP server...
INFO: Database connection established
INFO: Database connection verified
INFO: Uvicorn running on http://0.0.0.0:8000 (Press CTRL+C to quit)

MCP端点： http://localhost:8000/mcp

______________________________________________________________________

6.接触互联网

Copilot Studio需要一个公共HTTPS URL。 使用Cloudflare隧道 （免费，无需注册）。

为什么不是ngrok？ ngrok的免费版显示浏览器警告页面(ERR_NGROK_6024)阻止了像Copilot Studio这样的API客户端。

步骤

1. 安装（一次性）：

   winget install Cloudflare.cloudflared

安装后关闭并重新打开终端。

1. 在一个 新终端 （在第一个服务器中保持服务器运行）：

   cloudflared tunnel --url http://localhost:8000

1. 从输出中复制URL：

   https://electronic-annie-jose-spoken.trycloudflare.com

1. Copilot Studio的MCP服务器URL：

   https://electronic-annie-jose-spoken.trycloudflare.com/mcp

URL在重新启动时更改。对于永久URL，请部署到Azure应用服务（请参阅 生产部署).

______________________________________________________________________

7.副驾驶室设置

步骤1：添加MCP服务器

1. 首选 副驾驶工作室
2. 打开你的代理→ 工具 → 添加工具 → 新工具 → 模型上下文协议
3. 填写：

1. 点击 创建 → 下一步 → 创建新连接 → 添加和配置

步骤2：配置代理（概述选项卡）

这两个字段都是 必需的 --没有他们，代理人就无法工作。

说明：

Azure SQL Database Assistant that queries tables, retrieves data, inspects schema, manages records, and visualizes data with charts.

说明（单击编辑）：

You are an Azure SQL Database assistant. You help users interact with their database using natural language.

Your capabilities:
- List tables and describe their schema
- Execute SQL queries (SELECT, INSERT, UPDATE, DELETE)
- Search for data across table columns
- Create and drop tables
- Visualize data as charts (bar, pie, line, doughnut)
- Provide database information and statistics

Rules:
- Always use the MCP tools to answer database questions - never guess table names or data
- Before querying, list tables first if you don't know the schema
- Use parameterized queries when possible
- Ask for confirmation before UPDATE, DELETE, or DROP operations
- Format results clearly for the user
- When asked for charts, pick the most appropriate chart type based on the data

步骤3：发布和测试

点击 发布，等待一分钟，然后使用以下提示进行测试：

• *“显示数据库中的所有表”*
• *“客户表的模式是什么？”*
• *“从订单中获取前10行”*
• *“每个表中有多少条记录？”*
• *“以柱状图形式显示按地区划分的销售额”*

______________________________________________________________________

8.所有12个工具参考

所有工具均支持 markdown 和 json 响应格式。

______________________________________________________________________

9.CRUD操作

创建-- azure_sql_create_record

{
  "table_name": "customers",
  "data": {
    "name": "John Doe",
    "email": "john@example.com",
    "city": "Seattle"
  }
}

自然语言： *“通过电子邮件添加一位名为John Doe的新客户john@example.com"*

______________________________________________________________________

阅读-- azure_sql_execute_query

{
  "query": "SELECT * FROM customers WHERE city = ?",
  "params": ["Seattle"],
  "response_format": "markdown"
}

自然语言： *“显示所有来自西雅图的客户”*

______________________________________________________________________

更新-- azure_sql_update_record

{
  "table_name": "customers",
  "data": { "email": "newemail@example.com" },
  "where": { "id": 123 }
}

安全： WHERE子句是 必需的 --防止意外的大规模更新。

自然语言： *“将客户123的电子邮件更新至newemail@example.com"*

______________________________________________________________________

删除-- azure_sql_delete_record

{
  "table_name": "customers",
  "where": { "id": 999 }
}

安全： WHERE子句是 必需的 --防止意外批量删除。

自然语言： *“删除ID为999的客户”*

______________________________________________________________________

搜索-- azure_sql_search

{
  "table_name": "customers",
  "search_term": "john",
  "columns": ["name", "email"],
  "limit": 50
}

如果 columns 如果省略，则自动搜索所有文本列。

自然语言： *“在客户表中搜索'john'”*

______________________________________________________________________

创建表格-- azure_sql_create_table

{
  "table_name": "employees",
  "columns": [
    { "name": "id", "type": "INT", "primary_key": true, "identity": true },
    { "name": "name", "type": "NVARCHAR(100)", "nullable": false },
    { "name": "email", "type": "NVARCHAR(255)" },
    { "name": "hire_date", "type": "DATE" },
    { "name": "salary", "type": "DECIMAL(10,2)" }
  ],
  "if_not_exists": true
}

列属性： name, type, primary_key, identity, nullable, default.

自然语言： *创建一个包含id、姓名、电子邮件和电话列的客户表*

______________________________________________________________________

升降台-- azure_sql_drop_table

"old_backup_table"

用途 DROP TABLE IF EXISTS --如果表不存在，则不会出错。

自然语言： *“删除旧的_backup_table”*

______________________________________________________________________

10.图表可视化

工具： azure_sql_visualize_data

通过QuickChart API生成图表，并返回在Copilot Studio中直接渲染的自适应卡。

参数

图表类型

示例：条形图

{
  "query": "SELECT region, SUM(sales) as total FROM orders GROUP BY region ORDER BY total DESC",
  "chart_type": "bar",
  "title": "Sales by Region",
  "label_column": "region",
  "value_column": "total"
}

示例：饼图

{
  "query": "SELECT category, COUNT(*) as count FROM products GROUP BY category",
  "chart_type": "pie",
  "title": "Products by Category",
  "label_column": "category",
  "value_column": "count"
}

示例：折线图（趋势）

{
  "query": "SELECT FORMAT(order_date, 'yyyy-MM') as month, SUM(total) as revenue FROM orders WHERE order_date >= DATEADD(month, -6, GETDATE()) GROUP BY FORMAT(order_date, 'yyyy-MM') ORDER BY month",
  "chart_type": "line",
  "title": "Revenue Trend (Last 6 Months)",
  "label_column": "month",
  "value_column": "revenue"
}

副驾驶显示什么

每个图表都会返回一张带有图表图像的自适应卡，以及自动统计数据：总计、平均、最高（带标签）、最低（有标签）和数据点计数。

图表的最佳实践

• 条形图： 3–15个类别，按DESC值排序
• 饼图： 3-8个切片，按DESC值排序
• 折线图： 5-50分，按日期/时间ASC排序
• 使用明确的列别名： SUM(sales) as total_sales 不 SUM(s)
• 使用测试您的查询 azure_sql_execute_query 首先，然后想象

______________________________________________________________________

11.示例查询和用例

数据探索

"Show me all tables in the database"
"What columns does the orders table have?"
"Show me 10 sample products"

数据分析

"How many orders were placed last month?"
"Which customer has the highest order total?"
"What's the average product price by category?"
"Show me sales trends for the last 6 months"

数据质量

"Are there any customers with missing email addresses?"
"Find duplicate customer records"
"Show me orders with invalid status values"

参数化查询（SQL注入安全）

{
  "query": "SELECT * FROM customers WHERE city = ? AND status = ?",
  "params": ["Seattle", "active"],
  "response_format": "json"
}

聚合

{
  "query": "SELECT category, COUNT(*) as product_count, AVG(price) as avg_price FROM products GROUP BY category ORDER BY product_count DESC"
}

连接

{
  "query": "SELECT c.customer_name, COUNT(o.order_id) as order_count FROM customers c LEFT JOIN orders o ON c.customer_id = o.customer_id GROUP BY c.customer_name ORDER BY order_count DESC"
}

基于时间的查询

-- Daily (last 30 days)
SELECT CAST(order_date AS DATE) as day, SUM(total) as revenue
FROM orders WHERE order_date >= DATEADD(day, -30, GETDATE())
GROUP BY CAST(order_date AS DATE) ORDER BY day

-- Monthly
SELECT FORMAT(order_date, 'yyyy-MM') as month, SUM(total) as revenue
FROM orders WHERE YEAR(order_date) = YEAR(GETDATE())
GROUP BY FORMAT(order_date, 'yyyy-MM') ORDER BY month

-- Quarterly
SELECT 'Q' + CAST(DATEPART(quarter, order_date) AS VARCHAR) as quarter, SUM(total) as revenue
FROM orders WHERE YEAR(order_date) = YEAR(GETDATE())
GROUP BY DATEPART(quarter, order_date) ORDER BY DATEPART(quarter, order_date)

完成CRUD工作流

1. 创建表 → azure_sql_create_table
2. 插入数据 → azure_sql_create_record
3. 搜索 → azure_sql_search
4. 更新 → azure_sql_update_record
5. 可视化 → azure_sql_visualize_data
6. 清理 → azure_sql_delete_record 或 azure_sql_drop_table

______________________________________________________________________

12.生产部署

Azure应用服务

1. 创建部署文件：

runtime.txt:

   python-3.11

Procfile:

   web: python azure_sql_mcp.py

1. 部署：

   az login
   az group create --name mcp-servers --location eastus
   az appservice plan create --name mcp-plan --resource-group mcp-servers --sku B1 --is-linux
   az webapp create --name azure-sql-mcp --resource-group mcp-servers --plan mcp-plan --runtime "PYTHON:3.11"

   az webapp config appsettings set --name azure-sql-mcp --resource-group mcp-servers --settings \
       AZURE_SQL_SERVER="your-server.database.windows.net" \
       AZURE_SQL_DATABASE="your-database" \
       AZURE_SQL_USERNAME="your-username" \
       AZURE_SQL_PASSWORD="your-password" \
       AZURE_SQL_DRIVER="ODBC Driver 18 for SQL Server" \
       PORT="8000"

   az webapp up --name azure-sql-mcp --resource-group mcp-servers

1. 永久URL: https://azure-sql-mcp.azurewebsites.net/mcp

码头工人

FROM python:3.11-slim

RUN apt-get update && apt-get install -y curl apt-transport-https gnupg2 \
    && curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add - \
    && curl https://packages.microsoft.com/config/debian/11/prod.list > /etc/apt/sources.list.d/mssql-release.list \
    && apt-get update \
    && ACCEPT_EULA=Y apt-get install -y msodbcsql18 \
    && apt-get clean && rm -rf /var/lib/apt/lists/*

WORKDIR /app
COPY requirements.txt .
RUN pip install --no-cache-dir -r requirements.txt
COPY azure_sql_mcp.py .

EXPOSE 8000
CMD ["python", "azure_sql_mcp.py"]

docker build -t azure-sql-mcp .
docker run -p 8000:8000 --env-file .env azure-sql-mcp

______________________________________________________________________

13.API密钥认证

对于生产，添加API关键中间件来保护您的服务器。

步骤1：添加中间件 azure_sql_mcp.py

将此添加到Pydantic模型部分上方：

from starlette.middleware.base import BaseHTTPMiddleware
from starlette.requests import Request
from starlette.responses import JSONResponse

MCP_API_KEY = os.getenv("MCP_API_KEY", "")

class APIKeyMiddleware(BaseHTTPMiddleware):
    async def dispatch(self, request: Request, call_next):
        if not MCP_API_KEY:
            return await call_next(request)
        api_key = request.headers.get("X-API-Key", "")
        if api_key != MCP_API_KEY:
            return JSONResponse(status_code=401, content={"error": "Invalid API key"})
        return await call_next(request)

步骤2：更新入口点

if __name__ == "__main__":
    app = mcp.streamable_http_app()
    app.add_middleware(APIKeyMiddleware)

    import uvicorn
    port = int(os.getenv("PORT", 8000))
    uvicorn.run(app, host="0.0.0.0", port=port)

步骤3：添加到 .env

MCP_API_KEY=your-secret-api-key-here

生成强密钥： python -c "import secrets; print(secrets.token_urlsafe(32))"

步骤4：在Copilot Studio中配置

创建连接时输入相同的键值。

______________________________________________________________________

14.故障排除

检查已安装的ODBC驱动程序

# Windows (PowerShell)
Get-OdbcDriver

# macOS/Linux
odbcinst -q -d

______________________________________________________________________

15.安全检查表

• \[ \] .env 文件在 .gitignore （从不提交凭据）
• \[\]服务器URL使用HTTPS
• \[\]已为生产启用API密钥身份验证
• \[\]用于用户输入的参数化查询
• \[\]Azure SQL防火墙限制对已知IP的访问
• \[\]数据库用户具有最低权限
• \[\]已启用Azure SQL审核
• \[\]定期轮换API密钥和密码
• \[\]不允许使用多个SQL语句（内置）
• \[\]UPDATE/DELETE（内置）需要WHERE子句

______________________________________________________________________

项目结构

azure-sql-mcp-server/
├── azure_sql_mcp.py           # Main MCP server (all 12 tools)
├── requirements.txt           # Python dependencies
├── .env.example               # Environment variables template
├── .env                       # Your config (not in git)
├── AZURE_SQL_MCP_GUIDE.md     # This file
└── .gitignore

______________________________________________________________________

添加自定义工具

class CustomInput(BaseModel):
    param1: str = Field(..., description="Parameter description")
    response_format: ResponseFormat = Field(default=ResponseFormat.MARKDOWN)

@mcp.tool(
    name="azure_sql_custom_tool",
    annotations={
        "title": "Custom Tool",
        "readOnlyHint": True,
        "destructiveHint": False,
        "idempotentHint": True,
        "openWorldHint": False
    }
)
async def custom_tool(params: CustomInput) -> str:
    """Tool description."""
    try:
        results = execute_query("SELECT ...")
        if params.response_format == ResponseFormat.JSON:
            return json.dumps(results, indent=2, default=str)
        return "**Results**\n..."
    except Exception as e:
        return _handle_db_error(e)

______________________________________________________________________

更新日志

v2.0.0--图表可视化

• 添加 azure_sql_visualize_data 工具（6种图表类型）
• Copilot Studio的自适应卡输出
• 自动统计（总计、平均、最小、最大）
• QuickChart API集成（不需要API密钥）

v1.0.0--初始版本

• 5个核心工具：execute_query、list_tables、get_table_schema、get_table_data、get_tabase_info
• Pydantic验证、参数化查询、双输出格式

v2.1.0——完全CRUD+部署

• 新增7个工具：创建记录、更新记录、删除记录、搜索、创建表、删除表、可视化数据
• Cloudflare隧道支持（取代ngrok）
• API密钥认证中间件
• Copilot Studio代理配置（描述+说明）