# 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:

```bash
pip install pyodbc asyncio fastmcp
```

2. Ensure you have Microsoft SQL Server installed and the ODBC Driver 17 for SQL Server.

3. Configure the connection settings in the script:

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

## Usage

Run the server:

```bash
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:

```python
@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]