# SQL Server MCP Server

A Model Context Protocol (MCP) server for SQL Server that provides tools for database operations to coding assistants.

## Features

This MCP server provides three powerful tools for interacting with SQL Server:

1. **search_tables** - Search for tables and columns in the database
   - Filter by table name patterns
   - Filter by column name patterns
   - Filter by schema name
   - Returns table structure with data types

2. **execute_select** - Execute SELECT queries and return results
   - Safe execution (only SELECT statements allowed)
   - Configurable row limits
   - Returns structured JSON results

3. **get_table_info** - Get detailed information about a specific table
   - Column details with data types
   - Primary key information
   - Foreign key relationships
   - Full table structure
   - **Enhanced with dbDocs API** (optional):
     - Column descriptions from documentation
     - SQL usage examples from actual codebase
     - "RefersTo" relationships
     - Child tables that reference this table

## API Enhancement (Optional)

The server can optionally integrate with a dbDocs API to provide enriched metadata:
- **Column descriptions**: Human-readable descriptions for each column
- **Usage examples**: Real SQL queries from your codebase that use the table
- **Relationships**: Enhanced foreign key relationships with semantic meaning
- **Referenced by**: List of child tables that reference this table

To enable, add to your `config.json`:
```json
{
  "dbdocs_api_url": "http://localhost:8090",
  "database_name": "your_database_name"
}
```

The API endpoint format should be: `{dbdocs_api_url}/v1/dbDocs/{database_name}/tables/{table_name}`

If the API is unavailable, the server gracefully falls back to standard SQL Server metadata.

## Prerequisites

- Python 3.10 or higher
- SQL Server database
- ODBC Driver for SQL Server (see installation below)

### Installing ODBC Driver for SQL Server

**Windows:**
Download and install from: https://docs.microsoft.com/en-us/sql/connect/odbc/download-odbc-driver-for-sql-server

**Linux:**
```bash
# Ubuntu/Debian
curl https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add -
curl https://packages.microsoft.com/config/ubuntu/$(lsb_release -rs)/prod.list | sudo tee /etc/apt/sources.list.d/mssql-release.list
sudo apt-get update
sudo ACCEPT_EULA=Y apt-get install -y msodbcsql17

# RHEL/CentOS
sudo curl -o /etc/yum.repos.d/mssql-release.repo https://packages.microsoft.com/config/rhel/8/prod.repo
sudo ACCEPT_EULA=Y yum install -y msodbcsql17
```

**macOS:**
```bash
brew tap microsoft/mssql-release https://github.com/Microsoft/homebrew-mssql-release
brew update
HOMEBREW_NO_ENV_FILTERING=1 ACCEPT_EULA=Y brew install msodbcsql17
```

## Installation

1. Clone or download this repository

2. Install Python dependencies:
```bash
pip install -r requirements.txt
```

3. Create your configuration file:
```bash
cp config.json.example config.json
```

4. Edit `config.json` with your SQL Server connection details:
```json
{
  "connection_string": "Driver={ODBC Driver 17 for SQL Server};Server=your-server;Database=your-database;UID=your-username;PWD=your-password;",
  "connection_timeout": 30,
  "query_timeout": 60,
  "dbdocs_api_url": "http://localhost:8090",
  "database_name": "your-database"
}
```

**Note:** The `dbdocs_api_url` and `database_name` fields are optional. If provided, the server will fetch enhanced table metadata including column descriptions, usage examples, and relationships. If omitted or if the API is unavailable, the server will use standard SQL Server metadata.

### Connection String Examples

**Windows Authentication:**
```
Driver={ODBC Driver 17 for SQL Server};Server=localhost;Database=MyDB;Trusted_Connection=yes;
```

**SQL Server Authentication:**
```
Driver={ODBC Driver 17 for SQL Server};Server=localhost;Database=MyDB;UID=myuser;PWD=mypassword;
```

