sql-server-mcp

aagirre92/sql-server-mcp

3.1

If you are the rightful owner of sql-server-mcp and would like to certify it and/or have it hosted online, please leave a comment on the right or send an email to dayong@mcphub.com.

A Model Context Protocol (MCP) server designed for seamless interaction with a SQL Server database, enabling efficient management of people records.

Tools
3
Resources
0
Prompts
0

SQL Server MCP Server

A Model Context Protocol (MCP) server that provides tools for interacting with a SQL Server database. This server allows you to query, add, and delete people records from a SQL Server database through MCP-compatible clients.

Features

  • Query People: Search for people in the database by name or retrieve all records
  • Add Person: Insert new person records with name, surname, and birthdate
  • Delete Person: Remove person records based on name, surname, and birthdate
  • Optimized Connection Management: Uses FastMCP's lifespan management for efficient database connections
  • Type Safety: Fully typed with Pydantic models and proper error handling

Prerequisites

Database Requirements

  • SQL Server instance (local or remote)
  • Database with a people table
  • ODBC Driver 18 for SQL Server installed

Required Table Schema

Your SQL Server database should have a people table with the following structure:

CREATE TABLE people (
    id INT IDENTITY(1,1) PRIMARY KEY,
    name NVARCHAR(100) NOT NULL,
    surname NVARCHAR(100) NOT NULL,
    birthdate DATE NOT NULL
);

ODBC Driver Installation

Download and install the ODBC Driver 18 for SQL Server from: https://learn.microsoft.com/en-us/sql/connect/odbc/download-odbc-driver-for-sql-server

Installation

  1. Clone or download this repository
  2. Install dependencies using uv (recommended) or pip:
# Using uv (recommended)
uv sync

# Or using pip
pip install -r requirements.txt

Configuration

Create a .env file in the project root with your SQL Server connection details:

MSSQL_SERVER=your-server-name-or-ip
MSSQL_DATABASE=your-database-name
MSSQL_USER=your-username
MSSQL_PASSWORD=your-password

Example .env file:

MSSQL_SERVER=localhost
MSSQL_DATABASE=TestDB
MSSQL_USER=sa
MSSQL_PASSWORD=YourPassword123!

Usage

Running the Server

python server.py

The server will start and connect to your SQL Server database. It uses FastMCP's lifespan management to maintain a persistent database connection throughout the server's lifetime.

Available Tools

1. Query People

Search for people in the database:

  • Without filter: Returns all people in the database
  • With name filter: Returns people matching the specified name
2. Add Person

Add a new person to the database with:

  • Name (required)
  • Surname (required)
  • Birthdate (required, in YYYY-MM-DD format)
3. Delete Person

Remove a person from the database by providing:

  • Name (required)
  • Surname (required)
  • Birthdate (required, in YYYY-MM-DD format)

Note: If multiple records match the criteria, all will be deleted.

How It Works: Technical Deep Dive

This section explains the technical concepts and architecture for Python newcomers.

Understanding the Code Structure

1. Imports and Dependencies
import os
import pyodbc
from datetime import date
from collections.abc import AsyncIterator
from contextlib import asynccontextmanager
from dataclasses import dataclass
from fastmcp import FastMCP, Context
from dotenv import load_dotenv
from pydantic import BaseModel, Field

What each import does:

  • os: Provides access to environment variables (like database credentials)
  • pyodbc: Python library for connecting to SQL Server databases
  • datetime.date: For handling date objects (birthdates)
  • AsyncIterator: Type hint for asynchronous iterators (advanced Python feature)
  • asynccontextmanager: Decorator for creating async context managers (explained below)
  • dataclass: Decorator for creating simple data classes
  • FastMCP, Context: Core MCP server framework components
  • load_dotenv: Loads environment variables from .env file
  • BaseModel, Field: Pydantic classes for data validation
2. Environment Variables Loading
load_dotenv()

This line reads your .env file and makes the database credentials available through os.getenv(). This is a security best practice - never hardcode passwords in your source code!

3. Database Connection Class
class DatabaseConnection:
    """Database connection manager for SQL Server."""
    
    def __init__(self):
        self.connection = None
    
    async def connect(self) -> "DatabaseConnection":
        """Connect to SQL Server database."""
        self.connection = pyodbc.connect(
            f"DRIVER={{ODBC Driver 18 for SQL Server}};"
            f"SERVER={os.getenv('MSSQL_SERVER')};"
            f"DATABASE={os.getenv('MSSQL_DATABASE')};"
            f"UID={os.getenv('MSSQL_USER')};"
            f"PWD={os.getenv('MSSQL_PASSWORD')};"
            "Encrypt=yes;TrustServerCertificate=no;Connection Timeout=30;"
        )
        return self

