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