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

npm install

Configuration

Create a .env file with your database credentials:

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:

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:

{
  "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:

{
  "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:

{
  "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:

{
  "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:

{
  "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:

{
  "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:

{
  "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:

{
  "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.