**Azure SQL Database:**
```
Driver={ODBC Driver 17 for SQL Server};Server=your-server.database.windows.net;Database=your-database;UID=your-username;PWD=your-password;Encrypt=yes;TrustServerCertificate=no;
```

**Named Instance:**
```
Driver={ODBC Driver 17 for SQL Server};Server=localhost\\SQLEXPRESS;Database=MyDB;UID=myuser;PWD=mypassword;
```

## Usage

### Running the Server

Run the MCP server:
```bash
python server.py
```

The server runs as a command-line application and communicates via stdio (standard input/output) using the MCP protocol.

### Configuring with Claude Desktop or Other MCP Clients

To use this server with Claude Desktop or another MCP client, add it to your MCP client configuration:

**For Claude Desktop** (`claude_desktop_config.json`):
```json
{
  "mcpServers": {
    "sql-server": {
      "command": "python",
      "args": ["c:\\repos_private\\sql-mcp-server\\server.py"],
      "cwd": "c:\\repos_private\\sql-mcp-server"
    }
  }
}
```

**For other MCP clients**, consult their documentation on how to register stdio-based MCP servers.

## Tool Usage Examples

### 1. Search for Tables

Search for all tables containing "user" in their name:
```json
{
  "table_pattern": "%user%"
}
```

Search for tables with columns containing "email":
```json
{
  "column_pattern": "%email%"
}
```

Search in a specific schema:
```json
{
  "table_pattern": "%",
  "schema_name": "dbo"
}
```

### 2. Execute SELECT Query

Simple query:
```json
{
  "query": "SELECT * FROM Users WHERE Age > 25"
}
```

With row limit:
```json
{
  "query": "SELECT TOP 100 * FROM Orders ORDER BY OrderDate DESC",
  "max_rows": 100
}
```

### 3. Get Table Information

Get full details about a table:
```json
{
  "table_name": "Users",
  "schema_name": "dbo"
}
```

## Security Features

- **Read-only operations**: Only SELECT queries are allowed
- **Query validation**: Blocks dangerous SQL keywords (DROP, DELETE, INSERT, UPDATE, etc.)
- **Row limits**: Prevents excessive data retrieval (configurable, max 10,000 rows)
- **Connection timeouts**: Prevents hanging connections
- **Query timeouts**: Prevents long-running queries from blocking

## Architecture

The server is built using:
- **MCP SDK**: Official Model Context Protocol implementation
- **pyodbc**: Python ODBC interface for SQL Server
- **asyncio**: Asynchronous I/O for efficient operation

## Development

### Project Structure
```
sql-mcp-server/
├── server.py              # Main MCP server implementation
├── config.json            # Configuration file (create from example)
├── config.json.example    # Example configuration
├── requirements.txt       # Python dependencies
├── pyproject.toml        # Python project metadata
├── .gitignore            # Git ignore patterns
└── README.md             # This file
```

### Testing

You can test the server locally:

1. Start the server:
```bash
python server.py
```

2. The server communicates via stdio, so you'll need an MCP client to interact with it properly.

### Troubleshooting

**"Configuration file not found"**
- Make sure you've created `config.json` from the example file

**"Failed to connect to database"**
- Verify your connection string is correct
- Ensure the SQL Server is accessible
- Check firewall settings
- Verify ODBC driver is installed

**"ODBC Driver not found"**
- Install the ODBC Driver for SQL Server (see Prerequisites above)
- Make sure the driver name in your connection string matches the installed driver

**"Only SELECT queries are allowed"**
- This server only supports SELECT statements for safety
- Modify, insert, or delete operations are not permitted

## License

MIT License - feel free to modify and use as needed.

## Contributing

Contributions are welcome! Please feel free to submit issues or pull requests.

## Future Enhancements

Potential features for future versions:
- Support for stored procedure execution (read-only)
- Query result caching
- Connection pooling
- Support for multiple database connections
- Database schema visualization
- Query performance statistics
- Table data statistics (row counts, size, etc.)