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

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

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

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

# ❌ BEFORE
@asynccontextmanager
async def app_lifespan():

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

# ❌ 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)

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

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

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)

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

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

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

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

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

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

{
  "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
}

"old_backup_table"

{
  "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"
}

"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"

{
  "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)

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

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)

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)

MCP_API_KEY=your-secret-api-key-here

# Windows (PowerShell)
Get-OdbcDriver

# macOS/Linux
odbcinst -q -d

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)