# MSSQL MCP Server

A production-ready Model Context Protocol (MCP) server for Microsoft SQL Server with comprehensive stored procedure management, virtual filesystem protocol, and safe write operations.

## Features

- **Virtual Filesystem**: Browse database objects (stored procedures, views, functions) as files
- **Discovery Tools**: Search stored procedures, inspect table schemas, analyze dependencies
- **Safe Write Operations**: SQL validation, transaction management, audit logging
- **SP Management**: Draft → Test → Deploy → Rollback workflow with version control
- **Performance Analysis**: Query execution statistics from plan cache
- **Security**: SQL injection prevention, blocked keywords, row limits

## Installation

```bash
npm install
npm run build
```

## Configuration

Copy `.env.example` to `.env` and configure:

```bash
cp .env.example .env
```

Key configuration options:

- **DB_SERVER**: SQL Server hostname
- **DB_DATABASE**: Database name
- **DB_USER/DB_PASSWORD**: Credentials
- **DB_ENCRYPT**: Enable encryption (default: true)
- **DB_TRUST_SERVER_CERTIFICATE**: Trust server certificate for dev environments (default: false)
- **ENABLE_WRITE_OPERATIONS**: Enable INSERT/UPDATE/DELETE (default: true)
- **ENABLE_SP_MODIFICATIONS**: Enable stored procedure management (default: true)
- **REQUIRE_TRANSACTIONS**: Require explicit transactions for modifications (default: true)
- **MAX_ROWS_AFFECTED**: Maximum rows that can be modified in one operation (default: 10000)
- **DRAFT_SCHEMA**: Schema for testing SPs before deployment (default: dbo_draft)
- **AUTO_BACKUP_BEFORE_DEPLOY**: Auto-backup SPs before deploying (default: true)

See `.env.example` for all options.

## Usage with Claude Code

Add to your MCP configuration file (`.claude.json` or `~/.claude.json`):

```json
{
  "mcpServers": {
    "mssql": {
      "command": "node",
      "args": ["/absolute/path/to/sql-server-mcp/dist/index.js"],
      "env": {
        "DB_SERVER": "localhost",
        "DB_DATABASE": "MyDatabase",
        "DB_USER": "sa",
        "DB_PASSWORD": "YourPassword",
        "ENABLE_WRITE_OPERATIONS": "true",
        "ENABLE_SP_MODIFICATIONS": "true"
      }
    }
  }
}
```

## Available Tools

### Discovery & Schema
- **list_stored_procedures**: List stored procedures with pagination
- **search_stored_procedures**: Search SPs by name or content
- **get_table_schema**: Get table structure with columns, indexes, foreign keys
- **get_dependencies**: Analyze object dependencies (callers and references)
- **analyze_sp_performance**: View execution statistics from plan cache

### Execution
- **execute_query_write**: Execute INSERT/UPDATE/DELETE with validation

### Transactions
- **begin_transaction**: Start a new transaction
- **commit_transaction**: Commit changes
- **rollback_transaction**: Undo changes

### Stored Procedure Management
- **create_sp_draft**: Create a draft SP in separate schema
- **test_sp_draft**: Test draft with parameters
- **deploy_sp**: Deploy to production (auto-backs up current version)
- **rollback_sp**: Restore previous version
- **list_sp_versions**: View version history

## Example Workflows

### Searching for Stored Procedures
```
search for stored procedures that modify the Orders table
```

### Viewing Database Objects as Files
```
show me /database/stored_procedures/dbo/GetCustomerOrders.sql
```

### Modifying a Stored Procedure
```
I need to update GetCustomerOrders to include email.
Create a draft, test it with CustomerId=123, then deploy.
```

### Safe Data Modifications
```
Start a transaction, update Customer set Status='Active' where Id=123,
show me the result, then commit.
```

### Performance Analysis
```
Analyze performance of GetDailySalesReport and show execution stats
```

## Architecture

### Phase 1: Read-Only Foundation
- Virtual filesystem for database objects
- Connection pooling (read-only pool)
- Query execution with error handling
- Discovery and schema inspection tools

### Phase 2: Safe Writes
- SQL validation and injection prevention
- Transaction manager with timeout protection
- Audit logging for compliance
- Write operation tools

### Phase 3: SP Management
- Version control with SPVersionHistory table
- Draft schema for safe testing
- Deploy/rollback workflow
- Automatic backups before deployment

### Phase 4: Advanced Features
- Dependency analysis
- Performance monitoring from DMVs
- Reference finding

## Security Features

- **SQL Validation**: Blocks dangerous keywords and patterns
- **Parameterized Queries**: Prevents SQL injection
- **Row Limits**: Prevents accidental mass updates
- **Transaction Timeouts**: Auto-rollback after 5 minutes
- **Audit Trail**: Logs all operations to file
- **Separate Credentials**: Optional read-only and read-write connections

## Testing

A complete test environment is included in the `test/` directory:

```bash
cd test
./setup.sh init
```

This sets up a Docker container with SQL Server 2022 and a test database (`MCPTestDB`) pre-populated with:
- Sample tables (Customers, Products, Orders, etc.)
- 13 test stored procedures
- Views and functions
- Sample data for testing all MCP features

Connection string for testing:
```
Server=localhost,1433;Database=MCPTestDB;User Id=sa;Password=McpTest123!;TrustServerCertificate=true
```

### Running Tests

After setting up the test database:

```bash
# Run all tests
npm run test:all

# Or run specific tests
npm run test:connection  # Basic connectivity test
npm run test:db         # Database object verification
npm run test:scenarios  # Comprehensive functionality tests
```

See `test/README.md` for detailed testing scenarios and management commands.

## Development

```bash
# Development mode with auto-reload
npm run dev

# Build
npm run build

# Run production build
npm start

# Lint
npm run lint

# Format
npm run format
```

## Project Structure

```
sql-server-mcp/
├── src/
│   ├── index.ts                    # Entry point
│   ├── server.ts                   # MCP server setup
│   ├── types/                      # TypeScript interfaces
│   ├── config/                     # Configuration and logging
│   ├── database/                   # Connection, query execution, transactions
│   ├── filesystem/                 # Virtual filesystem protocol
│   ├── tools/                      # MCP tool implementations
│   │   ├── discovery/              # Search and discovery
│   │   ├── schema/                 # Schema inspection
│   │   ├── execution/              # Query execution
│   │   ├── sp-management/          # SP lifecycle
│   │   └── transactions/           # Transaction control
│   ├── security/                   # Validation, audit logging
│   └── utils/                      # Version manager, helpers
├── test/                           # Test environment (Docker + SQL Server)
│   ├── docker-compose.yml          # SQL Server 2022 container
│   ├── setup.sh                    # Management script
│   ├── init-scripts/               # Database initialization
│   └── backups/                    # Database backups
├── .env.example                    # Configuration template
└── package.json
```

## License

MIT

## Contributing

Contributions are welcome! Please ensure all tools follow security best practices.