# Directory Structure
```
├── .dockerignore
├── .editorconfig
├── .env.example
├── .env.test
├── .eslintrc.json
├── .github
│ ├── ISSUE_TEMPLATE
│ │ ├── bug_report.md
│ │ ├── config.yml
│ │ ├── documentation.md
│ │ └── feature_request.md
│ ├── pull_request_template.md
│ └── workflows
│ └── ci.yml
├── .gitignore
├── .prettierrc.json
├── CHANGELOG.md
├── CODE_OF_CONDUCT.md
├── docker
│ └── README.md
├── docker-compose.yml
├── Dockerfile
├── Dockerfile.dev
├── docs
│ ├── api
│ │ ├── configuration.md
│ │ ├── error-handling.md
│ │ └── tools.md
│ ├── CONTRIBUTING.md
│ ├── examples
│ │ ├── advanced-usage.md
│ │ └── basic-queries.md
│ └── README.md
├── examples
│ ├── README.md
│ └── simple-server.ts
├── HowToRun.md
├── jest.config.js
├── jest.setup.js
├── LICENSE
├── package-lock.json
├── package.json
├── README.md
├── scripts
│ ├── build.sh
│ ├── setup-dev.sh
│ └── test.sh
├── SECURITY.md
├── smithery.yaml
├── src
│ ├── __tests__
│ │ └── server.test.ts
│ ├── evals
│ │ └── evals.ts
│ ├── index.test.ts
│ ├── index.ts
│ ├── types
│ │ ├── index.ts
│ │ └── mcp.d.ts
│ └── utils
│ ├── config.ts
│ ├── database.ts
│ ├── error.ts
│ └── validation.ts
├── TODO.md
├── tsconfig.json
├── UPGRADE.md
└── vitest.config.ts
```
# Files
--------------------------------------------------------------------------------
/.prettierrc.json:
--------------------------------------------------------------------------------
```json
{
"printWidth": 100,
"tabWidth": 2,
"useTabs": false,
"semi": true,
"singleQuote": true,
"trailingComma": "es5",
"bracketSpacing": true,
"arrowParens": "always"
}
```
--------------------------------------------------------------------------------
/.gitignore:
--------------------------------------------------------------------------------
```
# Dependencies
node_modules/
.pnp/
.pnp.js
# Build output
build/
dist/
*.tsbuildinfo
# Testing
coverage/
# Logs
logs/
*.log
npm-debug.log*
yarn-debug.log*
yarn-error.log*
# Environment variables
.env
.env.local
.env.*.local
# IDE
.idea/
.vscode/
.vs/
*.swp
*.swo
# OS
.DS_Store
Thumbs.db
```
--------------------------------------------------------------------------------
/.env.test:
--------------------------------------------------------------------------------
```
# Test Database Connection Settings
MSSQL_HOST=localhost
MSSQL_PORT=1433
MSSQL_USER=sa
MSSQL_PASSWORD=YourStrongPassword123!
MSSQL_DATABASE=master
# Security Settings (Relaxed for testing)
MSSQL_ENCRYPT=false
MSSQL_TRUST_SERVER_CERTIFICATE=true
MSSQL_ENABLE_ARITH_ABORT=true
# Pool Settings (Minimal for testing)
MSSQL_POOL_MAX=5
MSSQL_POOL_MIN=0
MSSQL_POOL_IDLE_TIMEOUT=10000
MSSQL_POOL_ACQUIRE_TIMEOUT=5000
# Query Settings
MSSQL_QUERY_TIMEOUT=5000
MSSQL_MULTIPLE_STATEMENTS=false
MSSQL_ROWS_AS_ARRAY=false
# Debug Settings (Enabled for testing)
MSSQL_DEBUG=true
MSSQL_DEBUG_SQL=true
```
--------------------------------------------------------------------------------
/.env.example:
--------------------------------------------------------------------------------
```
# SQL Server Connection Settings
MSSQL_HOST=localhost
MSSQL_PORT=1433
MSSQL_USER=sa
MSSQL_PASSWORD=YourStrongPassword123
MSSQL_DATABASE=master
# Security Settings
MSSQL_ENCRYPT=true
MSSQL_TRUST_SERVER_CERTIFICATE=false
MSSQL_ENABLE_ARITH_ABORT=true
# Connection Pool Settings
MSSQL_POOL_MAX=10
MSSQL_POOL_MIN=0
MSSQL_POOL_IDLE_TIMEOUT=30000
MSSQL_POOL_ACQUIRE_TIMEOUT=15000
# Query Settings
MSSQL_QUERY_TIMEOUT=30000
MSSQL_MULTIPLE_STATEMENTS=false
MSSQL_ROWS_AS_ARRAY=false
# Debug Settings
MSSQL_DEBUG=false
MSSQL_DEBUG_SQL=false
# Note: Copy this file to .env and update the values with your configuration
# Do not commit the .env file to version control
```
--------------------------------------------------------------------------------
/.dockerignore:
--------------------------------------------------------------------------------
```
# Version control
.git
.gitignore
.gitattributes
# Dependencies
node_modules
npm-debug.log*
yarn-debug.log*
yarn-error.log*
# Build output
build
dist
coverage
.nyc_output
# Development files
.vscode
.idea
*.swp
*.swo
*~
.DS_Store
# Environment files
.env
.env.*
!.env.example
# Documentation
docs
*.md
!README.md
# Test files
__tests__
*.test.ts
*.spec.ts
jest.config.js
jest.setup.js
# Docker files
Dockerfile
Dockerfile.dev
docker-compose.yml
.dockerignore
# CI/CD
.github
.gitlab-ci.yml
.travis.yml
.circleci
# Temporary files
tmp
temp
*.log
logs
# Editor configurations
.editorconfig
.eslintrc*
.prettierrc*
.stylelintrc*
# Miscellaneous
*.gz
*.zip
*.tar
*.tgz
.cache
.env.local
.env.development.local
.env.test.local
.env.production.local
# TypeScript cache
*.tsbuildinfo
# Debug files
.debug
debug.log
# Husky
.husky
```
--------------------------------------------------------------------------------
/.editorconfig:
--------------------------------------------------------------------------------
```
# EditorConfig is awesome: https://EditorConfig.org
# top-most EditorConfig file
root = true
# Unix-style newlines with a newline ending every file
[*]
end_of_line = lf
insert_final_newline = true
charset = utf-8
trim_trailing_whitespace = true
# TypeScript and JavaScript files
[*.{ts,js,tsx,jsx}]
indent_style = space
indent_size = 2
quote_type = single
# JSON files
[*.json]
indent_style = space
indent_size = 2
# YAML files
[*.{yml,yaml}]
indent_style = space
indent_size = 2
# Markdown files
[*.md]
trim_trailing_whitespace = false
indent_style = space
indent_size = 2
# Shell scripts
[*.sh]
indent_style = space
indent_size = 2
end_of_line = lf
# Docker files
[{Dockerfile,Dockerfile.dev}]
indent_style = space
indent_size = 2
# Environment files
[*.env*]
indent_style = space
indent_size = 2
# Configuration files
[*.{eslintrc,prettierrc,babelrc}]
indent_style = space
indent_size = 2
# Documentation
[docs/**/*.md]
trim_trailing_whitespace = false
indent_style = space
indent_size = 2
# Test files
[**/__tests__/**]
indent_style = space
indent_size = 2
# Package files
[package.json]
indent_style = space
indent_size = 2
# Git files
[.git*]
indent_style = space
indent_size = 2
```
--------------------------------------------------------------------------------
/.eslintrc.json:
--------------------------------------------------------------------------------
```json
{
"env": {
"node": true,
"es2022": true
},
"extends": [
"eslint:recommended",
"plugin:@typescript-eslint/recommended",
"plugin:@typescript-eslint/recommended-requiring-type-checking",
"prettier"
],
"parser": "@typescript-eslint/parser",
"parserOptions": {
"ecmaVersion": "latest",
"sourceType": "module",
"project": "./tsconfig.json"
},
"plugins": ["@typescript-eslint", "prettier"],
"rules": {
"prettier/prettier": "error",
"@typescript-eslint/explicit-function-return-type": "error",
"@typescript-eslint/no-explicit-any": "error",
"@typescript-eslint/no-unused-vars": ["error", { "argsIgnorePattern": "^_" }],
"@typescript-eslint/no-floating-promises": "error",
"no-console": ["error", { "allow": ["error"] }]
},
"overrides": [
{
"files": ["**/*.test.ts", "vitest.config.ts"],
"rules": {
"@typescript-eslint/no-unsafe-assignment": "off",
"@typescript-eslint/no-unsafe-member-access": "off",
"@typescript-eslint/no-unsafe-call": "off",
"@typescript-eslint/no-unsafe-return": "off",
"@typescript-eslint/unbound-method": "off"
},
"env": {
"vitest-globals/env": true
},
"extends": ["plugin:vitest-globals/recommended"]
}
]
}
```
--------------------------------------------------------------------------------
/examples/README.md:
--------------------------------------------------------------------------------
```markdown
# MSSQL MCP Server Examples
This directory contains example implementations of the MSSQL MCP server.
## Simple Server
The `simple-server.ts` example demonstrates a basic MSSQL MCP server with:
- A query tool for executing SQL statements
- An examples resource that provides usage information
- Connection pooling for better performance
- Proper error handling
### Running the Example
```bash
# Install dependencies first if you haven't already
cd ..
npm install
# Run the example with tsx
npx tsx examples/simple-server.ts
```
### Testing with MCP Inspector
You can test this server with the [MCP Inspector](https://github.com/modelcontextprotocol/inspector):
1. Install the inspector:
```bash
npm install -g @modelcontextprotocol/inspector
```
2. In one terminal, start the server:
```bash
npx tsx examples/simple-server.ts
```
3. In another terminal, start the inspector:
```bash
mcp-inspector --transport=stdio --command="npx tsx examples/simple-server.ts"
```
4. Use the inspector UI to:
- Browse available tools and resources
- Execute SQL queries
- View responses and errors
## Configuration
The example server can be configured with environment variables:
```bash
# Configure the server through environment variables
MSSQL_HOST=localhost MSSQL_USER=sa MSSQL_PASSWORD=YourPassword123! npx tsx examples/simple-server.ts
```
For more advanced configuration options, see the main project README.
```
--------------------------------------------------------------------------------
/docs/README.md:
--------------------------------------------------------------------------------
```markdown
# MSSQL MCP Server
A Model Context Protocol (MCP) server for interacting with Microsoft SQL Server databases. This server provides tools for executing queries, listing databases and tables, and retrieving schema information.
## Features
- Execute SQL queries with support for parameters
- List available databases
- List tables in a database
- Get detailed table schema information
- Connection pooling for optimal performance
- Comprehensive error handling and reporting
- Security features including query sanitization and TLS support
## Installation
```bash
# Clone the repository
git clone [repository-url]
cd mssql-mcp-server
# Install dependencies
npm install
```
## Configuration
The server requires the following environment variables to be set in your MCP settings configuration:
```json
{
"mcpServers": {
"mssql": {
"command": "node",
"args": ["/path/to/mssql-mcp-server/build/index.js"],
"env": {
"MSSQL_HOST": "your-server-host",
"MSSQL_PORT": "1433",
"MSSQL_USER": "your-username",
"MSSQL_PASSWORD": "your-password",
"MSSQL_DATABASE": "optional-default-database",
"MSSQL_ENCRYPT": "true",
"MSSQL_TRUST_SERVER_CERTIFICATE": "false",
"MSSQL_POOL_MAX": "10",
"MSSQL_POOL_MIN": "0",
"MSSQL_POOL_IDLE_TIMEOUT": "30000"
}
}
}
}
```
## Available Tools
### query
Execute SQL queries against the database.
```json
{
"query": "SELECT * FROM Users WHERE Age > @age",
"params": {
"age": 18
},
"database": "optional-different-database"
}
```
### list_databases
List all available databases.
```json
{
"filter": "optional-name-pattern"
}
```
### list_tables
List all tables in a database.
```json
{
"database": "database-name",
"schema": "optional-schema-name",
"filter": "optional-name-pattern"
}
```
### describe_table
Get detailed information about a table's schema.
```json
{
"database": "database-name",
"schema": "dbo",
"table": "table-name"
}
```
## Error Handling
The server provides detailed error information including:
- SQL Server specific error codes
- Connection issues
- Query syntax errors
- Permission problems
- Resource limitations
Each error response includes:
- Error code
- Human-readable message
- Additional context when available
- Suggested resolution steps
## Security Considerations
- All queries are validated and sanitized
- Support for parameterized queries to prevent SQL injection
- TLS encryption for data in transit
- Connection pooling with configurable limits
- Credential management through environment variables
## Development
See [CONTRIBUTING.md](./CONTRIBUTING.md) for development guidelines.
## License
MIT License - see [LICENSE](../LICENSE) for details
```
--------------------------------------------------------------------------------
/README.md:
--------------------------------------------------------------------------------
```markdown
# MSSQL MCP Server
[](https://smithery.ai/server/@c0h1b4/mssql-mcp-server)
A Model Context Protocol (MCP) server for connecting to Microsoft SQL Server databases. This server provides tools for executing SQL queries and managing database connections.
**Version Notice:** This project has been upgraded to use Model Context Protocol SDK 1.9.0. See [UPGRADE.md](UPGRADE.md) for details.
## Installation
### Installing via Smithery
To install MSSQL MCP Server for Claude Desktop automatically via [Smithery](https://smithery.ai/server/@c0h1b4/mssql-mcp-server):
```bash
npx -y @smithery/cli install @c0h1b4/mssql-mcp-server --client claude
```
### Manual Installation
```bash
npm install mssql-mcp-server
```
## Usage
Add the server to your MCP settings configuration file:
```json
{
"mcpServers": {
"mssql": {
"command": "mssql-mcp-server",
"env": {
"MSSQL_CONNECTION_STRING": "Server=localhost;Database=master;User Id=sa;Password=yourpassword;",
// Or individual connection parameters:
"MSSQL_HOST": "localhost",
"MSSQL_PORT": "1433",
"MSSQL_DATABASE": "master",
"MSSQL_USER": "sa",
"MSSQL_PASSWORD": "yourpassword",
"MSSQL_ENCRYPT": "false",
"MSSQL_TRUST_SERVER_CERTIFICATE": "true"
}
}
}
}
```
## Tools
### query
Execute a SQL query on a MSSQL database.
#### Parameters
- `connectionString` (string, optional): Full connection string (alternative to individual parameters)
- `host` (string, optional): Database server hostname
- `port` (number, optional): Database server port (default: 1433)
- `database` (string, optional): Database name (default: master)
- `username` (string, optional): Database username
- `password` (string, optional): Database password
- `query` (string, required): SQL query to execute
- `encrypt` (boolean, optional): Enable encryption (default: false)
- `trustServerCertificate` (boolean, optional): Trust server certificate (default: true)
Either `connectionString` OR (`host` + `username` + `password`) must be provided.
#### Example
```typescript
const result = await use_mcp_tool({
server_name: 'mssql',
tool_name: 'query',
arguments: {
host: 'localhost',
username: 'sa',
password: 'yourpassword',
query: 'SELECT * FROM Users',
},
});
```
## Running the Server
### Local Development
```bash
# Install dependencies
npm install
# Run in development mode
npm run dev
# Build
npm run build
# Run the built server
npm start
```
### Using Docker
```bash
# Build and start services (SQL Server + MCP server)
docker-compose up
# Or just build the Docker image
docker build -t mssql-mcp-server .
```
## Testing
```bash
# Run tests
npm test
# Run tests with coverage
npm run test:coverage
```
## Running evals
The evals package loads an mcp client that then runs the index.ts file, so there is no need to rebuild between tests. You can load environment variables by prefixing the npx command. Full documentation can be found [here](https://www.mcpevals.io/docs).
```bash
OPENAI_API_KEY=your-key npx mcp-eval src/evals/evals.ts examples/simple-server.ts
```
## Security
The server includes safeguards against dangerous SQL operations:
- Blocks potentially harmful commands like DROP, TRUNCATE, ALTER, CREATE, EXEC, etc.
- Validates all input parameters and database names
- Sets reasonable limits on query length and timeout
- Uses connection pooling for better performance and security
## License
MIT
```
--------------------------------------------------------------------------------
/docker/README.md:
--------------------------------------------------------------------------------
```markdown
# Docker Setup for MSSQL MCP Server
This document explains how to use Docker with the MSSQL MCP Server for both development and production environments.
## Prerequisites
- Docker
- Docker Compose
- Git
## Development Environment
The development environment uses `docker-compose.yml` and `Dockerfile.dev` to create a development-friendly setup with hot reloading.
### Starting the Development Environment
```bash
# Start the development environment
docker-compose up
# Start in detached mode
docker-compose up -d
# View logs if running in detached mode
docker-compose logs -f
```
### Development Container Features
- Hot reloading using nodemon
- Source code mounted as a volume
- SQL Server tools installed for debugging
- Development dependencies included
- Environment variables configured for local development
### Accessing SQL Server
```bash
# Connect using sqlcmd in the mssql container
docker-compose exec mssql /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P YourStrongPassword123!
# Or connect using your preferred SQL client:
Host: localhost
Port: 1433
User: sa
Password: YourStrongPassword123!
```
## Production Environment
The production environment uses the main `Dockerfile` which creates a minimal production image using multi-stage builds.
### Building the Production Image
```bash
# Build the production image
docker build -t mssql-mcp-server:latest .
# Run the production container
docker run -d \
--name mssql-mcp-server \
-e MSSQL_HOST=your-sql-server \
-e MSSQL_PORT=1433 \
-e MSSQL_USER=your-user \
-e MSSQL_PASSWORD=your-password \
mssql-mcp-server:latest
```
### Production Container Features
- Multi-stage build for minimal image size
- Production dependencies only
- SQL Server tools included for diagnostics
- Optimized for production use
## Environment Variables
Both development and production environments support the following environment variables:
```bash
MSSQL_HOST=hostname
MSSQL_PORT=1433
MSSQL_USER=username
MSSQL_PASSWORD=password
MSSQL_DATABASE=database
MSSQL_ENCRYPT=true/false
MSSQL_TRUST_SERVER_CERTIFICATE=true/false
```
## Docker Compose Commands
```bash
# Start services
docker-compose up
# Stop services
docker-compose down
# Rebuild services
docker-compose up --build
# View logs
docker-compose logs
# Execute command in service
docker-compose exec app npm run test
# View running services
docker-compose ps
```
## Data Persistence
SQL Server data is persisted using a named volume `mssql-data`. This ensures your data survives container restarts.
To manage the volume:
```bash
# List volumes
docker volume ls
# Inspect volume
docker volume inspect mssql-data
# Remove volume (WARNING: This will delete all data!)
docker volume rm mssql-data
```
## Troubleshooting
### Common Issues
1. **SQL Server Connection Issues**
```bash
# Check if SQL Server is running
docker-compose ps
# View SQL Server logs
docker-compose logs mssql
```
2. **Permission Issues**
```bash
# Reset SQL Server password
docker-compose exec mssql /opt/mssql-tools/bin/sqlcmd \
-S localhost -U SA -P YourStrongPassword123! \
-Q "ALTER LOGIN sa WITH PASSWORD='NewPassword123!'"
```
3. **Container Won't Start**
```bash
# Check container logs
docker-compose logs app
# Verify environment variables
docker-compose config
```
### Health Checks
The SQL Server container includes health checks to ensure the database is ready before starting the application. You can monitor the health status:
```bash
docker inspect --format='{{json .State.Health}}' $(docker-compose ps -q mssql)
```
## Security Notes
- Change default passwords in production
- Use secrets management in production
- Enable encryption for production environments
- Follow least privilege principle for SQL Server accounts
- Regularly update base images and dependencies
```
--------------------------------------------------------------------------------
/SECURITY.md:
--------------------------------------------------------------------------------
```markdown
# Security Policy
## Supported Versions
We release patches for security vulnerabilities. Currently supported versions are:
| Version | Supported |
| ------- | ------------------ |
| 0.1.x | :white_check_mark: |
## Reporting a Vulnerability
We take the security of MSSQL MCP Server seriously. If you believe you have found a security
vulnerability, please report it to us as described below.
**Please do not report security vulnerabilities through public GitHub issues.**
Instead, please report them via email to [[email protected]] (replace with appropriate
contact).
You should receive a response within 48 hours. If for some reason you do not, please follow up via
email to ensure we received your original message.
Please include the requested information listed below (as much as you can provide) to help us better
understand the nature and scope of the possible issue:
- Type of issue (e.g. buffer overflow, SQL injection, cross-site scripting, etc.)
- Full paths of source file(s) related to the manifestation of the issue
- The location of the affected source code (tag/branch/commit or direct URL)
- Any special configuration required to reproduce the issue
- Step-by-step instructions to reproduce the issue
- Proof-of-concept or exploit code (if possible)
- Impact of the issue, including how an attacker might exploit it
This information will help us triage your report more quickly.
## Preferred Languages
We prefer all communications to be in English.
## Security Update Process
1. The security report is received and assigned to an owner
2. The problem is confirmed and a list of affected versions is determined
3. Code is audited to find any similar problems
4. Fixes are prepared for all supported releases
5. New versions are released and notifications are sent
## Security Best Practices
When using MSSQL MCP Server in your environment:
1. **Environment Variables**
- Never commit sensitive environment variables
- Use secure secrets management in production
- Rotate credentials regularly
2. **Database Access**
- Use least privilege principle
- Enable TLS/SSL encryption
- Use strong passwords
- Implement proper access controls
3. **Query Security**
- Always use parameterized queries
- Validate all inputs
- Limit query execution time
- Monitor query patterns
4. **Network Security**
- Use firewalls to restrict access
- Enable encryption in transit
- Monitor network traffic
- Use VPNs or private networks when possible
5. **Logging and Monitoring**
- Enable security logging
- Monitor for suspicious activity
- Implement alerts for security events
- Maintain audit trails
## Disclosure Policy
When we receive a security bug report, we will:
1. Confirm the problem and determine affected versions
2. Audit code to find any similar problems
3. Prepare fixes for all supported versions
4. Release new versions as soon as possible
## Comments on this Policy
If you have suggestions on how this process could be improved, please submit a pull request.
```
--------------------------------------------------------------------------------
/docs/CONTRIBUTING.md:
--------------------------------------------------------------------------------
```markdown
# Contributing to MSSQL MCP Server
We appreciate your interest in contributing to the MSSQL MCP Server project! This document provides guidelines and instructions for contributing.
## Development Setup
1. **Prerequisites**
- Node.js (v18 or higher)
- npm (v9 or higher)
- Access to a Microsoft SQL Server instance for testing
2. **Local Development Environment**
```bash
# Clone the repository
git clone [repository-url]
cd mssql-mcp-server
# Install dependencies
npm install
# Create a .env file for local development
cp .env.example .env
# Edit .env with your SQL Server credentials
```
3. **Build the Project**
```bash
npm run build
```
4. **Run Tests**
```bash
npm test
```
## Code Style Guidelines
We use ESLint and Prettier to maintain consistent code style:
- TypeScript for all source files
- 2 spaces for indentation
- Single quotes for strings
- Semicolons required
- Maximum line length of 100 characters
- Clear and descriptive variable names
- JSDoc comments for public APIs
Run style checks:
```bash
npm run lint
```
Fix style issues automatically:
```bash
npm run lint:fix
```
## Testing Requirements
- All new features must include unit tests
- Integration tests required for database interactions
- Maintain or improve code coverage
- Test both success and error scenarios
- Mock external dependencies appropriately
Run tests with coverage:
```bash
npm run test:coverage
```
## Pull Request Process
1. **Branch Naming**
- Feature: `feature/description`
- Bug fix: `fix/description`
- Documentation: `docs/description`
- Performance: `perf/description`
2. **Before Submitting**
- Update documentation for new features
- Add/update tests
- Run linter and fix any issues
- Ensure all tests pass
- Update CHANGELOG.md
- Rebase on latest main branch
3. **PR Description**
- Clear description of changes
- Link to related issues
- List of breaking changes (if any)
- Screenshots (if UI changes)
- Steps to test changes
4. **Review Process**
- At least one maintainer review required
- Address all review comments
- Pass CI/CD checks
- Keep PR scope focused
## Commit Guidelines
Follow conventional commits specification:
```
type(scope): description
[optional body]
[optional footer]
```
Types:
- feat: New feature
- fix: Bug fix
- docs: Documentation changes
- style: Code style changes
- refactor: Code refactoring
- perf: Performance improvements
- test: Adding/updating tests
- chore: Maintenance tasks
Example:
```
feat(query): add support for parameterized queries
- Implement parameter validation
- Add type checking
- Update documentation
Closes #123
```
## Development Best Practices
1. **Error Handling**
- Use custom error classes
- Provide meaningful error messages
- Include context in error responses
- Log errors appropriately
2. **Security**
- Validate all inputs
- Use parameterized queries
- Follow least privilege principle
- Keep dependencies updated
3. **Performance**
- Use connection pooling
- Optimize database queries
- Handle resource cleanup
- Monitor memory usage
4. **Documentation**
- Keep README.md updated
- Document all public APIs
- Include usage examples
- Update CHANGELOG.md
## Getting Help
- Open an issue for bugs or feature requests
- Join our community chat for questions
- Check existing documentation and issues
- Contact maintainers for guidance
## Code of Conduct
Please note that this project is released with a Contributor Code of Conduct. By participating in this project you agree to abide by its terms.
## License
By contributing to MSSQL MCP Server, you agree that your contributions will be licensed under the MIT License.
```
--------------------------------------------------------------------------------
/CODE_OF_CONDUCT.md:
--------------------------------------------------------------------------------
```markdown
# Contributor Covenant Code of Conduct
## Our Pledge
We as members, contributors, and leaders pledge to make participation in our community a
harassment-free experience for everyone, regardless of age, body size, visible or invisible
disability, ethnicity, sex characteristics, gender identity and expression, level of experience,
education, socio-economic status, nationality, personal appearance, race, religion, or sexual
identity and orientation.
We pledge to act and interact in ways that contribute to an open, welcoming, diverse, inclusive, and
healthy community.
## Our Standards
Examples of behavior that contributes to a positive environment for our community include:
- Demonstrating empathy and kindness toward other people
- Being respectful of differing opinions, viewpoints, and experiences
- Giving and gracefully accepting constructive feedback
- Accepting responsibility and apologizing to those affected by our mistakes, and learning from the
experience
- Focusing on what is best not just for us as individuals, but for the overall community
Examples of unacceptable behavior include:
- The use of sexualized language or imagery, and sexual attention or advances of any kind
- Trolling, insulting or derogatory comments, and personal or political attacks
- Public or private harassment
- Publishing others' private information, such as a physical or email address, without their
explicit permission
- Other conduct which could reasonably be considered inappropriate in a professional setting
## Enforcement Responsibilities
Community leaders are responsible for clarifying and enforcing our standards of acceptable behavior
and will take appropriate and fair corrective action in response to any behavior that they deem
inappropriate, threatening, offensive, or harmful.
Community leaders have the right and responsibility to remove, edit, or reject comments, commits,
code, wiki edits, issues, and other contributions that are not aligned to this Code of Conduct, and
will communicate reasons for moderation decisions when appropriate.
## Scope
This Code of Conduct applies within all community spaces, and also applies when an individual is
officially representing the community in public spaces. Examples of representing our community
include using an official e-mail address, posting via an official social media account, or acting as
an appointed representative at an online or offline event.
## Enforcement
Instances of abusive, harassing, or otherwise unacceptable behavior may be reported to the community
leaders responsible for enforcement at [INSERT CONTACT METHOD]. All complaints will be reviewed and
investigated promptly and fairly.
All community leaders are obligated to respect the privacy and security of the reporter of any
incident.
## Enforcement Guidelines
Community leaders will follow these Community Impact Guidelines in determining the consequences for
any action they deem in violation of this Code of Conduct:
### 1. Correction
**Community Impact**: Use of inappropriate language or other behavior deemed unprofessional or
unwelcome in the community.
**Consequence**: A private, written warning from community leaders, providing clarity around the
nature of the violation and an explanation of why the behavior was inappropriate. A public apology
may be requested.
### 2. Warning
**Community Impact**: A violation through a single incident or series of actions.
**Consequence**: A warning with consequences for continued behavior. No interaction with the people
involved, including unsolicited interaction with those enforcing the Code of Conduct, for a
specified period of time. This includes avoiding interactions in community spaces as well as
external channels like social media. Violating these terms may lead to a temporary or permanent ban.
### 3. Temporary Ban
**Community Impact**: A serious violation of community standards, including sustained inappropriate
behavior.
**Consequence**: A temporary ban from any sort of interaction or public communication with the
community for a specified period of time. No public or private interaction with the people involved,
including unsolicited interaction with those enforcing the Code of Conduct, is allowed during this
period. Violating these terms may lead to a permanent ban.
### 4. Permanent Ban
**Community Impact**: Demonstrating a pattern of violation of community standards, including
sustained inappropriate behavior, harassment of an individual, or aggression toward or disparagement
of classes of individuals.
**Consequence**: A permanent ban from any sort of public interaction within the community.
## Attribution
This Code of Conduct is adapted from the [Contributor Covenant][homepage], version 2.1, available at
[https://www.contributor-covenant.org/version/2/1/code_of_conduct.html][v2.1].
Community Impact Guidelines were inspired by [Mozilla's code of conduct enforcement
ladder][Mozilla CoC].
For answers to common questions about this code of conduct, see the FAQ at
[https://www.contributor-covenant.org/faq][FAQ]. Translations are available at
[https://www.contributor-covenant.org/translations][translations].
[homepage]: https://www.contributor-covenant.org
[v2.1]: https://www.contributor-covenant.org/version/2/1/code_of_conduct.html
[Mozilla CoC]: https://github.com/mozilla/diversity
[FAQ]: https://www.contributor-covenant.org/faq
[translations]: https://www.contributor-covenant.org/translations
```
--------------------------------------------------------------------------------
/src/types/mcp.d.ts:
--------------------------------------------------------------------------------
```typescript
// We don't need these declarations anymore since the SDK now provides proper TypeScript typings
// Keeping this file as an empty declaration to avoid import errors
```
--------------------------------------------------------------------------------
/vitest.config.ts:
--------------------------------------------------------------------------------
```typescript
import { defineConfig } from 'vitest/config';
export default defineConfig({
test: {
globals: true,
environment: 'node',
include: ['src/**/*.test.ts'],
coverage: {
provider: 'v8',
reporter: ['text', 'lcov'],
include: ['src/**/*.ts'],
exclude: ['src/**/*.test.ts'],
},
},
});
```
--------------------------------------------------------------------------------
/scripts/build.sh:
--------------------------------------------------------------------------------
```bash
#!/bin/bash
# Exit on error
set -e
echo "Building MSSQL MCP Server..."
# Install dependencies if needed
if [ ! -d "node_modules" ]; then
echo "Installing dependencies..."
npm install
fi
# Run TypeScript compiler
echo "Compiling TypeScript..."
npm run build
# Make the build output executable
chmod +x build/index.js
echo "Build completed successfully!"
```
--------------------------------------------------------------------------------
/jest.setup.js:
--------------------------------------------------------------------------------
```javascript
// Load environment variables from .env.test
import dotenv from 'dotenv';
dotenv.config({ path: '.env.test' });
// Increase timeout for all tests
jest.setTimeout(10000);
// Mock console.error and console.warn to keep test output clean
global.console.error = jest.fn();
global.console.warn = jest.fn();
// Clear all mocks before each test
beforeEach(() => {
jest.clearAllMocks();
});
// Add custom matchers if needed
expect.extend({
// Add custom matchers here
});
```
--------------------------------------------------------------------------------
/tsconfig.json:
--------------------------------------------------------------------------------
```json
{
"compilerOptions": {
"target": "ES2022",
"module": "NodeNext",
"moduleResolution": "NodeNext",
"esModuleInterop": true,
"strict": true,
"skipLibCheck": true,
"forceConsistentCasingInFileNames": true,
"outDir": "build",
"declaration": true,
"sourceMap": true,
"rootDir": ".",
"noImplicitAny": true,
"noImplicitThis": true,
"noUnusedLocals": true,
"noUnusedParameters": true,
"noImplicitReturns": true,
"noFallthroughCasesInSwitch": true,
"allowSyntheticDefaultImports": true,
"types": ["vitest/globals", "node"]
},
"include": ["src/**/*", "vitest.config.ts"],
"exclude": ["node_modules", "build"]
}
```
--------------------------------------------------------------------------------
/scripts/test.sh:
--------------------------------------------------------------------------------
```bash
#!/bin/bash
# Exit on error
set -e
echo "Running MSSQL MCP Server tests..."
# Check if Docker is running
if ! docker info > /dev/null 2>&1; then
echo "Docker is not running. Please start Docker and try again."
exit 1
fi
# Start SQL Server container for testing
echo "Starting SQL Server container..."
docker run -d \
--name mssql-test \
-e "ACCEPT_EULA=Y" \
-e "SA_PASSWORD=YourTestPassword123!" \
-p 1433:1433 \
mcr.microsoft.com/mssql/server:2022-latest
# Wait for SQL Server to be ready
echo "Waiting for SQL Server to be ready..."
sleep 20
# Install dependencies if needed
if [ ! -d "node_modules" ]; then
echo "Installing dependencies..."
npm install
fi
# Run tests
echo "Running tests..."
npm test
# Cleanup
echo "Cleaning up..."
docker stop mssql-test
docker rm mssql-test
echo "Tests completed!"
```
--------------------------------------------------------------------------------
/.github/pull_request_template.md:
--------------------------------------------------------------------------------
```markdown
## Description
<!-- Describe your changes in detail -->
## Related Issue
<!-- Link to the issue that this PR addresses -->
## Type of Change
<!-- Mark the appropriate option with an "x" -->
- [ ] Bug fix (non-breaking change which fixes an issue)
- [ ] New feature (non-breaking change which adds functionality)
- [ ] Breaking change (fix or feature that would cause existing functionality to not work as expected)
- [ ] Documentation update
## Testing
<!-- Describe the tests you ran and their results -->
## Checklist
<!-- Mark completed items with an "x" -->
- [ ] My code follows the code style of this project
- [ ] I have added tests that prove my fix is effective or that my feature works
- [ ] I have updated the documentation accordingly
- [ ] All new and existing tests passed
- [ ] I have tested this change locally
```
--------------------------------------------------------------------------------
/jest.config.js:
--------------------------------------------------------------------------------
```javascript
/** @type {import('ts-jest').JestConfigWithTsJest} */
export default {
preset: 'ts-jest',
testEnvironment: 'node',
extensionsToTreatAsEsm: ['.ts'],
moduleNameMapper: {
'^(\\.{1,2}/.*)\\.js$': '$1',
},
transform: {
'^.+\\.tsx?$': [
'ts-jest',
{
useESM: true,
},
],
},
coverageDirectory: 'coverage',
collectCoverageFrom: [
'src/**/*.{ts,tsx}',
'!src/**/*.d.ts',
'!src/**/*.test.ts',
'!src/**/*.spec.ts',
'!src/types/**/*',
],
coverageThreshold: {
global: {
branches: 80,
functions: 80,
lines: 80,
statements: 80,
},
},
testMatch: ['**/__tests__/**/*.ts', '**/?(*.)+(spec|test).ts'],
testPathIgnorePatterns: ['/node_modules/', '/build/'],
setupFilesAfterEnv: ['<rootDir>/jest.setup.js'],
verbose: true,
testTimeout: 10000,
globals: {
'ts-jest': {
useESM: true,
},
},
};
```
--------------------------------------------------------------------------------
/.github/ISSUE_TEMPLATE/feature_request.md:
--------------------------------------------------------------------------------
```markdown
---
name: Feature request
about: Suggest an idea for this project
title: ''
labels: enhancement
assignees: ''
---
## Problem Statement
<!-- A clear and concise description of what the problem is. Ex. I'm always frustrated when [...] -->
## Proposed Solution
<!-- A clear and concise description of what you want to happen -->
## Alternative Solutions
<!-- A clear and concise description of any alternative solutions or features you've considered -->
## Example Usage
<!-- If applicable, provide example code or usage scenarios -->
```typescript
// Example code showing how the feature would be used
```
## Additional Context
<!-- Add any other context, screenshots, or examples about the feature request here -->
## Acceptance Criteria
<!-- List the requirements that need to be met for this feature to be considered complete -->
- [ ] Requirement 1
- [ ] Requirement 2
- [ ] Tests added/updated
- [ ] Documentation updated
```
--------------------------------------------------------------------------------
/docker-compose.yml:
--------------------------------------------------------------------------------
```yaml
version: '3.8'
services:
mssql:
image: mcr.microsoft.com/mssql/server:2022-latest
user: '0:0' # Run as root to avoid permission issues
environment:
- ACCEPT_EULA=Y
- SA_PASSWORD=YourStrongPassword123!
- MSSQL_PID=Developer
- MSSQL_AGENT_ENABLED=true
ports:
- '1433:1433'
volumes:
- mssql-data:/var/opt/mssql
cap_add:
- SYS_PTRACE
healthcheck:
test: /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P YourStrongPassword123! -Q 'SELECT 1' || exit 1
interval: 10s
timeout: 5s
retries: 5
start_period: 10s
app:
build:
context: .
dockerfile: Dockerfile.dev
volumes:
- .:/app
- /app/node_modules
environment:
- MSSQL_HOST=mssql
- MSSQL_PORT=1433
- MSSQL_USER=sa
- MSSQL_PASSWORD=YourStrongPassword123!
- MSSQL_DATABASE=master
- MSSQL_ENCRYPT=false
- MSSQL_TRUST_SERVER_CERTIFICATE=true
- NODE_ENV=development
depends_on:
mssql:
condition: service_healthy
command: npm run dev
volumes:
mssql-data:
```
--------------------------------------------------------------------------------
/Dockerfile:
--------------------------------------------------------------------------------
```dockerfile
# Build stage
FROM node:20-slim AS builder
# Create app directory
WORKDIR /app
# Install app dependencies
COPY package*.json ./
RUN npm ci
# Copy source code
COPY . .
# Build the application
RUN npm run build
# Production stage
FROM node:20-slim
# Install SQL Server tools
RUN apt-get update && apt-get install -y \
gnupg \
curl \
&& curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add - \
&& curl https://packages.microsoft.com/config/debian/11/prod.list > /etc/apt/sources.list.d/mssql-release.list \
&& apt-get update \
&& ACCEPT_EULA=Y apt-get install -y msodbcsql18 mssql-tools18 \
&& apt-get clean \
&& rm -rf /var/lib/apt/lists/*
# Create app directory
WORKDIR /app
# Copy package files
COPY package*.json ./
# Install production dependencies only
RUN npm ci --only=production
# Copy built application
COPY --from=builder /app/build ./build
# Set environment variables
ENV NODE_ENV=production
# Add node_modules/.bin to PATH
ENV PATH="/app/node_modules/.bin:${PATH}"
# Make the application executable
RUN chmod +x build/index.js
# Command to run the application
CMD ["node", "build/index.js"]
```
--------------------------------------------------------------------------------
/.github/ISSUE_TEMPLATE/config.yml:
--------------------------------------------------------------------------------
```yaml
blank_issues_enabled: false
contact_links:
- name: Documentation
url: https://github.com/yourusername/mssql-mcp-server/tree/main/docs
about: Check our documentation for answers to common questions
- name: Security Issues
url: https://github.com/yourusername/mssql-mcp-server/blob/main/SECURITY.md
about: Please report security vulnerabilities as described in our security policy
- name: Discussions
url: https://github.com/yourusername/mssql-mcp-server/discussions
about: Ask questions and discuss with other community members
- name: Contributing Guidelines
url: https://github.com/yourusername/mssql-mcp-server/blob/main/docs/CONTRIBUTING.md
about: Review our contributing guidelines before submitting a PR
# Template chooser settings
# This section configures how the template chooser appears
chooser:
title: Issue Templates
description: |
Please choose the appropriate template for your issue.
For security vulnerabilities, please follow our security policy.
preview: true
required: true
labels:
- bug: '🐛 Bug Report'
- enhancement: '✨ Feature Request'
- documentation: '📚 Documentation'
- question: '❓ Question'
```
--------------------------------------------------------------------------------
/src/evals/evals.ts:
--------------------------------------------------------------------------------
```typescript
//evals.ts
import { EvalConfig } from 'mcp-evals';
import { openai } from "@ai-sdk/openai";
import { grade, EvalFunction } from "mcp-evals";
const queryToolEvaluation: EvalFunction = {
name: 'query tool evaluation',
description: 'Evaluates the usage of the MSSQL query tool',
run: async () => {
const result = await grade(openai("gpt-4"), "Please use the 'query' tool to select the top 5 rows from the Person.Person table in the AdventureWorks database. Explain how to properly configure and use the tool to execute this query.");
return JSON.parse(result);
}
};
const mssqlServerEval: EvalFunction = {
name: 'mssql-server Evaluation',
description: 'Evaluates the mssql-server tool for query functionality',
run: async () => {
const result = await grade(openai("gpt-4"), "How do I use the 'query' tool in mssql-server to connect to a database at 'mydb.example.com' with user 'sa', password 'mySecretPass!' and run 'SELECT * FROM Employees'?");
return JSON.parse(result);
}
};
const config: EvalConfig = {
model: openai("gpt-4"),
evals: [queryToolEvaluation, mssqlServerEval]
};
export default config;
export const evals = [queryToolEvaluation, mssqlServerEval];
```
--------------------------------------------------------------------------------
/package.json:
--------------------------------------------------------------------------------
```json
{
"name": "mssql-mcp-server",
"version": "0.1.0",
"description": "MCP server for connecting to MSSQL databases",
"type": "module",
"main": "build/index.js",
"bin": {
"mssql-mcp-server": "build/index.js"
},
"scripts": {
"build": "tsc",
"build:unix": "tsc && chmod +x build/index.js",
"start": "node build/index.js",
"dev": "tsx watch src/index.ts",
"lint": "eslint . --ext .ts",
"format": "prettier --write \"src/**/*.ts\"",
"test": "vitest run",
"test:watch": "vitest",
"test:coverage": "vitest run --coverage"
},
"keywords": [
"mcp",
"mssql",
"database",
"sql",
"server"
],
"author": "",
"license": "MIT",
"dependencies": {
"@modelcontextprotocol/sdk": "^1.9.0",
"mcp-evals": "^1.0.18",
"mssql": "^10.0.1",
"zod": "^3.22.4"
},
"devDependencies": {
"@types/mssql": "^9.1.4",
"@types/node": "^20.10.6",
"@typescript-eslint/eslint-plugin": "^6.17.0",
"@typescript-eslint/parser": "^6.17.0",
"@vitest/coverage-v8": "^1.1.3",
"dotenv": "^16.5.0",
"eslint": "^8.56.0",
"eslint-config-prettier": "^9.1.0",
"eslint-plugin-prettier": "^5.1.2",
"eslint-plugin-vitest-globals": "^1.4.0",
"prettier": "^3.1.1",
"tsx": "^4.7.0",
"typescript": "^5.3.3",
"vitest": "^1.1.3"
}
}
```
--------------------------------------------------------------------------------
/src/types/index.ts:
--------------------------------------------------------------------------------
```typescript
export interface DatabaseConfig {
host: string;
port: number;
user: string;
password: string;
database?: string;
encrypt: boolean;
trustServerCertificate: boolean;
enableArithAbort: boolean;
pool: {
max: number;
min: number;
idleTimeoutMillis: number;
acquireTimeoutMillis: number;
};
queryTimeout: number;
multipleStatements: boolean;
rowsAsArray: boolean;
debug: boolean;
debugSql: boolean;
}
export interface QueryParams {
query: string;
params?: Record<string, unknown>;
database?: string;
timeout?: number;
}
export interface DatabaseError extends Error {
code?: string;
number?: number;
state?: string;
class?: number;
serverName?: string;
procName?: string;
lineNumber?: number;
}
export type EnvVars = {
MSSQL_HOST?: string;
MSSQL_PORT?: string;
MSSQL_USER?: string;
MSSQL_PASSWORD?: string;
MSSQL_DATABASE?: string;
MSSQL_ENCRYPT?: string;
MSSQL_TRUST_SERVER_CERTIFICATE?: string;
MSSQL_ENABLE_ARITH_ABORT?: string;
MSSQL_POOL_MAX?: string;
MSSQL_POOL_MIN?: string;
MSSQL_POOL_IDLE_TIMEOUT?: string;
MSSQL_POOL_ACQUIRE_TIMEOUT?: string;
MSSQL_QUERY_TIMEOUT?: string;
MSSQL_MULTIPLE_STATEMENTS?: string;
MSSQL_ROWS_AS_ARRAY?: string;
MSSQL_DEBUG?: string;
MSSQL_DEBUG_SQL?: string;
[key: string]: string | undefined;
};
```
--------------------------------------------------------------------------------
/src/utils/database.ts:
--------------------------------------------------------------------------------
```typescript
import { ConnectionPool, config as SQLConfig } from 'mssql';
import type { DatabaseConfig } from '../types/index.js';
export async function createConnectionPool(config: DatabaseConfig): Promise<ConnectionPool> {
const sqlConfig: SQLConfig = {
server: config.host,
port: config.port,
user: config.user,
password: config.password,
database: config.database,
options: {
encrypt: config.encrypt,
trustServerCertificate: config.trustServerCertificate,
enableArithAbort: config.enableArithAbort,
rowCollectionOnRequestCompletion: true,
},
pool: {
max: config.pool.max,
min: config.pool.min,
idleTimeoutMillis: config.pool.idleTimeoutMillis,
acquireTimeoutMillis: config.pool.acquireTimeoutMillis,
},
requestTimeout: config.queryTimeout,
arrayRowMode: config.rowsAsArray,
stream: false,
};
const pool = new ConnectionPool(sqlConfig);
try {
await pool.connect();
return pool;
} catch (error) {
// Ensure pool is closed if connection fails
await pool.close();
throw error;
}
}
export async function testConnection(pool: ConnectionPool): Promise<void> {
try {
await pool.request().query('SELECT 1');
} catch (error) {
throw new Error(`Failed to connect to database: ${(error as Error).message}`);
}
}
```
--------------------------------------------------------------------------------
/smithery.yaml:
--------------------------------------------------------------------------------
```yaml
# Smithery configuration file: https://smithery.ai/docs/config#smitheryyaml
startCommand:
type: stdio
configSchema:
# JSON Schema defining the configuration options for the MCP.
type: object
properties:
connectionString:
type: string
description: Full connection string to the MSSQL database
host:
type: string
description: Database server hostname
port:
type: number
default: 1433
description: Database server port
database:
type: string
default: master
description: Database name
username:
type: string
description: Database username
password:
type: string
description: Database password
encrypt:
type: boolean
default: false
description: Enable encryption
trustServerCertificate:
type: boolean
default: true
description: Trust server certificate
commandFunction:
# A function that produces the CLI command to start the MCP on stdio.
|-
(config) => ({ command: 'node', args: ['build/index.js'], env: { MSSQL_CONNECTION_STRING: config.connectionString, MSSQL_HOST: config.host, MSSQL_PORT: config.port ? config.port.toString() : undefined, MSSQL_DATABASE: config.database, MSSQL_USER: config.username, MSSQL_PASSWORD: config.password, MSSQL_ENCRYPT: config.encrypt ? 'true' : 'false', MSSQL_TRUST_SERVER_CERTIFICATE: config.trustServerCertificate ? 'true' : 'false' } })
```
--------------------------------------------------------------------------------
/CHANGELOG.md:
--------------------------------------------------------------------------------
```markdown
# Changelog
All notable changes to this project will be documented in this file.
The format is based on [Keep a Changelog](https://keepachangelog.com/en/1.0.0/),
and this project adheres to [Semantic Versioning](https://semver.org/spec/v2.0.0.html).
## [0.1.0] - 2024-01-28
### Added
- Initial release of the MSSQL MCP Server
- Basic query execution functionality
- Database listing capability
- Table listing with schema support
- Table schema description tool
- Connection pooling
- Comprehensive error handling
- Query parameter validation
- Security features:
- Query sanitization
- Parameter validation
- TLS support
- Connection encryption
- Documentation:
- API documentation
- Configuration guide
- Usage examples
- Contributing guidelines
- Development tools:
- TypeScript configuration
- ESLint and Prettier setup
- Jest testing framework
- Docker-based integration tests
- Build and test scripts
### Security
- Input validation for all query parameters
- Blocking of dangerous SQL commands
- Support for TLS encryption
- Connection pooling with configurable limits
- Environment variable based configuration
## [Unreleased]
### Planned Features
- Query result pagination
- Transaction support
- Stored procedure execution
- Bulk operations
- Schema creation/modification tools
- Database backup/restore tools
- Enhanced monitoring and logging
- Performance optimization tools
- Extended security features:
- Row-level security
- Column encryption
- Audit logging
- Additional documentation:
- Performance tuning guide
- Security best practices
- Troubleshooting guide
```
--------------------------------------------------------------------------------
/.github/ISSUE_TEMPLATE/bug_report.md:
--------------------------------------------------------------------------------
```markdown
---
name: Bug Report
about: Create a report to help us improve
title: '[BUG] '
labels: bug
assignees: ''
---
## Bug Description
<!-- A clear and concise description of what the bug is -->
## Environment
- MSSQL MCP Server Version:
- Node.js Version:
- SQL Server Version:
- Operating System:
- Docker Version (if applicable):
- Other relevant environment details:
## Steps to Reproduce
1. <!-- First Step -->
2. <!-- Second Step -->
3. <!-- And so on... -->
## Expected Behavior
<!-- What you expected to happen -->
## Actual Behavior
<!-- What actually happened -->
## Code Example
<!-- If applicable, provide a minimal code example that demonstrates the issue -->
```typescript
// Your code here
```
## Error Messages
<!-- If applicable, provide any error messages or stack traces -->
```
Error message here
```
## SQL Query
<!-- If the issue involves a specific SQL query, provide it here -->
```sql
-- Your query here
```
## Configuration
<!-- Provide relevant configuration settings (without sensitive information) -->
```json
{
"configuration": "settings"
}
```
## Additional Context
<!-- Add any other context about the problem here -->
## Possible Solution
<!-- If you have suggestions on how to fix the issue -->
## Screenshots
<!-- If applicable, add screenshots to help explain your problem -->
## Logs
<!-- Include relevant log output -->
```
Log output here
```
## Impact
<!-- Describe the impact of this bug on your usage of the server -->
## Workaround
<!-- If you found a workaround, please describe it here -->
## Related Issues
<!-- Link to related issues if any -->
## Checklist
<!-- Mark completed items with an "x" -->
- [ ] I have checked for similar issues
- [ ] I have included all relevant information
- [ ] I can reliably reproduce the issue
- [ ] I have included error messages/logs
- [ ] I have included steps to reproduce
- [ ] I have included my environment details
- [ ] I have removed sensitive information
```
--------------------------------------------------------------------------------
/.github/ISSUE_TEMPLATE/documentation.md:
--------------------------------------------------------------------------------
```markdown
---
name: Documentation
about: Report documentation issues or suggest improvements
title: '[DOCS] '
labels: documentation
assignees: ''
---
## Documentation Location
<!-- Specify which documentation needs to be updated -->
- [ ] README.md
- [ ] API Documentation
- [ ] Configuration Guide
- [ ] Examples
- [ ] Error Handling Guide
- [ ] Contributing Guidelines
- [ ] Security Policy
- [ ] Other (please specify)
## Type of Documentation Issue
- [ ] Missing information
- [ ] Incorrect information
- [ ] Unclear explanation
- [ ] Outdated content
- [ ] Broken links
- [ ] Code examples need improvement
- [ ] Formatting issues
- [ ] Translation needed
- [ ] Other (please specify)
## Current Documentation
<!-- If applicable, quote the current documentation -->
```markdown
Current documentation text here
```
## Suggested Changes
<!-- Describe what needs to be changed or added -->
```markdown
Suggested documentation text here
```
## Additional Context
<!-- Add any additional context about the documentation issue -->
## Code Examples
<!-- If suggesting changes to code examples, provide them here -->
```typescript
// Current example
```
```typescript
// Suggested example
```
## Screenshots
<!-- If applicable, add screenshots to help explain the issue -->
## Related Issues/PRs
<!-- Link to related issues or pull requests -->
## Checklist
- [ ] I have checked that this is not a duplicate issue
- [ ] I have provided clear examples of the current and desired documentation
- [ ] I have checked that all links in my suggested changes are valid
- [ ] I have removed any sensitive information from examples
- [ ] I have followed the project's documentation style guide
- [ ] I have included all necessary context for understanding the issue
## Implementation Notes
<!-- For maintainers -->
- [ ] Documentation style consistent
- [ ] Examples tested
- [ ] Links verified
- [ ] Cross-references updated
- [ ] Navigation updated if needed
- [ ] Table of contents updated if needed
```
--------------------------------------------------------------------------------
/scripts/setup-dev.sh:
--------------------------------------------------------------------------------
```bash
#!/bin/bash
# Exit on error
set -e
echo "Setting up MSSQL MCP Server development environment..."
# Check if Docker is installed
if ! command -v docker &> /dev/null; then
echo "Error: Docker is not installed. Please install Docker first."
exit 1
fi
# Check if Docker Compose is installed
if ! command -v docker-compose &> /dev/null; then
echo "Error: Docker Compose is not installed. Please install Docker Compose first."
exit 1
fi
# Check if Node.js is installed
if ! command -v node &> /dev/null; then
echo "Error: Node.js is not installed. Please install Node.js first."
exit 1
fi
# Check Node.js version
NODE_VERSION=$(node -v | cut -d'v' -f2)
if [ "$(printf '%s\n' "18.0.0" "$NODE_VERSION" | sort -V | head -n1)" = "18.0.0" ]; then
echo "Node.js version $NODE_VERSION detected (OK)"
else
echo "Error: Node.js version 18.0.0 or higher is required"
exit 1
fi
# Create .env file if it doesn't exist
if [ ! -f .env ]; then
echo "Creating .env file..."
cp .env.example .env
echo "Please edit .env file with your configuration"
fi
# Install dependencies
echo "Installing dependencies..."
npm install
# Create data directories if they don't exist
echo "Creating data directories..."
mkdir -p data/mssql
# Pull required Docker images
echo "Pulling Docker images..."
docker pull mcr.microsoft.com/mssql/server:2022-latest
# Build development containers
echo "Building development containers..."
docker-compose build
echo "Setting up Git hooks..."
npm run prepare
echo "Running linter..."
npm run lint
echo "Running tests..."
npm test
echo "Setup complete!"
echo ""
echo "To start the development environment:"
echo "1. Make sure Docker is running"
echo "2. Run: docker-compose up"
echo ""
echo "To run tests:"
echo "- Unit tests: npm test"
echo "- Integration tests: npm run test:integration"
echo ""
echo "To clean up:"
echo "- Stop containers: docker-compose down"
echo "- Remove volumes: docker-compose down -v"
echo ""
echo "Documentation available in:"
echo "- docs/README.md: Main documentation"
echo "- docker/README.md: Docker setup guide"
echo "- docs/api/: API documentation"
echo "- docs/examples/: Usage examples"
```
--------------------------------------------------------------------------------
/HowToRun.md:
--------------------------------------------------------------------------------
```markdown
# How to Run the MSSQL MCP Server
This guide explains how to run the MSSQL MCP Server after the upgrade to MCP SDK 1.9.0.
## Prerequisites
- Node.js 16 or later
- SQL Server instance (or access to one)
- Git (if cloning from repository)
## Installation
1. Clone the repository (if you haven't already):
```bash
git clone https://github.com/c0h1b4/mssql-mcp-server.git
cd mssql-mcp-server
```
2. Install dependencies:
```bash
npm install
```
## Configuration
1. Create a `.env` file based on the example:
```bash
cp .env.example .env
```
2. Edit the `.env` file to match your SQL Server configuration:
```
# SQL Server Connection Settings
MSSQL_HOST=your-server-host
MSSQL_PORT=1433
MSSQL_USER=your-username
MSSQL_PASSWORD=your-password
MSSQL_DATABASE=your-database
# Security Settings
MSSQL_ENCRYPT=true
MSSQL_TRUST_SERVER_CERTIFICATE=false
```
## Building the Server
Build the TypeScript code:
```bash
npm run build
```
## Running the Server
### Method 1: Direct Execution
```bash
npm start
```
### Method 2: Using the Example Server
```bash
npx tsx examples/simple-server.ts
```
### Method 3: Using Docker
```bash
# Start both the SQL Server and MCP Server
docker-compose up
# Or just the MCP Server (if you have a SQL Server elsewhere)
docker-compose up app
```
## Testing the Server
You can test the server using the MCP Inspector:
1. Install the inspector:
```bash
npm install -g @modelcontextprotocol/inspector
```
2. Run the inspector (in a separate terminal while the server is running):
```bash
mcp-inspector --transport=stdio --command="node build/index.js"
```
3. Use the inspector to:
- Browse available tools
- Execute SQL queries
- View responses
## Troubleshooting
- **Connection issues**: Check your SQL Server connection settings in `.env`
- **Build errors**: Make sure you have the latest dependencies with `npm install`
- **Permission errors**: Ensure your SQL user has the necessary permissions
- **Port conflicts**: Check if port 1433 is already in use
## Example Queries
Here are some example queries to try with the server:
```sql
-- List all databases
SELECT name FROM sys.databases
-- List tables in the current database
SELECT * FROM information_schema.tables
-- Simple query with parameters
SELECT * FROM your_table WHERE your_column = @value
```
For more detailed usage instructions, see the main [README.md](README.md).
```
--------------------------------------------------------------------------------
/TODO.md:
--------------------------------------------------------------------------------
```markdown
# MSSQL MCP Server Implementation TODO
## Phase 1: Project Setup
- [ ] Initialize new MCP server project
- [ ] Set up basic project structure
- [ ] Add core dependencies (mssql, dotenv)
- [ ] Configure TypeScript
- [ ] Set up testing framework
## Phase 2: Core Implementation
- [ ] Implement connection management
- [ ] Connection pooling setup
- [ ] Connection string validation
- [ ] Reconnection handling
- [ ] Add query execution functionality
- [ ] Basic query support
- [ ] Parameterized queries
- [ ] Transaction support
- [ ] Create database listing tool
- [ ] Filter options
- [ ] Permissions checking
- [ ] Create table listing tool
- [ ] Schema support
- [ ] Filter capabilities
- [ ] Create table description tool
- [ ] Column details
- [ ] Index information
- [ ] Foreign key relationships
- [ ] Implement error handling
- [ ] SQL error mapping
- [ ] Connection error handling
- [ ] Timeout handling
## Phase 3: Security & Validation
- [ ] Add input validation
- [ ] Query string validation
- [ ] Parameter type checking
- [ ] Size limits enforcement
- [ ] Implement query sanitization
- [ ] SQL injection prevention
- [ ] Dangerous command blocking
- [ ] Add parameterized query support
- [ ] Type mapping
- [ ] Bulk operations
- [ ] Set up connection pooling
- [ ] Pool size configuration
- [ ] Timeout settings
- [ ] Dead connection handling
- [ ] Add security best practices
- [ ] TLS/SSL support
- [ ] Credential management
- [ ] Access control
## Phase 4: Testing
- [ ] Write unit tests
- [ ] Connection tests
- [ ] Query tests
- [ ] Tool tests
- [ ] Write integration tests
- [ ] End-to-end scenarios
- [ ] Error scenarios
- [ ] Create test database setup script
- [ ] Sample data generation
- [ ] Clean up procedures
- [ ] Add test coverage reporting
- [ ] Coverage thresholds
- [ ] Report generation
## Phase 5: Documentation
- [ ] Write main README
- [ ] Installation guide
- [ ] Configuration options
- [ ] Basic usage examples
- [ ] Create API documentation
- [ ] Tool specifications
- [ ] Parameter details
- [ ] Response formats
- [ ] Add usage examples
- [ ] Basic queries
- [ ] Advanced scenarios
- [ ] Document error codes
- [ ] Error categories
- [ ] Troubleshooting guides
- [ ] Create contributing guidelines
- [ ] Code style guide
- [ ] PR process
- [ ] Testing requirements
## Phase 6: Final Steps
- [ ] Perform security audit
- [ ] Code review
- [ ] Dependency check
- [ ] Configuration review
- [ ] Add performance optimizations
- [ ] Query optimization
- [ ] Connection management
- [ ] Resource cleanup
- [ ] Create release workflow
- [ ] Version management
- [ ] Change documentation
- [ ] Release process
- [ ] Write changelog
- [ ] Version history
- [ ] Breaking changes
- [ ] Migration guides
```
--------------------------------------------------------------------------------
/src/utils/error.ts:
--------------------------------------------------------------------------------
```typescript
// This file is kept for backward compatibility with tests
// but the main error handling is now done within the tool implementation
import type { DatabaseError } from '../types/index.js';
export function handleError(error: unknown): Error {
if (error instanceof Error) {
return error;
}
const dbError = error as DatabaseError;
// SQL Server specific error codes
if (dbError.number) {
switch (dbError.number) {
// Login failed
case 18456:
return new Error('Authentication failed');
// Database does not exist
case 4060:
return new Error('Database does not exist');
// Object (table, view, etc.) does not exist
case 208:
return new Error('Object does not exist');
// Permission denied
case 229:
case 230:
return new Error('Insufficient permissions');
// Query timeout
case -2:
return new Error('Query execution timeout');
// Connection timeout
case -1:
return new Error('Connection timeout');
// Constraint violation
case 547:
return new Error('Operation would violate database constraints');
// Duplicate key
case 2601:
case 2627:
return new Error('Duplicate key value');
// Arithmetic overflow
case 8115:
return new Error('Arithmetic overflow error');
// String or binary data would be truncated
case 8152:
return new Error('Data would be truncated');
// Invalid object name
case 201:
return new Error('Invalid object name');
// Invalid column name
case 207:
return new Error('Invalid column name');
// Syntax error
case 102:
return new Error('SQL syntax error');
}
}
// Connection errors
if (dbError.code) {
switch (dbError.code) {
case 'ECONNREFUSED':
return new Error('Connection refused');
case 'ETIMEDOUT':
return new Error('Connection timed out');
case 'ENOTFOUND':
return new Error('Host not found');
case 'ENETUNREACH':
return new Error('Network unreachable');
}
}
// Generic error handling
const message = dbError.message || 'An unknown error occurred';
return new Error(message);
}
export function isTransientError(error: unknown): boolean {
const dbError = error as DatabaseError;
// SQL Server transient error numbers
const transientErrors = [
-2, // Timeout
701, // Out of memory
921, // Database has not been recovered yet
1204, // Lock issue
1205, // Deadlock victim
1221, // Resource lock validation
40143, // Azure SQL connection issue
40197, // Azure SQL error processing request
40501, // Azure SQL service busy
40613, // Azure SQL Database not currently available
];
return (
transientErrors.includes(dbError.number || 0) ||
dbError.code === 'ETIMEDOUT' ||
dbError.code === 'ECONNRESET' ||
dbError.code === 'EPIPE'
);
}
```
--------------------------------------------------------------------------------
/UPGRADE.md:
--------------------------------------------------------------------------------
```markdown
# Upgrade to MCP SDK 1.9.0
This document describes the upgrade from Model Context Protocol SDK version 0.1.0 to 1.9.0.
## Major Changes
The MCP SDK has undergone significant changes between version 0.1.0 and 1.9.0, including:
1. Introduction of a higher-level `McpServer` class that simplifies server creation
2. Improved parameter validation with Zod schemas instead of custom validation functions
3. Simplified tool definition and error handling
4. Better type safety and IntelliSense support
## Upgrade Steps
The following changes were made to upgrade the codebase:
### 1. Package Dependencies
Updated `package.json` to use the latest SDK version:
```diff
- "@modelcontextprotocol/sdk": "^0.1.0",
+ "@modelcontextprotocol/sdk": "^1.9.0",
```
And added the required Zod dependency:
```
npm install zod
```
### 2. Server Implementation
The main implementation class was updated to use the new API:
```diff
- import { Server } from '@modelcontextprotocol/sdk/server/index.js';
+ import { McpServer } from '@modelcontextprotocol/sdk/server/mcp.js';
```
### 3. Schema Validation
Replaced custom validation with Zod schemas but with a format that works with the tool API:
```diff
- const isValidQueryArgs = (args: unknown): args is QueryArgs => {
- // Custom validation logic...
- };
+ server.tool(
+ 'query',
+ {
+ connectionString: z.string().optional(),
+ host: z.string().optional(),
+ // etc.
+ },
+ async (args) => {
+ // ...
+ }
+ );
```
### 4. Method Names
Some method names have changed in the new API:
```diff
- await this.server.disconnect();
+ await this.server.close();
```
### 5. Error Handling
Switched from McpError objects to standard Error or returned error responses:
```diff
- throw new McpError(ErrorCode.InternalError, `Database error: ${message}`);
+ throw new Error(`Database error: ${message}`);
// OR
+ return {
+ content: [{ type: 'text', text: `Database error: ${message}` }],
+ isError: true,
+ };
```
## Breaking Changes
1. The low-level `Server` class has been replaced with the higher-level `McpServer` class
2. Request handlers are now registered using more concise methods like `tool()`, `resource()`, etc.
3. Error handling is now done by returning objects with `isError: true` instead of throwing exceptions
4. Parameter validation is now done using Zod schemas in a format expected by the tool API
5. Method names have changed - `disconnect()` is now `close()`
## Testing
After upgrading, make sure to run tests to verify the functionality:
```
npm test
```
Note that test files have been updated to accommodate the new API changes.
## Known Issues
When using Zod schemas with the tool API, you need to use the individual schema properties as an object instead of directly passing a ZodEffects or ZodObject:
```diff
- server.tool('query', QueryArgsSchema, async (args) => {/* ... */})
+ server.tool('query',
+ {
+ connectionString: z.string().optional(),
+ // other schema properties...
+ },
+ async (args) => {/* ... */}
+ )
```
```
--------------------------------------------------------------------------------
/src/index.test.ts:
--------------------------------------------------------------------------------
```typescript
import { describe, expect, vi, beforeEach, afterEach, type SpyInstance } from 'vitest';
interface MockRequest {
query: SpyInstance;
}
interface MockPool {
request: () => MockRequest;
close: SpyInstance;
connect: SpyInstance;
}
// Mock the mssql module
vi.mock('mssql', () => {
const mockRequest: MockRequest = {
query: vi.fn().mockResolvedValue({ recordset: [{ id: 1, name: 'Test' }] }),
};
const mockPool: MockPool = {
request: () => mockRequest,
close: vi.fn(),
connect: vi.fn(),
};
return {
default: {
ConnectionPool: vi.fn(() => mockPool),
},
};
});
// Import after mocking
import { MssqlServer } from './index.js';
describe('MssqlServer', () => {
let server: MssqlServer;
const mockQuery = 'SELECT * FROM TestTable';
beforeEach(() => {
server = new MssqlServer();
});
afterEach(() => {
vi.clearAllMocks();
});
describe('query tool', () => {
it('should execute a query with connection string', async () => {
// Get access to private methods via any cast for testing
const serverAny = server as any;
// Execute our getConnectionConfig directly
const response = await serverAny.getConnectionConfig({
connectionString: 'Server=localhost;Database=test;User Id=sa;Password=test;',
query: mockQuery,
});
expect(response).toEqual({
server: 'Server=localhost;Database=test;User Id=sa;Password=test;',
});
});
it('should execute a query with individual parameters', async () => {
// Get access to private methods via any cast for testing
const serverAny = server as any;
// Execute our method directly
const response = await serverAny.getConnectionConfig({
host: 'localhost',
username: 'sa',
password: 'test',
query: mockQuery,
});
expect(response).toEqual({
server: 'localhost',
port: 1433,
database: 'master',
user: 'sa',
password: 'test',
options: {
encrypt: false,
trustServerCertificate: true,
},
});
});
it('should handle database errors', async () => {
const mockPool = new (await import('mssql')).default.ConnectionPool({} as any) as unknown as MockPool;
const querySpy = mockPool.request().query as SpyInstance;
// Make the query function throw an error this time
querySpy.mockRejectedValueOnce(new Error('Database error'));
// Get access to private methods via any cast for testing
const serverAny = server as any;
// Create and get the pool
const config = serverAny.getConnectionConfig({
host: 'localhost',
username: 'sa',
password: 'test',
query: mockQuery,
});
// Test directly with the SDK's result object structure
await expect(async () => {
const pool = await serverAny.getPool(config);
await pool.request().query(mockQuery);
}).rejects.toThrow();
});
});
});
```
--------------------------------------------------------------------------------
/src/utils/validation.ts:
--------------------------------------------------------------------------------
```typescript
// This file is kept for backward compatibility with tests
// but the main validation logic is now handled by Zod schemas
import { z } from 'zod';
import type { QueryParams } from '../types/index.js';
const MAX_QUERY_LENGTH = 1000000; // 1MB
const DANGEROUS_COMMANDS = ['DROP', 'TRUNCATE', 'ALTER', 'CREATE', 'EXEC', 'EXECUTE', 'sp_', 'xp_'];
// Schema for validating query parameters
export const QueryParamsSchema = z.object({
query: z.string().min(1).max(MAX_QUERY_LENGTH),
params: z.record(z.unknown()).optional(),
database: z.string().max(128).optional(),
timeout: z.number().min(0).max(3600000).optional(),
});
// Legacy validation function kept for test compatibility
export function validateQueryParams(params: QueryParams): void {
// Check query presence
if (!params.query) {
throw new Error('Query is required');
}
// Check query length
if (params.query.length > MAX_QUERY_LENGTH) {
throw new Error(`Query exceeds maximum length of ${MAX_QUERY_LENGTH} characters`);
}
// Check for dangerous commands
const upperQuery = params.query.toUpperCase();
for (const command of DANGEROUS_COMMANDS) {
if (upperQuery.includes(command)) {
throw new Error(`Query contains forbidden command: ${command}`);
}
}
// Validate database name if provided
if (params.database) {
validateDatabaseName(params.database);
}
// Validate timeout if provided
if (params.timeout !== undefined) {
validateTimeout(params.timeout);
}
// Validate parameters if provided
if (params.params) {
validateQueryParameters(params.params);
}
}
function validateDatabaseName(name: string): void {
// Check for SQL injection in database name
const invalidChars = /[;'"\\]/;
if (invalidChars.test(name)) {
throw new Error('Database name contains invalid characters');
}
// Check database name length
if (name.length > 128) {
throw new Error('Database name exceeds maximum length of 128 characters');
}
}
function validateTimeout(timeout: number): void {
if (typeof timeout !== 'number') {
throw new Error('Timeout must be a number');
}
if (timeout < 0) {
throw new Error('Timeout cannot be negative');
}
if (timeout > 3600000) {
// 1 hour
throw new Error('Timeout cannot exceed 1 hour');
}
}
function validateQueryParameters(params: Record<string, unknown>): void {
for (const [key, value] of Object.entries(params)) {
// Validate parameter name
if (!/^[a-zA-Z0-9_]+$/.test(key)) {
throw new Error(`Invalid parameter name: ${key}`);
}
// Validate parameter value
if (!isValidParameterValue(value)) {
throw new Error(`Invalid parameter value for ${key}`);
}
}
}
function isValidParameterValue(value: unknown): boolean {
if (value === null) {
return true;
}
switch (typeof value) {
case 'string':
return value.length <= 8000; // Max NVARCHAR length
case 'number':
return !isNaN(value) && isFinite(value);
case 'boolean':
return true;
case 'object':
if (value instanceof Date) {
return !isNaN(value.getTime());
}
if (value instanceof Buffer) {
return value.length <= 2147483647; // Max VARBINARY length
}
return false;
default:
return false;
}
}
```
--------------------------------------------------------------------------------
/src/utils/config.ts:
--------------------------------------------------------------------------------
```typescript
import type { DatabaseConfig, EnvVars } from '../types/index.js';
const DEFAULT_PORT = 1433;
const DEFAULT_POOL_MAX = 10;
const DEFAULT_POOL_MIN = 0;
const DEFAULT_POOL_IDLE_TIMEOUT = 30000;
const DEFAULT_POOL_ACQUIRE_TIMEOUT = 15000;
const DEFAULT_QUERY_TIMEOUT = 30000;
export function validateConfig(env: EnvVars): DatabaseConfig {
// Required settings
if (!env.MSSQL_HOST) {
throw new Error('MSSQL_HOST is required');
}
if (!env.MSSQL_USER) {
throw new Error('MSSQL_USER is required');
}
if (!env.MSSQL_PASSWORD) {
throw new Error('MSSQL_PASSWORD is required');
}
// Parse and validate port
const port = parseInt(env.MSSQL_PORT || String(DEFAULT_PORT), 10);
if (isNaN(port) || port <= 0 || port > 65535) {
throw new Error('Invalid MSSQL_PORT value');
}
// Parse pool settings
const poolMax = parseInt(env.MSSQL_POOL_MAX || String(DEFAULT_POOL_MAX), 10);
const poolMin = parseInt(env.MSSQL_POOL_MIN || String(DEFAULT_POOL_MIN), 10);
const poolIdleTimeout = parseInt(
env.MSSQL_POOL_IDLE_TIMEOUT || String(DEFAULT_POOL_IDLE_TIMEOUT),
10
);
const poolAcquireTimeout = parseInt(
env.MSSQL_POOL_ACQUIRE_TIMEOUT || String(DEFAULT_POOL_ACQUIRE_TIMEOUT),
10
);
// Validate pool settings
if (isNaN(poolMax) || poolMax <= 0) {
throw new Error('Invalid MSSQL_POOL_MAX value');
}
if (isNaN(poolMin) || poolMin < 0) {
throw new Error('Invalid MSSQL_POOL_MIN value');
}
if (poolMin > poolMax) {
throw new Error('MSSQL_POOL_MIN cannot be greater than MSSQL_POOL_MAX');
}
if (isNaN(poolIdleTimeout) || poolIdleTimeout < 0) {
throw new Error('Invalid MSSQL_POOL_IDLE_TIMEOUT value');
}
if (isNaN(poolAcquireTimeout) || poolAcquireTimeout < 0) {
throw new Error('Invalid MSSQL_POOL_ACQUIRE_TIMEOUT value');
}
// Parse query timeout
const queryTimeout = parseInt(env.MSSQL_QUERY_TIMEOUT || String(DEFAULT_QUERY_TIMEOUT), 10);
if (isNaN(queryTimeout) || queryTimeout < 0) {
throw new Error('Invalid MSSQL_QUERY_TIMEOUT value');
}
// Parse boolean settings
const encrypt = parseBooleanConfig(env.MSSQL_ENCRYPT, true);
const trustServerCertificate = parseBooleanConfig(env.MSSQL_TRUST_SERVER_CERTIFICATE, false);
const enableArithAbort = parseBooleanConfig(env.MSSQL_ENABLE_ARITH_ABORT, true);
const multipleStatements = parseBooleanConfig(env.MSSQL_MULTIPLE_STATEMENTS, false);
const rowsAsArray = parseBooleanConfig(env.MSSQL_ROWS_AS_ARRAY, false);
const debug = parseBooleanConfig(env.MSSQL_DEBUG, false);
const debugSql = parseBooleanConfig(env.MSSQL_DEBUG_SQL, false);
return {
host: env.MSSQL_HOST,
port,
user: env.MSSQL_USER,
password: env.MSSQL_PASSWORD,
database: env.MSSQL_DATABASE,
encrypt,
trustServerCertificate,
enableArithAbort,
pool: {
max: poolMax,
min: poolMin,
idleTimeoutMillis: poolIdleTimeout,
acquireTimeoutMillis: poolAcquireTimeout,
},
queryTimeout,
multipleStatements,
rowsAsArray,
debug,
debugSql,
};
}
function parseBooleanConfig(value: string | undefined, defaultValue: boolean): boolean {
if (value === undefined) {
return defaultValue;
}
const lowercaseValue = value.toLowerCase();
if (lowercaseValue === 'true' || lowercaseValue === '1') {
return true;
}
if (lowercaseValue === 'false' || lowercaseValue === '0') {
return false;
}
throw new Error(`Invalid boolean value: ${value}`);
}
```
--------------------------------------------------------------------------------
/examples/simple-server.ts:
--------------------------------------------------------------------------------
```typescript
#!/usr/bin/env node
import { McpServer } from '@modelcontextprotocol/sdk/server/mcp.js';
import { StdioServerTransport } from '@modelcontextprotocol/sdk/server/stdio.js';
import sql from 'mssql';
import { z } from 'zod';
// Define the main server class
async function main() {
// Create a new MCP server
const server = new McpServer({
name: 'mssql-demo-server',
version: '1.0.0',
});
// Store pools for connection reuse
const pools = new Map<string, sql.ConnectionPool>();
// Helper function to get connection config
function getConnectionConfig(args: any): sql.config {
if (args.connectionString) {
return {
server: args.connectionString,
};
}
return {
server: args.host,
port: args.port || 1433,
database: args.database || 'master',
user: args.username,
password: args.password,
options: {
encrypt: args.encrypt ?? false,
trustServerCertificate: args.trustServerCertificate ?? true,
},
};
}
// Helper function to get/create a connection pool
async function getPool(config: sql.config): Promise<sql.ConnectionPool> {
const key = JSON.stringify(config);
let pool = pools.get(key);
if (!pool) {
pool = new sql.ConnectionPool(config);
await pool.connect();
pools.set(key, pool);
}
return pool;
}
// Register the query tool
server.tool(
'query',
{
connectionString: z.string().optional(),
host: z.string().optional(),
port: z.number().optional(),
database: z.string().optional(),
username: z.string().optional(),
password: z.string().optional(),
query: z.string(),
encrypt: z.boolean().optional(),
trustServerCertificate: z.boolean().optional(),
},
async (args) => {
try {
const config = getConnectionConfig(args);
const pool = await getPool(config);
const result = await pool.request().query(args.query);
return {
content: [
{
type: 'text',
text: JSON.stringify(result.recordset, null, 2),
},
],
};
} catch (error) {
const message = error instanceof Error ? error.message : String(error);
return {
content: [{ type: 'text', text: `Database error: ${message}` }],
isError: true,
};
}
}
);
// Add examples resource that shows usage examples
server.resource(
'examples',
'examples://mssql',
async (uri) => ({
contents: [{
uri: uri.href,
text: `
# MSSQL MCP Server Examples
This server provides a 'query' tool to execute SQL queries. Here are some examples:
## Simple SELECT query
\`\`\`
query({
host: "localhost",
username: "sa",
password: "YourPassword123!",
query: "SELECT TOP 10 * FROM master.sys.objects"
})
\`\`\`
## Using a connection string
\`\`\`
query({
connectionString: "Server=localhost;Database=master;User Id=sa;Password=YourPassword123!;",
query: "SELECT @@VERSION AS SqlVersion"
})
\`\`\`
## Query with filter
\`\`\`
query({
host: "localhost",
username: "sa",
password: "YourPassword123!",
database: "AdventureWorks",
query: "SELECT TOP 5 * FROM Person.Person WHERE FirstName LIKE 'A%'"
})
\`\`\`
`
}]
})
);
// Connect to the transport
const transport = new StdioServerTransport();
await server.connect(transport);
console.error('MSSQL demo server running on stdio transport');
// Set up cleanup on exit
process.on('SIGINT', async () => {
const closePromises = Array.from(pools.values()).map((pool) => pool.close());
await Promise.all(closePromises);
pools.clear();
await server.close();
process.exit(0);
});
}
main().catch(console.error);
```
--------------------------------------------------------------------------------
/src/__tests__/server.test.ts:
--------------------------------------------------------------------------------
```typescript
import { config } from 'dotenv';
import { ConnectionPool } from 'mssql';
import { validateConfig } from '../utils/config.js';
import { createConnectionPool } from '../utils/database.js';
import { handleError } from '../utils/error.js';
import { validateQueryParams } from '../utils/validation.js';
import type { DatabaseConfig, QueryParams } from '../types/index.js';
// Load test environment variables
config({ path: '.env.test' });
describe('MSSQL MCP Server', () => {
let testConfig: DatabaseConfig;
let pool: ConnectionPool;
beforeAll(async () => {
testConfig = validateConfig(process.env);
pool = await createConnectionPool(testConfig);
});
afterAll(async () => {
await pool?.close();
});
describe('Configuration', () => {
it('should validate configuration correctly', () => {
const config = validateConfig(process.env);
expect(config).toBeDefined();
expect(config.host).toBe(process.env.MSSQL_HOST);
expect(config.port).toBe(Number(process.env.MSSQL_PORT));
});
it('should throw error for missing required config', () => {
const invalidEnv = { ...process.env };
delete invalidEnv.MSSQL_HOST;
expect(() => validateConfig(invalidEnv)).toThrow('MSSQL_HOST is required');
});
});
describe('Query Validation', () => {
it('should validate correct query params', () => {
const params: QueryParams = {
query: 'SELECT 1',
};
expect(() => validateQueryParams(params)).not.toThrow();
});
it('should throw error for missing query', () => {
const params = {} as QueryParams;
expect(() => validateQueryParams(params)).toThrow('Query is required');
});
it('should throw error for dangerous commands', () => {
const params: QueryParams = {
query: 'DROP TABLE Users',
};
expect(() => validateQueryParams(params)).toThrow('Query contains forbidden command');
});
it('should validate query parameters', () => {
const params: QueryParams = {
query: 'SELECT * FROM Users WHERE Id = @id',
params: {
id: 1,
},
};
expect(() => validateQueryParams(params)).not.toThrow();
});
it('should throw error for invalid parameter values', () => {
const params: QueryParams = {
query: 'SELECT * FROM Users WHERE Id = @id',
params: {
id: Symbol('invalid'),
},
};
expect(() => validateQueryParams(params)).toThrow('Invalid parameter value');
});
});
describe('Error Handling', () => {
it('should handle SQL errors correctly', () => {
const sqlError = new Error('SQL error');
Object.assign(sqlError, { number: 208 });
const mcpError = handleError(sqlError);
expect(mcpError.message).toContain('Object does not exist');
});
it('should handle connection errors correctly', () => {
const connError = new Error('Connection error');
Object.assign(connError, { code: 'ECONNREFUSED' });
const mcpError = handleError(connError);
expect(mcpError.message).toContain('Connection refused');
});
it('should handle unknown errors correctly', () => {
const unknownError = new Error('Unknown error');
const mcpError = handleError(unknownError);
expect(mcpError.message).toContain('Unknown error');
});
});
describe('Database Operations', () => {
it('should connect to database successfully', async () => {
const pool = await createConnectionPool(testConfig);
expect(pool).toBeDefined();
expect(pool.connected).toBe(true);
await pool.close();
});
it('should execute simple query successfully', async () => {
const result = await pool.request().query('SELECT 1 as value');
expect(result.recordset[0].value).toBe(1);
});
it('should handle parameterized queries', async () => {
const value = 42;
const result = await pool
.request()
.input('value', value)
.query('SELECT @value as value');
expect(result.recordset[0].value).toBe(value);
});
});
});
```
--------------------------------------------------------------------------------
/src/index.ts:
--------------------------------------------------------------------------------
```typescript
#!/usr/bin/env node
import { McpServer } from '@modelcontextprotocol/sdk/server/mcp.js';
import { StdioServerTransport } from '@modelcontextprotocol/sdk/server/stdio.js';
import sql from 'mssql';
import { z } from 'zod';
// Define the schema for the query parameters
const QueryArgsSchema = z.object({
connectionString: z.string().optional(),
host: z.string().optional(),
port: z.number().optional(),
database: z.string().optional(),
username: z.string().optional(),
password: z.string().optional(),
query: z.string(),
encrypt: z.boolean().optional(),
trustServerCertificate: z.boolean().optional(),
}).refine(
(data) => {
// Either connectionString OR (host + username + password) must be provided
return (
(data.connectionString !== undefined) ||
(data.host !== undefined && data.username !== undefined && data.password !== undefined)
);
},
{
message: 'Either connectionString OR (host, username, and password) must be provided',
}
);
// Type inference from the schema
type QueryArgs = z.infer<typeof QueryArgsSchema>;
export class MssqlServer {
private server: McpServer;
private pools: Map<string, sql.ConnectionPool>;
constructor() {
this.server = new McpServer({
name: 'mssql-server',
version: '0.1.0',
});
this.pools = new Map();
this.setupTools();
// Error handling
process.on('SIGINT', () => {
void this.cleanup();
process.exit(0);
});
}
private async cleanup(): Promise<void> {
const closePromises = Array.from(this.pools.values()).map((pool) => pool.close());
await Promise.all(closePromises);
this.pools.clear();
// The close method in the new API
await this.server.close();
}
private getConnectionConfig(args: QueryArgs): sql.config {
if (args.connectionString) {
return {
server: args.connectionString, // Using server instead of connectionString as per mssql types
};
}
return {
server: args.host!,
port: args.port || 1433,
database: args.database || 'master',
user: args.username,
password: args.password,
options: {
encrypt: args.encrypt ?? false,
trustServerCertificate: args.trustServerCertificate ?? true,
},
};
}
private async getPool(config: sql.config): Promise<sql.ConnectionPool> {
const key = JSON.stringify(config);
let pool = this.pools.get(key);
if (!pool) {
pool = new sql.ConnectionPool(config);
await pool.connect();
this.pools.set(key, pool);
}
return pool;
}
private setupTools(): void {
// Define the query tool using the raw object form instead of ZodSchema
this.server.tool(
'query',
{
connectionString: z.string().optional(),
host: z.string().optional(),
port: z.number().optional(),
database: z.string().optional(),
username: z.string().optional(),
password: z.string().optional(),
query: z.string(),
encrypt: z.boolean().optional(),
trustServerCertificate: z.boolean().optional(),
},
async (args) => {
try {
const config = this.getConnectionConfig(args as QueryArgs);
const pool = await this.getPool(config);
const result = await pool.request().query(args.query);
return {
content: [
{
type: 'text',
text: JSON.stringify(result.recordset, null, 2),
},
],
};
} catch (error) {
const message = error instanceof Error ? error.message : String(error);
return {
content: [{ type: 'text', text: `Database error: ${message}` }],
isError: true,
};
}
}
);
}
async run(): Promise<void> {
const transport = new StdioServerTransport();
await this.server.connect(transport);
console.error('MSSQL MCP server running on stdio');
}
}
// Only start the server if this file is being run directly
if (import.meta.url === `file://${process.argv[1]}`) {
const server = new MssqlServer();
void server.run().catch((error) => console.error('Server error:', error));
}
```
--------------------------------------------------------------------------------
/docs/examples/basic-queries.md:
--------------------------------------------------------------------------------
```markdown
# Basic Query Examples
This document provides examples of common query patterns using the MSSQL MCP Server.
## Simple Queries
### Select All Records
```json
{
"tool": "query",
"arguments": {
"query": "SELECT * FROM Users"
}
}
```
### Filter Records
```json
{
"tool": "query",
"arguments": {
"query": "SELECT * FROM Products WHERE Price > @minPrice",
"params": {
"minPrice": 100
}
}
}
```
### Insert Record
```json
{
"tool": "query",
"arguments": {
"query": "INSERT INTO Customers (Name, Email) VALUES (@name, @email)",
"params": {
"name": "John Doe",
"email": "[email protected]"
}
}
}
```
### Update Records
```json
{
"tool": "query",
"arguments": {
"query": "UPDATE Orders SET Status = @status WHERE OrderId = @orderId",
"params": {
"status": "Shipped",
"orderId": 12345
}
}
}
```
### Delete Records
```json
{
"tool": "query",
"arguments": {
"query": "DELETE FROM Cart WHERE LastUpdated < @cutoffDate",
"params": {
"cutoffDate": "2024-01-01"
}
}
}
```
## Working with Different Databases
### Query Specific Database
```json
{
"tool": "query",
"arguments": {
"database": "Inventory",
"query": "SELECT * FROM Stock WHERE Quantity < @threshold",
"params": {
"threshold": 10
}
}
}
```
### List Available Databases
```json
{
"tool": "list_databases",
"arguments": {
"filter": "Prod%"
}
}
```
### List Tables in Database
```json
{
"tool": "list_tables",
"arguments": {
"database": "Sales",
"schema": "dbo",
"filter": "Order%"
}
}
```
## Schema Operations
### Get Table Schema
```json
{
"tool": "describe_table",
"arguments": {
"database": "HR",
"schema": "dbo",
"table": "Employees"
}
}
```
## Data Types
### Working with Dates
```json
{
"tool": "query",
"arguments": {
"query": "SELECT * FROM Orders WHERE OrderDate BETWEEN @start AND @end",
"params": {
"start": "2024-01-01",
"end": "2024-12-31"
}
}
}
```
### Binary Data
```json
{
"tool": "query",
"arguments": {
"query": "INSERT INTO Documents (Name, Content) VALUES (@name, @content)",
"params": {
"name": "example.pdf",
"content": Buffer.from("binary content")
}
}
}
```
### Decimal Values
```json
{
"tool": "query",
"arguments": {
"query": "UPDATE Products SET Price = @price WHERE ProductId = @id",
"params": {
"price": 99.99,
"id": 1
}
}
}
```
## Error Handling Examples
### Handle Missing Table
```json
{
"tool": "query",
"arguments": {
"query": "SELECT * FROM NonExistentTable",
"onError": {
"action": "continue",
"defaultValue": []
}
}
}
```
### Transaction Rollback
```json
{
"tool": "query",
"arguments": {
"query": `
BEGIN TRANSACTION;
INSERT INTO Orders (CustomerId, Total) VALUES (@customerId, @total);
UPDATE Inventory SET Stock = Stock - @quantity WHERE ProductId = @productId;
COMMIT;
`,
"params": {
"customerId": 1,
"total": 150.00,
"quantity": 2,
"productId": 100
},
"onError": {
"action": "rollback"
}
}
}
```
## Performance Optimization
### Using TOP for Limited Results
```json
{
"tool": "query",
"arguments": {
"query": "SELECT TOP 10 * FROM Products ORDER BY Price DESC"
}
}
```
### Pagination
```json
{
"tool": "query",
"arguments": {
"query": `
SELECT * FROM Orders
ORDER BY OrderDate DESC
OFFSET @offset ROWS
FETCH NEXT @pageSize ROWS ONLY
`,
"params": {
"offset": 0,
"pageSize": 20
}
}
}
```
### Optimized Joins
```json
{
"tool": "query",
"arguments": {
"query": `
SELECT o.OrderId, c.Name, p.ProductName
FROM Orders o
INNER JOIN Customers c ON o.CustomerId = c.CustomerId
INNER JOIN Products p ON o.ProductId = p.ProductId
WHERE o.OrderDate >= @since
`,
"params": {
"since": "2024-01-01"
}
}
}
```
## Best Practices
1. **Always Use Parameters**
- Prevents SQL injection
- Improves query plan caching
- Handles data type conversion
2. **Set Appropriate Timeouts**
- Long-running queries
- Background operations
- Report generation
3. **Handle Transactions**
- Use explicit transactions
- Implement proper error handling
- Consider isolation levels
4. **Optimize Performance**
- Use appropriate indexes
- Limit result sets
- Implement pagination
- Monitor query execution plans
```
--------------------------------------------------------------------------------
/.github/workflows/ci.yml:
--------------------------------------------------------------------------------
```yaml
name: CI
on:
push:
branches: [main]
pull_request:
branches: [main]
jobs:
test:
runs-on: ubuntu-latest
services:
mssql:
image: mcr.microsoft.com/mssql/server:2019-CU18-ubuntu-20.04
env:
ACCEPT_EULA: Y
SA_PASSWORD: YourTestPassword123!
MSSQL_PID: Developer
MSSQL_TCP_PORT: 1433
ports:
- 1433:1433
options: >-
--health-cmd="/opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P 'YourTestPassword123!' -Q 'SELECT 1' || exit 1"
--health-interval=10s
--health-timeout=5s
--health-retries=5
--health-start-period=20s
--memory=2048MB
strategy:
matrix:
node-version: [18.x, 20.x]
steps:
- uses: actions/checkout@v4
- name: Use Node.js ${{ matrix.node-version }}
uses: actions/setup-node@v4
with:
node-version: ${{ matrix.node-version }}
cache: 'npm'
- name: Install dependencies
run: npm ci
- name: Run linter
run: npm run lint
- name: Install required tools
run: |
sudo apt-get update
sudo apt-get install -y netcat curl gnupg2
curl https://packages.microsoft.com/keys/microsoft.asc | sudo tee /etc/apt/trusted.gpg.d/microsoft.asc
curl https://packages.microsoft.com/config/ubuntu/22.04/prod.list | sudo tee /etc/apt/sources.list.d/mssql-release.list
sudo apt-get update
sudo ACCEPT_EULA=Y apt-get install -y mssql-tools18 unixodbc-dev
echo "/opt/mssql-tools18/bin" >> $GITHUB_PATH
- name: Verify SQL Tools Installation
run: |
which sqlcmd
sqlcmd --version
- name: Check SQL Server container
run: |
docker ps
docker logs $(docker ps -q --filter "ancestor=mcr.microsoft.com/mssql/server:2019-CU18-ubuntu-20.04")
- name: Verify network connectivity
run: |
echo "Testing connection to SQL Server..."
for i in {1..5}; do
if nc -zv localhost 1433; then
echo "Port 1433 is open"
break
fi
echo "Attempt $i: Port not ready, waiting..."
sleep 5
done
echo "Testing SQL Server connection from container..."
docker exec $(docker ps -q --filter "ancestor=mcr.microsoft.com/mssql/server:2019-CU18-ubuntu-20.04") \
/opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P 'YourTestPassword123!' \
-Q 'SELECT @@VERSION' || exit 1
- name: Wait for SQL Server
timeout-minutes: 5
run: |
until sqlcmd -S localhost -U sa -P 'YourTestPassword123!' -Q 'SELECT 1' &> /dev/null
do
echo "Waiting for SQL Server to be ready..."
sleep 5
done
echo "SQL Server is ready"
- name: Verify SQL Server
run: |
sqlcmd -S localhost -U sa -P 'YourTestPassword123!' -Q 'SELECT @@VERSION'
- name: Run tests
run: npm test
env:
MSSQL_HOST: localhost
MSSQL_PORT: 1433
MSSQL_USER: sa
MSSQL_PASSWORD: 'YourTestPassword123!'
MSSQL_DATABASE: master
MSSQL_ENCRYPT: false
MSSQL_TRUST_SERVER_CERTIFICATE: true
- name: Upload coverage reports
uses: codecov/codecov-action@v3
with:
token: ${{ secrets.CODECOV_TOKEN }}
files: ./coverage/lcov.info
flags: unittests
name: codecov-umbrella
fail_ci_if_error: true
build:
runs-on: ubuntu-latest
needs: test
if: github.event_name == 'push' && github.ref == 'refs/heads/main'
steps:
- uses: actions/checkout@v4
- name: Use Node.js
uses: actions/setup-node@v4
with:
node-version: '20.x'
cache: 'npm'
- name: Install dependencies
run: npm ci
- name: Build
run: npm run build
- name: Upload build artifacts
uses: actions/upload-artifact@v3
with:
name: build
path: build/
retention-days: 7
release:
runs-on: ubuntu-latest
needs: build
if: github.event_name == 'push' && github.ref == 'refs/heads/main'
permissions:
contents: write
steps:
- uses: actions/checkout@v4
with:
fetch-depth: 0
- name: Download build artifacts
uses: actions/download-artifact@v3
with:
name: build
path: build/
- name: Setup Node.js
uses: actions/setup-node@v4
with:
node-version: '20.x'
registry-url: 'https://registry.npmjs.org'
- name: Install dependencies
run: npm ci
- name: Create Release
env:
GITHUB_TOKEN: ${{ secrets.GITHUB_TOKEN }}
run: |
VERSION=$(node -p "require('./package.json').version")
gh release create v$VERSION \
--title "v$VERSION" \
--notes "See [CHANGELOG.md](CHANGELOG.md) for details." \
--draft
```
--------------------------------------------------------------------------------
/docs/api/configuration.md:
--------------------------------------------------------------------------------
```markdown
# Configuration Guide
This document details the configuration options available for the MSSQL MCP Server.
## Environment Variables
The server uses environment variables for configuration. These are set in the MCP settings configuration file.
### Required Variables
#### Connection Settings
```typescript
MSSQL_HOST="your-server-host"
// SQL Server hostname or IP address
// Example: "localhost" or "database.example.com"
MSSQL_PORT="1433"
// SQL Server port number
// Default: 1433
MSSQL_USER="your-username"
// SQL Server authentication username
// Example: "sa" or "app_user"
MSSQL_PASSWORD="your-password"
// SQL Server authentication password
```
### Optional Variables
#### Database Settings
```typescript
MSSQL_DATABASE="default-database"
// Default database to connect to
// If not specified, must be provided in each query
// Example: "master" or "application_db"
MSSQL_SCHEMA="default-schema"
// Default schema to use
// Default: "dbo"
```
#### Security Settings
```typescript
MSSQL_ENCRYPT="true"
// Enable/disable connection encryption
// Default: true
// Values: "true" or "false"
MSSQL_TRUST_SERVER_CERTIFICATE="false"
// Trust self-signed certificates
// Default: false
// Values: "true" or "false"
MSSQL_ENABLE_ARITH_ABORT="true"
// Set ARITHABORT property
// Default: true
// Values: "true" or "false"
```
#### Connection Pool Settings
```typescript
MSSQL_POOL_MAX="10"
// Maximum number of connections in pool
// Default: 10
// Range: 1-1000
MSSQL_POOL_MIN="0"
// Minimum number of connections in pool
// Default: 0
// Range: 0-1000
MSSQL_POOL_IDLE_TIMEOUT="30000"
// Time (ms) before idle connections are closed
// Default: 30000 (30 seconds)
// Range: 1000-3600000
MSSQL_POOL_ACQUIRE_TIMEOUT="15000"
// Time (ms) to wait for connection from pool
// Default: 15000 (15 seconds)
// Range: 1000-60000
```
#### Query Settings
```typescript
MSSQL_QUERY_TIMEOUT="30000"
// Default query timeout in milliseconds
// Default: 30000 (30 seconds)
// Range: 1000-3600000
MSSQL_MULTIPLE_STATEMENTS="false"
// Allow multiple statements in single query
// Default: false
// Values: "true" or "false"
MSSQL_ROWS_AS_ARRAY="false"
// Return rows as arrays instead of objects
// Default: false
// Values: "true" or "false"
```
#### Debug Settings
```typescript
MSSQL_DEBUG="false"
// Enable debug logging
// Default: false
// Values: "true" or "false"
MSSQL_DEBUG_SQL="false"
// Log SQL queries
// Default: false
// Values: "true" or "false"
```
## MCP Settings Configuration
Example configuration in MCP settings file:
```json
{
"mcpServers": {
"mssql": {
"command": "node",
"args": ["/path/to/mssql-mcp-server/build/index.js"],
"env": {
"MSSQL_HOST": "localhost",
"MSSQL_PORT": "1433",
"MSSQL_USER": "sa",
"MSSQL_PASSWORD": "YourStrongPassword123",
"MSSQL_DATABASE": "master",
"MSSQL_ENCRYPT": "true",
"MSSQL_TRUST_SERVER_CERTIFICATE": "false",
"MSSQL_POOL_MAX": "10",
"MSSQL_POOL_MIN": "0",
"MSSQL_POOL_IDLE_TIMEOUT": "30000",
"MSSQL_QUERY_TIMEOUT": "30000",
"MSSQL_DEBUG": "false"
}
}
}
}
```
## Connection String Format
The server internally constructs a connection string using the provided environment variables. The format is:
```
Server=#{MSSQL_HOST},#{MSSQL_PORT};Database=#{MSSQL_DATABASE};User Id=#{MSSQL_USER};Password=#{MSSQL_PASSWORD};Encrypt=#{MSSQL_ENCRYPT};TrustServerCertificate=#{MSSQL_TRUST_SERVER_CERTIFICATE};
```
## Configuration Best Practices
### Security
1. **Encryption**
- Always enable encryption in production
- Use trusted certificates
- Avoid trusting self-signed certificates
2. **Authentication**
- Use strong passwords
- Consider using integrated security
- Rotate credentials regularly
3. **Network**
- Use firewalls to restrict access
- Configure proper port settings
- Enable TLS/SSL
### Performance
1. **Connection Pool**
- Set appropriate pool size
- Configure idle timeout
- Monitor pool usage
2. **Query Settings**
- Set reasonable timeouts
- Enable multiple statements only if needed
- Monitor query performance
3. **Resource Management**
- Configure minimum connections
- Set maximum connections
- Monitor resource usage
### Development
1. **Debug Settings**
- Enable debug logging in development
- Log SQL queries during testing
- Disable in production
2. **Error Handling**
- Configure proper timeouts
- Enable detailed errors in development
- Use production-safe error messages
## Environment-Specific Configurations
### Development
```json
{
"MSSQL_HOST": "localhost",
"MSSQL_TRUST_SERVER_CERTIFICATE": "true",
"MSSQL_DEBUG": "true",
"MSSQL_DEBUG_SQL": "true",
"MSSQL_POOL_MAX": "5"
}
```
### Testing
```json
{
"MSSQL_HOST": "test-db",
"MSSQL_POOL_MAX": "5",
"MSSQL_DEBUG": "true",
"MSSQL_QUERY_TIMEOUT": "5000"
}
```
### Production
```json
{
"MSSQL_HOST": "prod-db",
"MSSQL_ENCRYPT": "true",
"MSSQL_TRUST_SERVER_CERTIFICATE": "false",
"MSSQL_DEBUG": "false",
"MSSQL_POOL_MAX": "20"
}
```
## Troubleshooting
### Common Issues
1. **Connection Failures**
- Verify host and port
- Check credentials
- Confirm firewall settings
- Verify SSL/TLS configuration
2. **Pool Issues**
- Check pool size settings
- Monitor connection usage
- Verify timeout settings
- Check for connection leaks
3. **Performance Problems**
- Review pool configuration
- Check query timeouts
- Monitor resource usage
- Optimize connection settings
### Configuration Validation
The server validates configuration on startup:
- Checks required variables
- Validates value ranges
- Verifies format of values
- Tests database connection
```
--------------------------------------------------------------------------------
/docs/api/tools.md:
--------------------------------------------------------------------------------
```markdown
# MSSQL MCP Server Tools Documentation
This document provides detailed information about each tool available in the MSSQL MCP Server.
## Tool: query
Execute SQL queries against the database with support for parameters.
### Input Schema
```typescript
{
// The SQL query to execute
query: string;
// Optional parameters for the query
params?: {
[key: string]: string | number | boolean | Date | Buffer | null;
};
// Optional database name (overrides default)
database?: string;
// Optional timeout in milliseconds
timeout?: number;
}
```
### Response Schema
```typescript
{
// Array of result sets (for multiple statements)
resultSets: Array<{
// Array of records
records: Array<Record<string, any>>;
// Number of rows affected (for INSERT/UPDATE/DELETE)
rowsAffected: number;
// Metadata about the columns
columns: Array<{
name: string;
type: string;
nullable: boolean;
}>;
}>;
}
```
### Error Codes
- `INVALID_QUERY`: Invalid SQL syntax
- `PARAMETER_MISMATCH`: Missing or invalid parameters
- `DATABASE_NOT_FOUND`: Specified database doesn't exist
- `PERMISSION_DENIED`: Insufficient permissions
- `TIMEOUT`: Query execution timeout
- `CONNECTION_ERROR`: Database connection issues
### Examples
1. Basic SELECT query:
```json
{
"query": "SELECT * FROM Users WHERE Active = 1"
}
```
2. Parameterized query:
```json
{
"query": "INSERT INTO Users (Name, Email, Age) VALUES (@name, @email, @age)",
"params": {
"name": "John Doe",
"email": "[email protected]",
"age": 30
}
}
```
3. Query with different database:
```json
{
"query": "SELECT * FROM Products",
"database": "Inventory"
}
```
## Tool: list_databases
List all available databases with optional filtering.
### Input Schema
```typescript
{
// Optional filter pattern (SQL LIKE syntax)
filter?: string;
}
```
### Response Schema
```typescript
{
databases: Array<{
name: string;
size: number;
owner: string;
created: string;
state: string;
}>;
}
```
### Error Codes
- `PERMISSION_DENIED`: Insufficient permissions
- `INVALID_FILTER`: Invalid filter pattern
- `CONNECTION_ERROR`: Database connection issues
### Examples
1. List all databases:
```json
{}
```
2. Filter databases by pattern:
```json
{
"filter": "Test%"
}
```
## Tool: list_tables
List all tables in a specified database.
### Input Schema
```typescript
{
// Database name
database: string;
// Optional schema name (defaults to 'dbo')
schema?: string;
// Optional filter pattern (SQL LIKE syntax)
filter?: string;
}
```
### Response Schema
```typescript
{
tables: Array<{
name: string;
schema: string;
type: string;
rowCount: number;
created: string;
modified: string;
}>;
}
```
### Error Codes
- `DATABASE_NOT_FOUND`: Specified database doesn't exist
- `SCHEMA_NOT_FOUND`: Specified schema doesn't exist
- `PERMISSION_DENIED`: Insufficient permissions
- `INVALID_FILTER`: Invalid filter pattern
- `CONNECTION_ERROR`: Database connection issues
### Examples
1. List all tables in default schema:
```json
{
"database": "Northwind"
}
```
2. List tables in specific schema with filter:
```json
{
"database": "Northwind",
"schema": "sales",
"filter": "Order%"
}
```
## Tool: describe_table
Get detailed schema information about a specific table.
### Input Schema
```typescript
{
// Database name
database: string;
// Schema name (defaults to 'dbo')
schema?: string;
// Table name
table: string;
}
```
### Response Schema
```typescript
{
table: {
name: string;
schema: string;
columns: Array<{
name: string;
type: string;
nullable: boolean;
default: string | null;
isPrimary: boolean;
isIdentity: boolean;
length: number | null;
precision: number | null;
scale: number | null;
}>;
indexes: Array<{
name: string;
type: string;
columns: string[];
isUnique: boolean;
isClustered: boolean;
}>;
foreignKeys: Array<{
name: string;
columns: string[];
referencedTable: string;
referencedSchema: string;
referencedColumns: string[];
onUpdate: string;
onDelete: string;
}>;
triggers: Array<{
name: string;
type: string;
definition: string;
}>;
};
}
```
### Error Codes
- `DATABASE_NOT_FOUND`: Specified database doesn't exist
- `SCHEMA_NOT_FOUND`: Specified schema doesn't exist
- `TABLE_NOT_FOUND`: Specified table doesn't exist
- `PERMISSION_DENIED`: Insufficient permissions
- `CONNECTION_ERROR`: Database connection issues
### Examples
1. Describe table in default schema:
```json
{
"database": "Northwind",
"table": "Customers"
}
```
2. Describe table in specific schema:
```json
{
"database": "Northwind",
"schema": "sales",
"table": "Orders"
}
```
## Best Practices
1. **Query Tool**
- Use parameters instead of string concatenation
- Set appropriate timeouts for long-running queries
- Handle multiple result sets when needed
- Use transactions for data modifications
2. **List Operations**
- Use filters to reduce result sets
- Handle pagination for large results
- Consider permissions when listing objects
3. **Schema Information**
- Cache schema information when appropriate
- Check for schema changes
- Handle large object definitions
4. **Error Handling**
- Always check for specific error codes
- Handle connection issues gracefully
- Provide meaningful error messages
- Implement proper logging
5. **Security**
- Validate all input parameters
- Use minimum required permissions
- Implement proper access control
- Handle sensitive data appropriately
```
--------------------------------------------------------------------------------
/docs/api/error-handling.md:
--------------------------------------------------------------------------------
```markdown
# Error Handling Documentation
This document details the error handling system in the MSSQL MCP Server, including error categories, codes, and troubleshooting guidelines.
## Error Response Format
All errors follow a consistent format:
```typescript
{
code: string; // Unique error identifier
message: string; // Human-readable error description
details?: any; // Additional error context
cause?: string; // Original error that caused this error
suggestions?: string[]; // Recommended solutions
}
```
## Error Categories
### 1. Connection Errors (CONNECTION_*)
#### CONNECTION_ERROR
- **Description**: General connection failure
- **Possible Causes**:
- Network connectivity issues
- Invalid credentials
- Server unavailable
- **Solutions**:
- Verify network connectivity
- Check credentials
- Confirm server status
- Check firewall settings
#### CONNECTION_TIMEOUT
- **Description**: Connection attempt timed out
- **Possible Causes**:
- Network latency
- Server overload
- Firewall blocking
- **Solutions**:
- Increase timeout settings
- Check network performance
- Verify firewall rules
#### CONNECTION_CLOSED
- **Description**: Connection unexpectedly closed
- **Possible Causes**:
- Server restart
- Network interruption
- Connection pool timeout
- **Solutions**:
- Implement retry logic
- Check server logs
- Adjust pool settings
### 2. Query Errors (QUERY_*)
#### INVALID_QUERY
- **Description**: SQL syntax error
- **Possible Causes**:
- Syntax mistakes
- Invalid table/column names
- Unsupported SQL features
- **Solutions**:
- Verify SQL syntax
- Check object names
- Review SQL Server version compatibility
#### PARAMETER_MISMATCH
- **Description**: Parameter validation failure
- **Possible Causes**:
- Missing parameters
- Invalid parameter types
- Parameter name mismatch
- **Solutions**:
- Check parameter names
- Verify parameter types
- Ensure all required parameters are provided
#### QUERY_TIMEOUT
- **Description**: Query execution timeout
- **Possible Causes**:
- Complex query
- Server load
- Missing indexes
- **Solutions**:
- Optimize query
- Add appropriate indexes
- Increase timeout setting
- Consider query pagination
### 3. Permission Errors (PERMISSION_*)
#### PERMISSION_DENIED
- **Description**: Insufficient privileges
- **Possible Causes**:
- Missing user permissions
- Object-level restrictions
- Server-level restrictions
- **Solutions**:
- Review user permissions
- Check object permissions
- Request necessary access
#### LOGIN_FAILED
- **Description**: Authentication failure
- **Possible Causes**:
- Invalid credentials
- Account locked
- Password expired
- **Solutions**:
- Verify credentials
- Check account status
- Update password if needed
### 4. Resource Errors (RESOURCE_*)
#### DATABASE_NOT_FOUND
- **Description**: Database does not exist
- **Possible Causes**:
- Database name typo
- Database not created
- Database deleted
- **Solutions**:
- Verify database name
- Check database existence
- Create database if needed
#### TABLE_NOT_FOUND
- **Description**: Table does not exist
- **Possible Causes**:
- Table name typo
- Wrong schema
- Table deleted
- **Solutions**:
- Verify table name
- Check schema name
- Confirm table existence
#### SCHEMA_NOT_FOUND
- **Description**: Schema does not exist
- **Possible Causes**:
- Schema name typo
- Schema not created
- Schema deleted
- **Solutions**:
- Verify schema name
- Check schema existence
- Create schema if needed
### 5. Validation Errors (VALIDATION_*)
#### INVALID_INPUT
- **Description**: Input validation failure
- **Possible Causes**:
- Invalid data types
- Value out of range
- Format mismatch
- **Solutions**:
- Check input types
- Verify value ranges
- Format data correctly
#### CONSTRAINT_VIOLATION
- **Description**: Database constraint violation
- **Possible Causes**:
- Unique constraint violation
- Foreign key constraint violation
- Check constraint violation
- **Solutions**:
- Check unique constraints
- Verify foreign key relationships
- Validate check constraints
## Error Handling Best Practices
1. **Logging**
- Log all errors with context
- Include timestamp and correlation ID
- Maintain different log levels
- Implement log rotation
2. **Recovery**
- Implement retry logic for transient errors
- Use exponential backoff
- Set maximum retry attempts
- Handle cleanup after errors
3. **User Communication**
- Provide clear error messages
- Include actionable solutions
- Hide sensitive information
- Use appropriate error codes
4. **Prevention**
- Validate inputs early
- Check permissions proactively
- Monitor resource usage
- Implement timeouts
## Troubleshooting Guide
1. **Connection Issues**
- Verify network connectivity
- Check firewall settings
- Confirm server status
- Review connection string
- Check SSL/TLS settings
2. **Query Problems**
- Review query syntax
- Check execution plan
- Verify parameter types
- Monitor query performance
- Check for deadlocks
3. **Permission Problems**
- Review user permissions
- Check object permissions
- Verify login status
- Review security policies
- Check for permission inheritance
4. **Resource Issues**
- Monitor server resources
- Check connection pool
- Review memory usage
- Monitor disk space
- Check CPU utilization
## Monitoring and Alerting
1. **Key Metrics**
- Error rate
- Connection failures
- Query timeouts
- Permission denials
- Resource exhaustion
2. **Alert Thresholds**
- Error rate spike
- Connection pool exhaustion
- High query timeout rate
- Repeated permission failures
- Resource usage limits
3. **Response Plans**
- Error investigation
- Resource scaling
- Permission updates
- Query optimization
- Infrastructure updates
```
--------------------------------------------------------------------------------
/docs/examples/advanced-usage.md:
--------------------------------------------------------------------------------
```markdown
# Advanced Usage Examples
This document demonstrates advanced usage patterns and complex scenarios for the MSSQL MCP Server.
## Complex Transactions
### Multi-Table Transaction with Error Handling
```json
{
"tool": "query",
"arguments": {
"query": `
BEGIN TRY
BEGIN TRANSACTION;
-- Create order
INSERT INTO Orders (CustomerId, OrderDate, Total)
VALUES (@customerId, GETDATE(), @total);
DECLARE @orderId INT = SCOPE_IDENTITY();
-- Insert order items
INSERT INTO OrderItems (OrderId, ProductId, Quantity, Price)
SELECT
@orderId,
ProductId,
Quantity,
CurrentPrice
FROM @orderItems;
-- Update inventory
UPDATE Inventory
SET StockQuantity = StockQuantity - i.Quantity
FROM Inventory inv
INNER JOIN @orderItems i ON inv.ProductId = i.ProductId;
-- Update customer stats
UPDATE CustomerStats
SET
TotalOrders = TotalOrders + 1,
LastOrderDate = GETDATE()
WHERE CustomerId = @customerId;
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;
THROW;
END CATCH
`,
"params": {
"customerId": 1001,
"total": 525.75,
"orderItems": [
{ "ProductId": 1, "Quantity": 2, "CurrentPrice": 99.99 },
{ "ProductId": 2, "Quantity": 1, "CurrentPrice": 325.77 }
]
}
}
}
```
## Dynamic SQL Generation
### Dynamic Column Selection
```json
{
"tool": "query",
"arguments": {
"query": `
DECLARE @columns NVARCHAR(MAX);
DECLARE @sql NVARCHAR(MAX);
SELECT @columns = STRING_AGG(QUOTENAME(column_name), ',')
FROM INFORMATION_SCHEMA.COLUMNS
WHERE
TABLE_SCHEMA = @schema
AND TABLE_NAME = @table
AND column_name IN (SELECT value FROM STRING_SPLIT(@selectedColumns, ','));
SET @sql = N'SELECT ' + @columns + ' FROM ' + QUOTENAME(@schema) + '.' + QUOTENAME(@table) +
' WHERE ' + @whereClause;
EXEC sp_executesql @sql,
N'@param1 int',
@param1 = @value;
`,
"params": {
"schema": "dbo",
"table": "Products",
"selectedColumns": "ProductId,Name,Price,Category",
"whereClause": "CategoryId = @param1",
"value": 5
}
}
}
```
## Stored Procedure Integration
### Execute Stored Procedure
```json
{
"tool": "query",
"arguments": {
"query": `
DECLARE @return_value int;
EXEC @return_value = [dbo].[GenerateReport]
@startDate = @start,
@endDate = @end,
@format = @reportFormat,
@userId = @user;
SELECT @return_value as ReturnValue;
`,
"params": {
"start": "2024-01-01",
"end": "2024-12-31",
"reportFormat": "PDF",
"user": 1001
}
}
}
```
## Batch Operations
### Bulk Insert with Table-Valued Parameter
```json
{
"tool": "query",
"arguments": {
"query": `
DECLARE @ProductData ProductTableType;
INSERT INTO @ProductData (Name, Price, Category)
SELECT Name, Price, Category
FROM OPENJSON(@products)
WITH (
Name nvarchar(100),
Price decimal(18,2),
Category nvarchar(50)
);
INSERT INTO Products (Name, Price, Category)
SELECT Name, Price, Category
FROM @ProductData;
`,
"params": {
"products": JSON.stringify([
{ "Name": "Product 1", "Price": 99.99, "Category": "Electronics" },
{ "Name": "Product 2", "Price": 149.99, "Category": "Electronics" }
])
}
}
}
```
## Advanced Querying
### Hierarchical Data Query
```json
{
"tool": "query",
"arguments": {
"query": `
WITH CategoryHierarchy AS (
SELECT
CategoryId,
Name,
ParentCategoryId,
0 as Level,
CAST(Name as nvarchar(255)) as Path
FROM Categories
WHERE ParentCategoryId IS NULL
UNION ALL
SELECT
c.CategoryId,
c.Name,
c.ParentCategoryId,
ch.Level + 1,
CAST(ch.Path + ' > ' + c.Name as nvarchar(255))
FROM Categories c
INNER JOIN CategoryHierarchy ch ON c.ParentCategoryId = ch.CategoryId
)
SELECT * FROM CategoryHierarchy
ORDER BY Path;
`
}
}
```
### Full-Text Search
```json
{
"tool": "query",
"arguments": {
"query": `
SELECT
p.ProductId,
p.Name,
p.Description,
KEY_TBL.RANK as SearchRank
FROM Products p
INNER JOIN CONTAINSTABLE(Products, (Name, Description), @searchTerm) AS KEY_TBL
ON p.ProductId = KEY_TBL.[KEY]
ORDER BY KEY_TBL.RANK DESC;
`,
"params": {
"searchTerm": "wireless AND (headphone OR earbuds)"
}
}
}
```
## Performance Monitoring
### Query Performance Analysis
```json
{
"tool": "query",
"arguments": {
"query": `
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
SELECT
c.CustomerId,
c.Name,
COUNT(o.OrderId) as OrderCount,
SUM(o.Total) as TotalSpent
FROM Customers c
LEFT JOIN Orders o ON c.CustomerId = o.CustomerId
WHERE o.OrderDate >= @since
GROUP BY c.CustomerId, c.Name
HAVING COUNT(o.OrderId) > @minOrders;
SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;
`,
"params": {
"since": "2024-01-01",
"minOrders": 5
}
}
}
```
## Security Implementations
### Row-Level Security
```json
{
"tool": "query",
"arguments": {
"query": `
-- Create security policy
CREATE SECURITY POLICY CustomerFilter
ADD FILTER PREDICATE dbo.fn_SecurityPredicate(@UserId)
ON dbo.CustomerData;
-- Apply policy
ALTER SECURITY POLICY CustomerFilter
WITH (STATE = ON);
-- Query with security context
EXECUTE AS USER = @userName;
SELECT * FROM CustomerData;
REVERT;
`,
"params": {
"userName": "app_user"
}
}
}
```
## Data Integration
### ETL Process
```json
{
"tool": "query",
"arguments": {
"query": `
-- Stage data
INSERT INTO StagingCustomers (ExternalId, Data)
SELECT ExternalId, Data
FROM OPENROWSET(
BULK 'customer_data.json',
SINGLE_CLOB
) as JsonData;
-- Transform
WITH ParsedData AS (
SELECT
ExternalId,
JSON_VALUE(Data, '$.name') as Name,
JSON_VALUE(Data, '$.email') as Email,
JSON_VALUE(Data, '$.address') as Address
FROM StagingCustomers
)
-- Load
MERGE INTO Customers as target
USING ParsedData as source
ON target.ExternalId = source.ExternalId
WHEN MATCHED THEN
UPDATE SET
Name = source.Name,
Email = source.Email,
Address = source.Address
WHEN NOT MATCHED THEN
INSERT (ExternalId, Name, Email, Address)
VALUES (source.ExternalId, source.Name, source.Email, source.Address);
`
}
}
```
## Best Practices
1. **Transaction Management**
- Use explicit transactions
- Implement proper error handling
- Consider isolation levels
- Keep transactions short
2. **Performance Optimization**
- Use appropriate indexes
- Monitor query plans
- Implement caching strategies
- Use batch operations
3. **Security**
- Implement row-level security
- Use parameterized queries
- Validate all inputs
- Audit sensitive operations
4. **Error Handling**
- Implement comprehensive error handling
- Log errors appropriately
- Provide meaningful error messages
- Handle cleanup in error cases
```