Zahidhr/MCP-SqlServerV1

MCP-SqlServerV1

A Model Context Protocol (MCP) server that enables AI assistants to interact with SQL Server databases through natural languages. This server provides safe, read-only access to query databases, inspect schemas, and execute stored procedures.

Features

• Execute SELECT queries with configurable row limits
• List database tables with schema filtering
• Inspect table schemas including column types and constraints
• Execute stored procedures with parameter support
• Safe by default - only SELECT queries allowed for data retrieval
• Windows Authentication support (or SQL Server authentication)

Prerequisites

• Python 3.10 or higher
• SQL Server (local or remote instance)
• ODBC Driver 17 for SQL Server or newer
• Any MCP-compatible client

Installation

1. Install Python Dependencies

pip install mcp pyodbc

2. Download the Server Files

Clone this repository or download the following files:

• sql_server_mcp.py - The MCP server implementation
• .mcp.json - Configuration template

3. Configure Your Database Connection

Edit the .mcp.json file with your database details:

{
  "servers": {
    "sql-server": {
      "command": "python",
      "args": [
        "C:\\MCP\\sql_server_mcp.py"
      ],
      "env": {
        "SQL_SERVER": "localhost",
        "SQL_DATABASE": "YourDatabaseName",
        "SQL_USERNAME": "",
        "SQL_PASSWORD": "",
        "SQL_DRIVER": "{ODBC Driver 17 for SQL Server}"
      }
    }
  }
}

Configuration Options:

• SQL_SERVER: Your SQL Server instance (e.g., localhost, server.domain.com, localhost\\SQLEXPRESS)
• SQL_DATABASE: Database name to connect to
• SQL_USERNAME: Leave empty for Windows Authentication, or provide SQL Server username
• SQL_PASSWORD: Leave empty for Windows Authentication, or provide SQL Server password
• SQL_DRIVER: ODBC driver name (check available drivers with odbcinst -q -d)

Available Tools

1. execute_query

Execute SELECT queries and retrieve results as JSON.

Parameters:

• query (required): SQL SELECT statement
• max_rows (optional): Maximum rows to return (default: 100)

Example:

SELECT TOP 10 * FROM Customers WHERE Country = 'USA'

2. list_tables

List all tables in the database.

Parameters:

• schema (optional): Filter by schema name (default: "dbo")

3. get_table_schema

Get detailed schema information for a specific table.

Parameters:

• table_name (required): Name of the table
• schema (optional): Schema name (default: "dbo")

4. execute_stored_procedure

Execute a stored procedure with parameters.

Parameters:

• procedure_name (required): Name of the stored procedure
• parameters (optional): Key-value pairs of parameters

Example:

{
  "procedure_name": "GetCustomerOrders",
  "parameters": {
    "CustomerId": 123,
    "StartDate": "2024-01-01"
  }
}

Usage Examples

Once configured with an MCP client, you can query your database with natural language:

• "List all tables in the database"
• "Show me the schema for the Users table"
• "Query the top 20 orders from last month"
• "Execute the GetSalesReport stored procedure for 2024"

Security Considerations

• Read-only by default: The execute_query tool only allows SELECT statements
• Credentials: Store sensitive credentials securely, avoid committing them to version control
• Network access: Ensure your SQL Server firewall rules are properly configured
• Least privilege: Use a database account with minimal necessary permissions

Troubleshooting

ODBC Driver Not Found

Check installed drivers:

# Windows (PowerShell)
Get-OdbcDriver

# Linux/macOS
odbcinst -q -d

Install ODBC Driver 17 for SQL Server from Microsoft's website if needed.

Connection Failed

1. Verify SQL Server is running and accessible
2. Check firewall settings (TCP port 1433 by default)
3. Confirm SQL Server authentication mode (Windows vs SQL Server auth)
4. Test connection string with a database client first

Permission Errors

Ensure the database user has at least:

• db_datareader role for SELECT queries
• EXECUTE permission for stored procedures

Contributing

Contributions are welcome! Please feel free to submit a Pull Request.

Acknowledgments

Built using the Model Context Protocol by Anthropic.

Note: This server is designed for development and testing purposes. For production use, consider implementing additional security measures, query validation, and audit logging.