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

Always use parameterized queries when including user input
Use appropriate limits to avoid large result sets
Check connection status before executing queries
Use schema names explicitly to avoid ambiguity
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.