MCP SQL Server

by dennismartis

Integrations

  • Implements database operations using the pyodbc Python library to provide SQL Server interaction capabilities.

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

  1. Install Python dependencies:
pip install pyodbc asyncio fastmcp
  1. Ensure you have Microsoft SQL Server installed and the ODBC Driver 17 for SQL Server.
  2. 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:

  1. Lifecycle Management: The app_lifespan context manager handles database connection setup and teardown.
  2. 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.
  3. 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:

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

License

[Your License Information]

Contact

[Your Contact Information]

-
security - not tested
F
license - not found
-
quality - not tested

A FastMCP server that provides natural language interaction with MS SQL databases, enabling users to query data, list tables, describe structures, and execute database operations through a conversational AI interface.

  1. Overview
    1. Features
      1. Requirements
        1. Installation
          1. Usage
            1. Available Tools
              1. query_sql
              2. list_tables
              3. describe_table
              4. execute_nonquery
              5. list_odbc_drivers
              6. database_info
            2. Architecture
              1. Error Handling
                1. Customization
                  1. Security Considerations
                    1. Troubleshooting
                      1. License
                        1. Contact

                          Related MCP Servers

                          • -
                            security
                            F
                            license
                            -
                            quality
                            A production-ready MCP server built with FastAPI, providing an enhanced tool registry for creating, managing, and documenting AI tools for Large Language Models (LLMs).
                            Last updated -
                            13
                            Python
                          • -
                            security
                            A
                            license
                            -
                            quality
                            A FastMCP server implementation for the Dixa API, enabling AI assistants to search, retrieve, and manage customer conversation data and tags.
                            Last updated -
                            TypeScript
                            MIT License
                          • A
                            security
                            F
                            license
                            A
                            quality
                            MCP server that allows Claude AI to interact directly with MySQL databases, enabling query execution and table information retrieval through natural language.
                            Last updated -
                            1
                            6
                            3
                            JavaScript
                          • -
                            security
                            -
                            license
                            -
                            quality
                            A FastMCP server that enables browser automation through natural language commands, allowing Language Models to browse the web, fill out forms, click buttons, and perform other web-based tasks via a simple API.
                            Last updated -
                            1
                            Python

                          View all related MCP servers

                          ID: kyi3u1xitg