# MSSQL MCP Server (Read-Only)

A Model Context Protocol (MCP) server that provides read-only access to Microsoft SQL Server databases. This server enables safe database exploration and querying through a standardized interface.

## Features

- 🔒 **Read-only operations** - All queries are validated to prevent data modification
- 🔐 **Secure by default** - Credentials read only from environment variables
- 📊 **Schema exploration** - Discover tables, views, procedures, and functions
- 🔍 **Query execution** - Execute SELECT queries with automatic safety limits
- 📝 **Stored procedure inspection** - View procedure definitions and parameters
- 🛡️ **Input validation** - Strict validation of identifiers and query patterns

## Installation

```bash
npm install
```

## Configuration

Create a `.env` file with your database credentials:

```env
DB_SERVER=your-server-address
DB_DATABASE=your-database-name
DB_USER=your-username
DB_PASSWORD=your-password
DB_PORT=1433
DB_ENCRYPT=true
DB_TRUST_SERVER_CERTIFICATE=false
DB_CONNECTION_TIMEOUT=30000
DB_REQUEST_TIMEOUT=30000
```

## Usage

Run the server:

```bash
npm start
# or
node server.ts
```

The server communicates via stdio and follows the MCP protocol.

## Available Tools

### 1. `connect_database`

**Purpose:** Establish a connection to a Microsoft SQL Server database.

**Description:** Connects to the database using credentials from environment variables only. This ensures security by preventing credential injection through user input.

**Parameters:** None (uses environment variables)

**Returns:** Connection status message

**Example:**
```json
{
  "content": [{
    "type": "text",
    "text": "✅ Connected to SQL Server: server-name (DB: database-name)"
  }]
}
```

---

### 2. `execute_query`

**Purpose:** Execute read-only SQL queries (SELECT statements or CTEs).

**Description:** 
- Only allows SELECT and WITH (CTE) queries
- Blocks DDL, DML, and execution keywords
- Automatically applies TOP(limit) to plain SELECT queries if not present
- Supports parameterized queries for safe user input
- Blocks comments and semicolons to prevent injection

**Parameters:**
- `query` (string, required): SQL query starting with SELECT or WITH
- `parameters` (object, optional): Key-value pairs for parameterized queries
- `limit` (number, optional, default: 200): Maximum rows returned (1-10000)

**Returns:** Query results with metadata (execution time, row count, etc.)

**Example:**
```json
{
  "query": "SELECT * FROM clients WHERE id = @id",
  "parameters": { "id": 123 },
  "limit": 100
}
```

---

### 3. `get_schema`

**Purpose:** Retrieve database schema information (tables, views, procedures, functions).

**Description:** Lists database objects filtered by type and optionally by schema name. All identifiers are validated to prevent SQL injection.

**Parameters:**
- `objectType` (enum, optional, default: "tables"): One of: "tables", "views", "procedures", "functions", "all"
- `schemaName` (string, optional): Filter by schema name (alphanumeric and underscore only)

**Returns:** Array of schema objects with their metadata

**Example:**
```json
{
  "objectType": "all",
  "schemaName": "dbo"
}
```

---

### 4. `describe_table`

**Purpose:** Get detailed structure information for a specific table.

**Description:** Returns column information including data types, nullability, defaults, and ordinal positions. Schema and table names are validated.

**Parameters:**
- `tableName` (string, required): Name of the table
- `schemaName` (string, optional, default: "dbo"): Schema name

**Returns:** Array of column definitions

**Example:**
```json
{
  "tableName": "clients",
  "schemaName": "dbo"
}
```

---

### 5. `connection_status`

**Purpose:** Check the current database connection status and configuration.

**Description:** Returns detailed connection information including server, database, port, connection pool stats, and security mode.

**Parameters:** None

**Returns:** Connection status object with:
- `connected`: Boolean indicating connection state
- `server`: Server address
- `database`: Database name
- `port`: Port number
- `connectionTime`: ISO timestamp of connection
- `security`: Security mode information
- `poolInfo`: Connection pool statistics

---

### 6. `disconnect_database`

**Purpose:** Close the current database connection.

**Description:** Safely closes the connection pool and cleans up resources.

**Parameters:** None

**Returns:** Success message

---

### 7. `get_table_data`

**Purpose:** Read rows from a table with optional filtering, pagination, and sorting.