Understanding this class:

  • __init__: Constructor that initializes the connection as None
  • async def connect(): Asynchronous function that establishes database connection
  • Why async? Database connections can take time, and async allows other operations to continue while waiting
  • Connection string: Tells pyodbc how to connect to SQL Server with your credentials
  • Security settings: Encrypt=yes ensures data is encrypted in transit
4. Application Context (Dependency Injection)
@dataclass
class AppContext:
    """Application context with typed dependencies."""
    db: DatabaseConnection

What is this?

  • A dataclass is a simple way to create a class that just holds data
  • This stores our database connection so all tools can access it
  • Dependency injection: Instead of each tool creating its own connection, they all share this one
5. Lifespan Management (The Magic Behind Efficiency)
@asynccontextmanager
async def app_lifespan(server: FastMCP) -> AsyncIterator[AppContext]:
    """Manage application lifecycle with type-safe context."""
    # Initialize on startup
    db = await DatabaseConnection().connect()
    try:
        yield AppContext(db=db)
    finally:
        # Cleanup on shutdown
        await db.disconnect()

Understanding async context managers:

  • What's happening: This function runs when the server starts and stops
  • @asynccontextmanager: Decorator that makes this function a context manager
  • await DatabaseConnection().connect(): Wait for database connection to establish
  • yield AppContext(db=db): Provide the database connection to all tools
  • try/finally: Ensures database cleanup happens even if something goes wrong
  • Why this matters: One connection for the entire server lifetime instead of connecting/disconnecting for each operation
6. Server Initialization
mcp = FastMCP("MSSQL MCP Server", lifespan=app_lifespan)

This creates the MCP server and tells it to use our lifespan manager for database connections.

7. Data Models (Type Safety)
class Person(BaseModel):
    name: str = Field(..., description="Name of the person")
    surname: str = Field(..., description="Surname of the person")
    birthdate: date = Field(..., description="Birthdate of the person")

Understanding Pydantic models:

  • BaseModel: Provides automatic data validation
  • Field(...): The ... means "required field"
  • Type hints: str and date tell Python what type of data to expect
  • Validation: Pydantic automatically validates that names are strings and birthdates are valid dates
8. MCP Tools (The Main Functions)
Query Tool Example:
@mcp.tool()
def query_people(ctx: Context, name: str = None) -> list[Person]:
    """Query people from the database."""
    db = ctx.request_context.lifespan_context.db
    cursor = db.get_cursor()
    # ... database operations

Breaking this down:

  • @mcp.tool(): Decorator that registers this function as an MCP tool
  • ctx: Context: The context contains our database connection
  • ctx.request_context.lifespan_context.db: Path to access our shared database connection
  • cursor = db.get_cursor(): Get a cursor to execute SQL commands
  • Return type list[Person]: Tells Python this returns a list of Person objects

Connection Flow Diagram

Server Startup:
1. load_dotenv() → Load .env file
2. app_lifespan() → Create database connection
3. yield AppContext(db) → Make connection available

Tool Execution:
1. Client calls tool → FastMCP receives request
2. Tool accesses ctx.request_context.lifespan_context.db → Get shared connection
3. Execute SQL → Use cursor to run database commands
4. Return results → Send back to client

Server Shutdown:
1. finally block → Close database connection
2. Clean exit → All resources freed

Why This Architecture Is Efficient

Traditional approach (inefficient):

Tool 1: Connect → Query → Disconnect
Tool 2: Connect → Insert → Disconnect  
Tool 3: Connect → Delete → Disconnect

Our optimized approach:

Startup: Connect once
Tool 1: Query (reuse connection)
Tool 2: Insert (reuse connection)
Tool 3: Delete (reuse connection)
Shutdown: Disconnect once

Async vs Sync: When and Why

Synchronous (blocking):

def slow_operation():
    time.sleep(5)  # Blocks everything for 5 seconds
    return "done"

Asynchronous (non-blocking):

async def fast_operation():
    await asyncio.sleep(5)  # Other things can run during these 5 seconds
    return "done"

In our server:

  • Database connections use async because they might take time
  • The server can handle multiple requests while one is waiting for database
  • Better performance and responsiveness

Error Handling and Security

SQL Injection Prevention:

# ❌ DANGEROUS - Don't do this:
cursor.execute(f"SELECT * FROM people WHERE name = '{name}'")

# ✅ SAFE - Parameterized query:
cursor.execute("SELECT * FROM people WHERE name = ?", (name,))

Environment Variables for Security:

  • Database passwords stored in .env file (not in code)
  • .env should be added to .gitignore (never commit passwords)
  • Connection encryption enabled (Encrypt=yes)

Practical Examples: Understanding the Code Flow

Example 1: Adding a Person - Step by Step

When you call the add_person tool, here's exactly what happens:

@mcp.tool()
def add_person(ctx: Context, person: Person) -> str:
    # Step 1: Get the shared database connection
    db = ctx.request_context.lifespan_context.db
    
    # Step 2: Create a cursor for executing SQL
    cursor = db.get_cursor()
    
    # Step 3: Execute parameterized SQL (prevents SQL injection)
    cursor.execute(
        "INSERT INTO people (name, surname, birthdate) VALUES (?, ?, ?)",
        (person.name, person.surname, person.birthdate)
    )
    
    # Step 4: Commit the transaction (save changes)
    db.connection.commit()
    
    # Step 5: Clean up the cursor (but keep connection open)
    cursor.close()
    
    # Step 6: Return success message
    return f"Person {person.name} {person.surname} added successfully mate"

What happens behind the scenes:

  1. Input validation: Pydantic ensures person has valid name, surname, and birthdate
  2. Connection reuse: No time wasted creating new database connection
  3. Safe SQL: The ? placeholders prevent malicious SQL injection
  4. Transaction: commit() makes the changes permanent
  5. Resource management: Cursor is closed but connection stays open for next operation
Example 2: Understanding Context Access

The path ctx.request_context.lifespan_context.db might look complex, but here's what each part means:

ctx                          # The context object passed to every tool
  .request_context          # Information about this specific request
    .lifespan_context       # Data that lives for the entire server lifetime
      .db                   # Our database connection object

Think of it like a file path: ctx → request_context → lifespan_context → db

Example 3: Error Handling in Practice

Here's how you might enhance error handling:

@mcp.tool()
def safe_add_person(ctx: Context, person: Person) -> str:
    try:
        db = ctx.request_context.lifespan_context.db
        cursor = db.get_cursor()
        
        cursor.execute(
            "INSERT INTO people (name, surname, birthdate) VALUES (?, ?, ?)",
            (person.name, person.surname, person.birthdate)
        )
        db.connection.commit()
        cursor.close()
        
        return f"Person {person.name} {person.surname} added successfully!"
        
    except pyodbc.IntegrityError as e:
        return f"Error: Duplicate person or invalid data - {str(e)}"
    except pyodbc.Error as e:
        return f"Database error: {str(e)}"
    except Exception as e:
        return f"Unexpected error: {str(e)}"

Learning Path: Python Concepts to Master

If you're new to Python and want to understand this code better, study these concepts in order:

Beginner Level:

  1. Classes and Objects - Understanding class DatabaseConnection
  2. Functions and Parameters - How def query_people(name: str = None) works
  3. Environment Variables - Why we use os.getenv('MSSQL_SERVER')
  4. String Formatting - Understanding f-strings like f"Person {person.name}"

Intermediate Level:

  1. Type Hints - What -> list[Person] means
  2. Decorators - How @mcp.tool() modifies functions
  3. Context Managers - Understanding with statements and try/finally
  4. Exception Handling - Using try/except blocks

Advanced Level:

  1. Async Programming - Understanding async def and await
  2. Data Classes - How @dataclass works
  3. Pydantic Models - Advanced data validation
  4. Dependency Injection - The context pattern we use

Database Concepts:

  1. SQL Basics - SELECT, INSERT, DELETE statements
  2. Parameterized Queries - Preventing SQL injection
  3. Database Connections - Connection strings and cursors
  4. Transactions - When to use commit() and rollback()

Dependencies

  • fastmcp: MCP server framework
  • pyodbc: SQL Server database connectivity
  • python-dotenv: Environment variable management
  • pydantic: Data validation and serialization

Security Considerations

  • Environment variables are used for database credentials
  • SQL injection protection through parameterized queries
  • Encrypted connections to SQL Server (TrustServerCertificate=no)
  • Connection timeout settings for reliability

Troubleshooting

Common Issues

  1. ODBC Driver Not Found

    • Ensure ODBC Driver 18 for SQL Server is installed
    • Check driver name in connection string matches installed version

  2. Connection Failed

    • Verify SQL Server is running and accessible
    • Check firewall settings
    • Validate credentials in .env file
    • Ensure database exists

  3. Table Not Found

    • Create the people table with the required schema
    • Verify database name in .env file

  4. Permission Denied

    • Ensure database user has SELECT, INSERT, DELETE permissions on people table
    • Check user authentication method (SQL Server or Windows authentication)

Development

To modify or extend this server:

  1. Add new tools by decorating functions with @mcp.tool()
  2. Use the ctx: Context parameter to access the database connection
  3. Access the database via ctx.request_context.lifespan_context.db
  4. Follow the existing pattern for cursor management

License

This project is open source. Please check the license file for details.

Contributing

  1. Fork the repository
  2. Create a feature branch
  3. Make your changes
  4. Test thoroughly
  5. Submit a pull request

Support

For issues and questions:

  • Check the troubleshooting section above
  • Review SQL Server and MCP documentation
  • Open an issue in the repository