MCP SQL Server

A FastMCP server that provides SQL database interaction tools via a conversational AI interface.

Overview

This project creates a server that exposes MS SQL Server operations through a conversational AI interface. It uses the FastMCP framework to provide tools for querying and manipulating SQL data, allowing users to interact with databases using natural language.

Features

Execute SQL queries and view results
List available tables in the database
Describe table structure with column information
Execute non-query operations (INSERT, UPDATE, DELETE)
List available ODBC drivers on the system
View database information and server details

Requirements

Python 3.7+
pyodbc
asyncio
FastMCP framework
Microsoft SQL Server
ODBC Driver 17 for SQL Server

Installation

Install Python dependencies:

pip install pyodbc asyncio fastmcp

Ensure you have Microsoft SQL Server installed and the ODBC Driver 17 for SQL Server.
Configure the connection settings in the script:

# Connection parameters
SERVER = "server\\instance"  # Change to your SQL Server instance
DATABASE = "db_name"              # Change to your database name

Usage

Run the server:

python mcp_sql_server.py

The server will initialize and establish a connection to the specified SQL Server database.

Available Tools

query_sql

Execute a SQL query and return the results.

query_sql(query: str = None) -> str

If no query is provided, it defaults to SELECT * FROM [dbo].[Table_1]
Returns query results as a formatted string

list_tables

List all tables available in the database.

list_tables() -> str

Returns a list of table names as a string

describe_table

Get the structure of a specific table.

describe_table(table_name: str) -> str

table_name: Name of the table to describe
Returns column information including names and data types

execute_nonquery

Execute INSERT, UPDATE, DELETE or other non-query SQL statements.

execute_nonquery(sql: str) -> str

sql: The SQL statement to execute
Returns operation results, including number of affected rows
Automatically handles transactions (commit/rollback)

list_odbc_drivers

List all available ODBC drivers on the system.

list_odbc_drivers() -> str

Returns a comma-separated list of installed ODBC drivers

database_info

Get general information about the connected database.

database_info() -> str

Returns server name, database name, SQL Server version, current server time, and table count

Architecture

The server uses an asynchronous architecture to avoid blocking operations:

Lifecycle Management: The app_lifespan context manager handles database connection setup and teardown.
Non-blocking Operations: Database operations run in a separate thread using asyncio.get_event_loop().run_in_executor() to prevent blocking the main event loop.
Error Handling: All operations include comprehensive error handling with useful error messages.

Error Handling

The server handles various error conditions:

Database connection failures
SQL query syntax errors
Table not found errors
Permission-related issues

All errors are logged and appropriate error messages are returned to the client.

Customization

To add new database tools or modify existing ones, follow the pattern used in the existing tools:

@mcp.tool()
async def your_new_tool(ctx: Context, param1: str) -> str:
    """Documentation for your tool"""
    try:
        conn = ctx.request_context.lifespan_context["conn"]
        
        if conn is None:
            return "Database connection is not available."
            
        def your_db_operation():
            # Your database operations here
            pass
            
        loop = asyncio.get_event_loop()
        result = await loop.run_in_executor(None, your_db_operation)
        
        # Process and return results
        return "Your result"
    except Exception as e:
        return f"Error: {str(e)}"

Security Considerations

The server uses Windows Authentication ("Trusted_Connection=yes")
Consider implementing input validation for SQL queries to prevent SQL injection
Restrict database user permissions based on the principle of least privilege

Troubleshooting

Common issues:

Connection errors: Verify the SQL Server instance name and ensure it's running
ODBC driver errors: Confirm ODBC Driver 17 for SQL Server is installed
Permission errors: Check that the Windows user running the application has appropriate SQL Server permissions

License

[Your License Information]

Contact

[Your Contact Information]

Integrations