**Description:** 
- Validates table and schema names
- Supports WHERE clauses with parameterized values
- Supports ORDER BY with validation
- Implements OFFSET/FETCH for pagination
- Maximum 10,000 rows per request

**Parameters:**
- `tableName` (string, required): Table name (alphanumeric and underscore only)
- `schemaName` (string, optional, default: "dbo"): Schema name
- `limit` (number, optional, default: 100): Maximum rows (1-10000)
- `offset` (number, optional, default: 0): Rows to skip
- `whereClause` (string, optional): WHERE clause without the WHERE keyword
- `orderBy` (string, optional): ORDER BY clause without the ORDER BY keyword
- `parameters` (object, optional): Parameters for WHERE clause

**Returns:** Table data with metadata (row count, execution time, etc.)

**Example:**
```json
{
  "tableName": "clients",
  "schemaName": "dbo",
  "limit": 50,
  "offset": 0,
  "whereClause": "age > @minAge",
  "orderBy": "name ASC",
  "parameters": { "minAge": 18 }
}
```

---

### 8. `list_procedures`

**Purpose:** List stored procedures in a specific schema.

**Description:** Returns all stored procedures with their creation and modification dates. Schema name is validated.

**Parameters:**
- `schemaName` (string, optional, default: "dbo"): Schema name to filter

**Returns:** Array of procedure information

**Example:**
```json
{
  "schemaName": "dbo"
}
```

---

### 9. `describe_procedure`

**Purpose:** Get detailed parameter information for a stored procedure.

**Description:** Returns procedure parameters including data types, lengths, precision, scale, output flags, and default values. All identifiers are validated.

**Parameters:**
- `procedureName` (string, required): Name of the procedure
- `schemaName` (string, optional, default: "dbo"): Schema name

**Returns:** Array of parameter definitions

**Example:**
```json
{
  "procedureName": "GetClientInfo",
  "schemaName": "dbo"
}
```

---

### 10. `get_procedure_definition`

**Purpose:** Retrieve the T-SQL source code of a stored procedure.

**Description:** Returns the full procedure definition. Requires VIEW DEFINITION permission on the database. All identifiers are validated.

**Parameters:**
- `procedureName` (string, required): Name of the procedure
- `schemaName` (string, optional, default: "dbo"): Schema name

**Returns:** Procedure definition text or error message if not found/no permission

**Example:**
```json
{
  "procedureName": "GetClientInfo",
  "schemaName": "dbo"
}
```

---

### 11. `list_databases`

**Purpose:** List all databases on the connected SQL Server instance.

**Description:** Returns database information including IDs, creation dates, collation, state, access mode, read-only status, and recovery model. Read-only operation.

**Parameters:** None

**Returns:** Array of database information

---

## Security Features

### Query Validation
- Only SELECT and WITH (CTE) queries allowed
- Blocks INSERT, UPDATE, DELETE, DROP, ALTER, CREATE, TRUNCATE, EXEC, etc.
- Blocks comments (`--`, `/* */`)
- Blocks semicolons to prevent multi-statement execution
- Automatic TOP limit for plain SELECT queries

### Input Validation
- Table names: Only alphanumeric and underscore
- Schema names: Only alphanumeric and underscore
- Parameterized queries: Use parameters for user input
- ORDER BY validation: Only column names and ASC/DESC

### Credential Security
- Credentials read only from environment variables
- No credential input through tool parameters
- Recommended: Use read-only database user with `db_datareader` role

## Resources

### `connection-info`
A resource that provides current connection information in JSON format.

**URI:** `mssql://connection/info`

**Content:** JSON object with connection status and configuration

## Error Handling

All tools return structured error responses with descriptive messages. Common errors include:
- Connection not established
- Invalid query syntax
- Permission denied
- Invalid identifier format
- Query validation failures

## Best Practices

1. **Always use parameterized queries** when including user input
2. **Use appropriate limits** to avoid large result sets
3. **Check connection status** before executing queries
4. **Use schema names** explicitly to avoid ambiguity
5. **Disconnect** when done to free resources

## Limitations

- Read-only operations only
- Maximum 10,000 rows per query
- Single-statement queries only (no semicolons)
- No comments allowed in queries
- Requires VIEW DEFINITION permission for procedure definitions

## Version

Current version: **1.0.0**

## Contributing

This is a read-only MCP server designed for safe database exploration. Contributions should maintain the security-first approach and read-only nature of the server.