# Directory Structure
```
├── .gitignore
├── env.example
├── install.sh
├── README.md
├── requirements.txt
├── setup.py
├── src
│ ├── __init__.py
│ └── server.py
├── start.sh
├── status.sh
├── stop.sh
└── tests
├── __init__.py
└── test_server.py
```
# Files
--------------------------------------------------------------------------------
/.gitignore:
--------------------------------------------------------------------------------
```
1 | # Byte-compiled / optimized / DLL files
2 | __pycache__/
3 | *.py[cod]
4 | *$py.class
5 |
6 | # C extensions
7 | *.so
8 |
9 | # Distribution / packaging
10 | .Python
11 | build/
12 | develop-eggs/
13 | dist/
14 | downloads/
15 | eggs/
16 | .eggs/
17 | lib/
18 | lib64/
19 | parts/
20 | sdist/
21 | var/
22 | wheels/
23 | pip-wheel-metadata/
24 | share/python-wheels/
25 | *.egg-info/
26 | .installed.cfg
27 | *.egg
28 | MANIFEST
29 |
30 | # PyInstaller
31 | # Usually these files are written by a python script from a template
32 | # before PyInstaller builds the exe, so as to inject date/other infos into it.
33 | *.manifest
34 | *.spec
35 |
36 | # Installer logs
37 | pip-log.txt
38 | pip-delete-this-directory.txt
39 |
40 | # Unit test / coverage reports
41 | htmlcov/
42 | .tox/
43 | .nox/
44 | .coverage
45 | .coverage.*
46 | .cache
47 | nosetests.xml
48 | coverage.xml
49 | *.cover
50 | *.py,cover
51 | .hypothesis/
52 | .pytest_cache/
53 |
54 | # Translations
55 | *.mo
56 | *.pot
57 |
58 | # Django stuff:
59 | *.log
60 | local_settings.py
61 | db.sqlite3
62 | db.sqlite3-journal
63 |
64 | # Flask stuff:
65 | instance/
66 | .webassets-cache
67 |
68 | # Scrapy stuff:
69 | .scrapy
70 |
71 | # Sphinx documentation
72 | docs/_build/
73 |
74 | # PyBuilder
75 | target/
76 |
77 | # Jupyter Notebook
78 | .ipynb_checkpoints
79 |
80 | # IPython
81 | profile_default/
82 | ipython_config.py
83 |
84 | # pyenv
85 | .python-version
86 |
87 | # pipenv
88 | # According to pypa/pipenv#598, it is recommended to include Pipfile.lock in version control.
89 | # However, in case of collaboration, if having platform-specific dependencies or dependencies
90 | # having no cross-platform support, pipenv may install dependencies that don't work, or not
91 | # install all needed dependencies.
92 | #Pipfile.lock
93 |
94 | # PEP 582; used by e.g. github.com/David-OConnor/pyflow
95 | __pypackages__/
96 |
97 | # Celery stuff
98 | celerybeat-schedule
99 | celerybeat.pid
100 |
101 | # SageMath parsed files
102 | *.sage.py
103 |
104 | # Environments
105 | .env
106 | .venv
107 | env/
108 | venv/
109 | ENV/
110 | env.bak/
111 | venv.bak/
112 |
113 | # Spyder project settings
114 | .spyderproject
115 | .spyproject
116 |
117 | # Rope project settings
118 | .ropeproject
119 |
120 | # mkdocs documentation
121 | /site
122 |
123 | # mypy
124 | .mypy_cache/
125 | .dmypy.json
126 | dmypy.json
127 |
128 | # Pyre type checker
129 | .pyre/
130 |
131 | # IDE
132 | .vscode/
133 | .idea/
134 | *.swp
135 | *.swo
136 |
137 | # OS
138 | .DS_Store
139 | Thumbs.db
140 |
141 | # Project specific
142 | config.json
143 |
144 | # Runtime files
145 | server.pid
146 | server.log
147 | nohup.out
```
--------------------------------------------------------------------------------
/README.md:
--------------------------------------------------------------------------------
```markdown
1 | # MSSQL MCP Server
2 |
3 | A Model Context Protocol (MCP) server that provides comprehensive access to Microsoft SQL Server databases. This enhanced server enables Language Models to inspect database schemas, execute queries, manage database objects, and perform advanced database operations through a standardized interface.
4 |
5 | ## 🚀 Enhanced Features
6 |
7 | ### **Complete Database Schema Traversal**
8 | - **23 comprehensive database management tools** (expanded from 5 basic operations)
9 | - **Full database object hierarchy exploration** - tables, views, stored procedures, indexes, schemas
10 | - **Advanced database object management** - create, modify, delete operations
11 | - **Intelligent resource access** - all tables and views available as MCP resources
12 | - **Large content handling** - retrieves complete stored procedures (1400+ lines) without truncation
13 |
14 | ### **Core Capabilities**
15 | - **Database Connection**: Connect to MSSQL Server instances with flexible authentication
16 | - **Schema Inspection**: Complete database object exploration and management
17 | - **Query Execution**: Execute SELECT, INSERT, UPDATE, DELETE, and DDL queries
18 | - **Stored Procedure Management**: Create, modify, execute, and manage stored procedures
19 | - **View Management**: Create, modify, delete, and describe views
20 | - **Index Management**: Create, delete, and analyze indexes
21 | - **Resource Access**: Browse table and view data as MCP resources
22 |
23 | - **Security**: Read-only and write operations are properly separated and validated
24 |
25 | ## ⚠️ Important Usage Guidelines for Engineering Teams
26 |
27 | ### **Database Limitation**
28 | **🔴 CRITICAL: Limit to ONE database per MCP server instance**
29 |
30 | - This enhanced MCP server creates **23 tools per database**
31 | - Cursor has a **40-tool limit** across all MCP servers
32 | - Using multiple database instances will exceed Cursor's tool limit
33 | - For multiple databases, use separate MCP server instances in different projects
34 |
35 | ### **Large Content Limitations**
36 | **⚠️ IMPORTANT: File operations not supported within chat context**
37 |
38 | - Large stored procedures (1400+ lines) can be retrieved and viewed in chat
39 | - However, saving large content to files via MCP tools is not reliable due to token limits
40 | - **For bulk data extraction**: Use standalone Python scripts with direct database connections
41 | - **Recommended approach**: Copy-paste smaller procedures from chat, use external scripts for large ones
42 |
43 | ### **Tool Distribution**
44 | - **Core Tools**: 5 (read_query, write_query, list_tables, describe_table, create_table)
45 | - **Stored Procedures**: 6 tools (create, modify, delete, list, describe, execute, get_parameters)
46 | - **Views**: 5 tools (create, modify, delete, list, describe)
47 | - **Indexes**: 4 tools (create, delete, list, describe)
48 | - **Schema Management**: 2 tools (list_schemas, list_all_objects)
49 | - **Total**: 23 tools + enhanced write_query supporting all database object operations
50 |
51 | ## Installation
52 |
53 | ### Prerequisites
54 |
55 | - Python 3.10 or higher
56 | - ODBC Driver 17 for SQL Server
57 | - Access to an MSSQL Server instance
58 |
59 | ### Quick Setup
60 |
61 | 1. **Clone or create the project directory:**
62 | ```bash
63 | mkdir mcp-sqlserver && cd mcp-sqlserver
64 | ```
65 |
66 | 2. **Run the installation script:**
67 | ```bash
68 | chmod +x install.sh
69 | ./install.sh
70 | ```
71 |
72 | 3. **Configure your database connection:**
73 | ```bash
74 | cp env.example .env
75 | # Edit .env with your database details
76 | ```
77 |
78 | ### Manual Installation
79 |
80 | 1. **Create virtual environment:**
81 | ```bash
82 | python3 -m venv venv
83 | source venv/bin/activate
84 | ```
85 |
86 | 2. **Install dependencies:**
87 | ```bash
88 | pip install -r requirements.txt
89 | ```
90 |
91 | 3. **Install ODBC Driver (macOS):**
92 | ```bash
93 | brew tap microsoft/mssql-release
94 | brew install msodbcsql17 mssql-tools
95 | ```
96 |
97 | ## Configuration
98 |
99 | Create a `.env` file with your database configuration:
100 |
101 | ```env
102 | MSSQL_DRIVER={ODBC Driver 17 for SQL Server}
103 | MSSQL_SERVER=your-server-address
104 | MSSQL_DATABASE=your-database-name
105 | MSSQL_USER=your-username
106 | MSSQL_PASSWORD=your-password
107 | MSSQL_PORT=1433
108 | TrustServerCertificate=yes
109 | ```
110 |
111 | ### Configuration Options
112 |
113 | - `MSSQL_SERVER`: Server hostname or IP address (required)
114 | - `MSSQL_DATABASE`: Database name to connect to (required)
115 | - `MSSQL_USER`: Username for authentication
116 | - `MSSQL_PASSWORD`: Password for authentication
117 | - `MSSQL_PORT`: Port number (default: 1433)
118 | - `MSSQL_DRIVER`: ODBC driver name (default: {ODBC Driver 17 for SQL Server})
119 | - `TrustServerCertificate`: Trust server certificate (default: yes)
120 | - `Trusted_Connection`: Use Windows authentication (default: no)
121 |
122 | ## Usage
123 |
124 | ### Understanding MCP Servers
125 |
126 | MCP (Model Context Protocol) servers are designed to work with AI assistants and language models. They communicate via stdin/stdout using JSON-RPC protocol, not as traditional web services.
127 |
128 | ### Running the Server
129 |
130 | **For AI Assistant Integration:**
131 | ```bash
132 | python3 src/server.py
133 | ```
134 |
135 | The server will start and wait for MCP protocol messages on stdin. This is how AI assistants like Claude Desktop or other MCP clients will communicate with it.
136 |
137 | **For Testing and Development:**
138 |
139 | 1. **Test database connection:**
140 | ```bash
141 | python3 test_connection.py
142 | ```
143 |
144 | 2. **Check server status:**
145 | ```bash
146 | ./status.sh
147 | ```
148 |
149 | 3. **View available tables:**
150 | ```bash
151 | # The server provides tools that can be called by MCP clients
152 | # Direct testing requires an MCP client or testing framework
153 | ```
154 |
155 | ## Available Tools (23 Total)
156 |
157 | The enhanced server provides comprehensive database management tools:
158 |
159 | ### **Core Database Operations (5 tools)**
160 | 1. **`read_query`** - Execute SELECT queries to read data
161 | 2. **`write_query`** - Execute INSERT, UPDATE, DELETE, and DDL queries
162 | 3. **`list_tables`** - List all tables in the database
163 | 4. **`describe_table`** - Get schema information for a specific table
164 | 5. **`create_table`** - Create new tables
165 |
166 | ### **Stored Procedure Management (6 tools)**
167 | 6. **`create_procedure`** - Create new stored procedures
168 | 7. **`modify_procedure`** - Modify existing stored procedures
169 | 8. **`delete_procedure`** - Delete stored procedures
170 | 9. **`list_procedures`** - List all stored procedures with metadata
171 | 10. **`describe_procedure`** - Get complete procedure definitions
172 | 11. **`execute_procedure`** - Execute procedures with parameters
173 | 12. **`get_procedure_parameters`** - Get detailed parameter information
174 |
175 | ### **View Management (5 tools)**
176 | 13. **`create_view`** - Create new views
177 | 14. **`modify_view`** - Modify existing views
178 | 15. **`delete_view`** - Delete views
179 | 16. **`list_views`** - List all views in the database
180 | 17. **`describe_view`** - Get view definitions and schema
181 |
182 | ### **Index Management (4 tools)**
183 | 18. **`create_index`** - Create new indexes
184 | 19. **`delete_index`** - Delete indexes
185 | 20. **`list_indexes`** - List all indexes (optionally by table)
186 | 21. **`describe_index`** - Get detailed index information
187 |
188 | ### **Schema Exploration (2 tools)**
189 | 22. **`list_schemas`** - List all schemas in the database
190 | 23. **`list_all_objects`** - List all database objects organized by schema
191 |
192 | ### **Available Resources**
193 |
194 | Both tables and views are exposed as MCP resources with URIs like:
195 | - `mssql://table_name/data` - Access table data in CSV format
196 | - `mssql://view_name/data` - Access view data in CSV format
197 |
198 | Resources provide the first 100 rows of data in CSV format for quick data exploration.
199 |
200 | ## Database Schema Traversal Examples
201 |
202 | ### **1. Explore Database Structure**
203 | ```
204 | # Start with schemas
205 | list_schemas
206 |
207 | # Get all objects in a specific schema
208 | list_all_objects(schema_name: "dbo")
209 |
210 | # Or get all objects across all schemas
211 | list_all_objects()
212 | ```
213 |
214 | ### **2. Table Exploration**
215 | ```
216 | # List all tables
217 | list_tables
218 |
219 | # Get detailed table information
220 | describe_table(table_name: "YourTableName")
221 |
222 | # Access table data as MCP resource
223 | # URI: mssql://YourTableName/data
224 | ```
225 |
226 | ### **3. View Management**
227 | ```
228 | # List all views
229 | list_views
230 |
231 | # Get view definition
232 | describe_view(view_name: "YourViewName")
233 |
234 | # Create a new view
235 | create_view(view_script: "CREATE VIEW MyView AS SELECT * FROM MyTable WHERE Active = 1")
236 |
237 | # Access view data as MCP resource
238 | # URI: mssql://YourViewName/data
239 | ```
240 |
241 | ### **4. Stored Procedure Operations**
242 | ```
243 | # List all procedures
244 | list_procedures
245 |
246 | # Get complete procedure definition (handles large procedures like wmPostPurchase)
247 | describe_procedure(procedure_name: "YourProcedureName")
248 |
249 | # Save large procedures to file for analysis
250 | write_file(file_path: "procedure_name.sql", content: "procedure_definition")
251 |
252 | # Get parameter details
253 | get_procedure_parameters(procedure_name: "YourProcedureName")
254 |
255 | # Execute procedure
256 | execute_procedure(procedure_name: "YourProcedureName", parameters: ["param1", "param2"])
257 | ```
258 |
259 | ### **5. Index Management**
260 | ```
261 | # List all indexes
262 | list_indexes()
263 |
264 | # List indexes for specific table
265 | list_indexes(table_name: "YourTableName")
266 |
267 | # Get index details
268 | describe_index(index_name: "IX_YourIndex", table_name: "YourTableName")
269 |
270 | # Create new index
271 | create_index(index_script: "CREATE INDEX IX_NewIndex ON MyTable (Column1, Column2)")
272 | ```
273 |
274 | ## Stored Procedure Management Examples
275 |
276 | ### **Create a Simple Stored Procedure**
277 |
278 | ```sql
279 | CREATE PROCEDURE GetEmployeeCount
280 | AS
281 | BEGIN
282 | SELECT COUNT(*) AS TotalEmployees FROM Employees
283 | END
284 | ```
285 |
286 | ### **Create a Stored Procedure with Parameters**
287 |
288 | ```sql
289 | CREATE PROCEDURE GetEmployeesByDepartment
290 | @DepartmentId INT,
291 | @MinSalary DECIMAL(10,2) = 0
292 | AS
293 | BEGIN
294 | SELECT
295 | EmployeeId,
296 | FirstName,
297 | LastName,
298 | Salary,
299 | DepartmentId
300 | FROM Employees
301 | WHERE DepartmentId = @DepartmentId
302 | AND Salary >= @MinSalary
303 | ORDER BY LastName, FirstName
304 | END
305 | ```
306 |
307 | ### **Create a Stored Procedure with Output Parameters**
308 |
309 | ```sql
310 | CREATE PROCEDURE GetDepartmentStats
311 | @DepartmentId INT,
312 | @EmployeeCount INT OUTPUT,
313 | @AverageSalary DECIMAL(10,2) OUTPUT
314 | AS
315 | BEGIN
316 | SELECT
317 | @EmployeeCount = COUNT(*),
318 | @AverageSalary = AVG(Salary)
319 | FROM Employees
320 | WHERE DepartmentId = @DepartmentId
321 | END
322 | ```
323 |
324 | ### **Modify an Existing Stored Procedure**
325 |
326 | ```sql
327 | ALTER PROCEDURE GetEmployeesByDepartment
328 | @DepartmentId INT,
329 | @MinSalary DECIMAL(10,2) = 0,
330 | @MaxSalary DECIMAL(10,2) = 999999.99
331 | AS
332 | BEGIN
333 | SELECT
334 | EmployeeId,
335 | FirstName,
336 | LastName,
337 | Salary,
338 | DepartmentId,
339 | HireDate
340 | FROM Employees
341 | WHERE DepartmentId = @DepartmentId
342 | AND Salary BETWEEN @MinSalary AND @MaxSalary
343 | ORDER BY Salary DESC, LastName, FirstName
344 | END
345 | ```
346 |
347 | ## Large Content Handling
348 |
349 | ### **How It Works**
350 |
351 | The server efficiently handles large database objects like stored procedures:
352 |
353 | 1. **Direct Retrieval**: Fetches complete content directly from SQL Server
354 | 2. **No Truncation**: Returns full procedure definitions regardless of size
355 | 3. **Chat Display**: Large procedures can be viewed in full within the chat interface
356 | 4. **Memory Efficient**: Processes content through database connection streams
357 |
358 | ### **Usage Examples**
359 |
360 | ```
361 | # Describe a large procedure (gets complete definition)
362 | describe_procedure(procedure_name: "wmPostPurchase")
363 |
364 | # Works with procedures of any size (tested with 1400+ line procedures)
365 | # Content is displayed in chat for viewing and copy-paste operations
366 | ```
367 |
368 | ### **Limitations for File Operations**
369 |
370 | **⚠️ Important**: While large procedures can be retrieved and displayed in chat, saving them to files via MCP tools is not reliable due to inference token limits. For bulk data extraction:
371 |
372 | 1. **Small procedures**: Copy-paste from chat interface
373 | 2. **Large procedures**: Use standalone Python scripts with direct database connections
374 | 3. **Bulk operations**: Create dedicated extraction scripts outside the MCP context
375 |
376 | ## Integration with AI Assistants
377 |
378 | ### Claude Desktop
379 |
380 | Add this server to your Claude Desktop configuration:
381 |
382 | ```json
383 | {
384 | "mcpServers": {
385 | "mssql": {
386 | "command": "python3",
387 | "args": ["/path/to/mcp-sqlserver/src/server.py"],
388 | "cwd": "/path/to/mcp-sqlserver",
389 | "env": {
390 | "MSSQL_SERVER": "your-server",
391 | "MSSQL_DATABASE": "your-database",
392 | "MSSQL_USER": "your-username",
393 | "MSSQL_PASSWORD": "your-password"
394 | }
395 | }
396 | }
397 | }
398 | ```
399 |
400 | ### Other MCP Clients
401 |
402 | The server follows the standard MCP protocol and should work with any compliant MCP client.
403 |
404 | ## Development
405 |
406 | ### Project Structure
407 |
408 | ```
409 | mcp-sqlserver/
410 | ├── src/
411 | │ └── server.py # Main MCP server implementation with chunking system
412 | ├── tests/
413 | │ └── test_server.py # Unit tests
414 | ├── requirements.txt # Python dependencies
415 | ├── .env # Database configuration (create from env.example)
416 | ├── env.example # Configuration template
417 | ├── install.sh # Installation script
418 | ├── start.sh # Server startup script (for development)
419 | ├── stop.sh # Server shutdown script
420 | ├── status.sh # Server status script
421 | └── README.md # This file
422 | ```
423 |
424 | ### Testing
425 |
426 | Run the test suite:
427 | ```bash
428 | python -m pytest tests/
429 | ```
430 |
431 | Test database connection:
432 | ```bash
433 | python3 test_connection.py
434 | ```
435 |
436 | ### Logging
437 |
438 | The server uses Python's logging module. Set the log level by modifying the `logging.basicConfig()` call in `src/server.py`.
439 |
440 | ## Security Considerations
441 |
442 | - **Authentication**: Always use strong passwords and secure authentication
443 | - **Network**: Ensure your database server is properly secured
444 | - **Permissions**: Grant only necessary database permissions to the user account
445 | - **SSL/TLS**: Use encrypted connections when possible
446 | - **Query Validation**: The server validates query types and prevents unauthorized operations
447 | - **DDL Operations**: Create/modify/delete operations for database objects are properly validated
448 | - **Stored Procedure Execution**: Parameters are safely handled to prevent injection attacks
449 | - **Large Content Handling**: Large procedures are retrieved efficiently without truncation
450 | - **File Operations**: Write operations are validated and sandboxed
451 | - **Read-First Approach**: Exploration tools are read-only by default for production safety
452 |
453 | ## Troubleshooting
454 |
455 | ### Common Issues
456 |
457 | 1. **Connection Failed**: Check your database server address, credentials, and network connectivity
458 | 2. **ODBC Driver Not Found**: Install Microsoft ODBC Driver 17 for SQL Server
459 | 3. **Permission Denied**: Ensure the database user has appropriate permissions
460 | 4. **Port Issues**: Verify the correct port number and firewall settings
461 | 5. **Large Content Issues**: Large procedures display in chat but cannot be saved to files via MCP tools
462 | 6. **Memory Issues**: Large content is streamed efficiently from the database
463 |
464 | ### Debug Mode
465 |
466 | Enable debug logging by setting the log level to DEBUG in `src/server.py`:
467 |
468 | ```python
469 | logging.basicConfig(level=logging.DEBUG, format='%(asctime)s - %(name)s - %(levelname)s - %(message)s')
470 | ```
471 |
472 | ### Large Content Troubleshooting
473 |
474 | If you encounter issues with large content:
475 |
476 | 1. **Copy-paste approach**: Use chat interface to view and copy large procedures
477 | 2. **External scripts**: Create standalone Python scripts for bulk data extraction
478 | 3. **Check memory**: Large procedures are handled efficiently by the database connection
479 | 4. **Verify permissions**: Ensure database user can access procedure definitions
480 | 5. **Test with smaller procedures**: Verify basic functionality first
481 |
482 | ### Getting Help
483 |
484 | 1. Check the server logs for detailed error messages
485 | 2. Verify your `.env` configuration
486 | 3. Test the database connection independently
487 | 4. Ensure all dependencies are installed correctly
488 | 5. For large content issues, use copy-paste from chat or create external extraction scripts
489 |
490 | ## Recent Enhancements
491 |
492 | ### **Large Content Handling (Latest)**
493 | - Verified complete retrieval of large stored procedures without truncation
494 | - Successfully tested with procedures like `wmPostPurchase` (1400+ lines, 57KB)
495 | - Large procedures display fully in chat interface for viewing and copy-paste
496 | - Efficient memory handling through database connection streaming
497 | - **Note**: File operations via MCP tools not reliable for large content due to token limits
498 |
499 | ### **Complete Database Object Management**
500 | - Expanded from 5 to 23 comprehensive database management tools
501 | - Added full CRUD operations for all major database objects
502 | - Implemented schema traversal capabilities matching SSMS functionality
503 | - Added MCP resource access for tables and views
504 | - Enhanced security with proper operation validation
505 |
506 | ## License
507 |
508 | This project is open source. See the license file for details.
509 |
510 | ## Contributing
511 |
512 | Contributions are welcome! Please feel free to submit pull requests or open issues for bugs and feature requests.
```
--------------------------------------------------------------------------------
/src/__init__.py:
--------------------------------------------------------------------------------
```python
1 | # MSSQL MCP Server Package
```
--------------------------------------------------------------------------------
/tests/__init__.py:
--------------------------------------------------------------------------------
```python
1 | # Test package for MSSQL MCP Server
```
--------------------------------------------------------------------------------
/requirements.txt:
--------------------------------------------------------------------------------
```
1 | pyodbc>=4.0.39
2 | pydantic>=2.0.0
3 | python-dotenv>=1.0.1
4 | mcp>=1.2.0
5 | anyio>=4.5.0
6 | asyncio-mqtt>=0.16.2
7 | pytest>=7.0.0
8 | pytest-asyncio>=0.21.0
```
--------------------------------------------------------------------------------
/setup.py:
--------------------------------------------------------------------------------
```python
1 | #!/usr/bin/env python3
2 | """
3 | Setup script for MSSQL MCP Server
4 | """
5 |
6 | from setuptools import setup, find_packages
7 |
8 | with open("README.md", "r", encoding="utf-8") as fh:
9 | long_description = fh.read()
10 |
11 | with open("requirements.txt", "r", encoding="utf-8") as fh:
12 | requirements = [line.strip() for line in fh if line.strip() and not line.startswith("#")]
13 |
14 | setup(
15 | name="mssql-mcp-server",
16 | version="1.0.0",
17 | author="MSSQL MCP Server",
18 | author_email="",
19 | description="A Model Context Protocol server for Microsoft SQL Server databases",
20 | long_description=long_description,
21 | long_description_content_type="text/markdown",
22 | url="https://github.com/your-username/mssql-mcp-server",
23 | packages=find_packages(),
24 | classifiers=[
25 | "Development Status :: 4 - Beta",
26 | "Intended Audience :: Developers",
27 | "License :: OSI Approved :: MIT License",
28 | "Operating System :: OS Independent",
29 | "Programming Language :: Python :: 3",
30 | "Programming Language :: Python :: 3.8",
31 | "Programming Language :: Python :: 3.9",
32 | "Programming Language :: Python :: 3.10",
33 | "Programming Language :: Python :: 3.11",
34 | "Programming Language :: Python :: 3.12",
35 | ],
36 | python_requires=">=3.8",
37 | install_requires=requirements,
38 | entry_points={
39 | "console_scripts": [
40 | "mssql-mcp-server=src.server:main",
41 | ],
42 | },
43 | include_package_data=True,
44 | zip_safe=False,
45 | )
```
--------------------------------------------------------------------------------
/install.sh:
--------------------------------------------------------------------------------
```bash
1 | #!/bin/bash
2 |
3 | # MSSQL MCP Server Installation Script
4 |
5 | echo "🚀 Installing MSSQL MCP Server..."
6 |
7 | # Check if Python is installed
8 | if ! command -v python3 &> /dev/null; then
9 | echo "❌ Python 3 is not installed. Please install Python 3.8 or higher."
10 | exit 1
11 | fi
12 |
13 | # Check Python version
14 | python_version=$(python3 -c "import sys; print('.'.join(map(str, sys.version_info[:2])))")
15 | required_version="3.8"
16 |
17 | if [ "$(printf '%s\n' "$required_version" "$python_version" | sort -V | head -n1)" != "$required_version" ]; then
18 | echo "❌ Python $python_version is installed, but Python $required_version or higher is required."
19 | exit 1
20 | fi
21 |
22 | echo "✅ Python $python_version detected"
23 |
24 | # Create virtual environment if it doesn't exist
25 | if [ ! -d "venv" ]; then
26 | echo "📦 Creating virtual environment..."
27 | python3 -m venv venv
28 | fi
29 |
30 | # Activate virtual environment
31 | echo "🔧 Activating virtual environment..."
32 | source venv/bin/activate
33 |
34 | # Upgrade pip
35 | echo "⬆️ Upgrading pip..."
36 | pip install --upgrade pip
37 |
38 | # Install requirements
39 | echo "📥 Installing dependencies..."
40 | pip install -r requirements.txt
41 |
42 | # Make server executable
43 | chmod +x src/server.py
44 |
45 | echo "✅ Installation complete!"
46 | echo ""
47 | echo "📋 Next steps:"
48 | echo "1. Copy env.example to .env and configure your database settings"
49 | echo "2. Run the server with: python src/server.py"
50 | echo "3. Or activate the virtual environment and run: source venv/bin/activate && python src/server.py"
51 | echo ""
52 | echo "📖 For Claude Desktop integration, see README.md"
```
--------------------------------------------------------------------------------
/stop.sh:
--------------------------------------------------------------------------------
```bash
1 | #!/bin/bash
2 |
3 | # MSSQL MCP Server Stop Script
4 |
5 | # Colors for output
6 | RED='\033[0;31m'
7 | GREEN='\033[0;32m'
8 | YELLOW='\033[1;33m'
9 | BLUE='\033[0;34m'
10 | NC='\033[0m' # No Color
11 |
12 | # Function to print colored output
13 | print_error() {
14 | echo -e "${RED}❌ $1${NC}"
15 | }
16 |
17 | print_success() {
18 | echo -e "${GREEN}✅ $1${NC}"
19 | }
20 |
21 | print_warning() {
22 | echo -e "${YELLOW}⚠️ $1${NC}"
23 | }
24 |
25 | print_info() {
26 | echo -e "${BLUE}ℹ️ $1${NC}"
27 | }
28 |
29 | echo "🛑 Stopping MSSQL MCP Server..."
30 |
31 | # Check if PID file exists
32 | if [ ! -f "server.pid" ]; then
33 | print_warning "No PID file found. Server may not be running."
34 |
35 | # Check if any python processes are running the server
36 | server_pids=$(pgrep -f "src/server.py" 2>/dev/null || true)
37 |
38 | if [ -n "$server_pids" ]; then
39 | print_warning "Found running server processes. Attempting to stop them..."
40 | echo "$server_pids" | while read pid; do
41 | if [ -n "$pid" ]; then
42 | print_info "Stopping process $pid..."
43 | kill $pid 2>/dev/null || true
44 |
45 | # Wait for process to stop
46 | for i in {1..10}; do
47 | if ! ps -p $pid > /dev/null 2>&1; then
48 | print_success "Process $pid stopped"
49 | break
50 | fi
51 | sleep 1
52 | done
53 |
54 | # Force kill if still running
55 | if ps -p $pid > /dev/null 2>&1; then
56 | print_warning "Force killing process $pid..."
57 | kill -9 $pid 2>/dev/null || true
58 | fi
59 | fi
60 | done
61 | print_success "All server processes stopped"
62 | else
63 | print_info "No running server processes found"
64 | fi
65 | exit 0
66 | fi
67 |
68 | # Read PID from file
69 | pid=$(cat server.pid)
70 |
71 | # Check if process is actually running
72 | if ! ps -p $pid > /dev/null 2>&1; then
73 | print_warning "Process $pid is not running. Cleaning up PID file..."
74 | rm server.pid
75 | print_success "Cleanup complete"
76 | exit 0
77 | fi
78 |
79 | print_info "Stopping server process $pid..."
80 |
81 | # Try graceful shutdown first
82 | kill $pid 2>/dev/null
83 |
84 | # Wait for process to stop gracefully
85 | stopped=false
86 | for i in {1..10}; do
87 | if ! ps -p $pid > /dev/null 2>&1; then
88 | stopped=true
89 | break
90 | fi
91 | print_info "Waiting for graceful shutdown... ($i/10)"
92 | sleep 1
93 | done
94 |
95 | if [ "$stopped" = false ]; then
96 | print_warning "Graceful shutdown failed. Force killing process..."
97 | kill -9 $pid 2>/dev/null || true
98 |
99 | # Wait a bit more
100 | sleep 2
101 |
102 | if ps -p $pid > /dev/null 2>&1; then
103 | print_error "Failed to stop process $pid"
104 | exit 1
105 | fi
106 | fi
107 |
108 | # Clean up PID file
109 | rm server.pid
110 |
111 | print_success "MSSQL MCP Server stopped successfully"
112 |
113 | # Show final status
114 | print_info "Server status: Stopped"
115 | if [ -f "server.log" ]; then
116 | print_info "Logs are available in server.log"
117 |
118 | # Show last few lines of log
119 | echo ""
120 | print_info "Last few log entries:"
121 | tail -5 server.log 2>/dev/null || print_warning "Could not read log file"
122 | fi
```
--------------------------------------------------------------------------------
/status.sh:
--------------------------------------------------------------------------------
```bash
1 | #!/bin/bash
2 |
3 | # MSSQL MCP Server Status Script
4 |
5 | # Colors for output
6 | RED='\033[0;31m'
7 | GREEN='\033[0;32m'
8 | YELLOW='\033[1;33m'
9 | BLUE='\033[0;34m'
10 | NC='\033[0m' # No Color
11 |
12 | # Function to print colored output
13 | print_error() {
14 | echo -e "${RED}❌ $1${NC}"
15 | }
16 |
17 | print_success() {
18 | echo -e "${GREEN}✅ $1${NC}"
19 | }
20 |
21 | print_warning() {
22 | echo -e "${YELLOW}⚠️ $1${NC}"
23 | }
24 |
25 | print_info() {
26 | echo -e "${BLUE}ℹ️ $1${NC}"
27 | }
28 |
29 | echo "📊 MSSQL MCP Server Status"
30 | echo "=========================="
31 |
32 | # Check if PID file exists
33 | if [ ! -f "server.pid" ]; then
34 | print_warning "No PID file found"
35 |
36 | # Check if any python processes are running the server
37 | server_pids=$(pgrep -f "src/server.py" 2>/dev/null || true)
38 |
39 | if [ -n "$server_pids" ]; then
40 | print_warning "Server processes found running without PID file:"
41 | echo "$server_pids" | while read pid; do
42 | if [ -n "$pid" ]; then
43 | echo " PID: $pid"
44 | fi
45 | done
46 | print_info "Consider running './stop.sh' to clean up"
47 | else
48 | print_error "Server is not running"
49 | fi
50 | exit 1
51 | fi
52 |
53 | # Read PID from file
54 | pid=$(cat server.pid)
55 |
56 | # Check if process is actually running
57 | if ! ps -p $pid > /dev/null 2>&1; then
58 | print_error "Server is not running (stale PID file)"
59 | print_info "PID file contains: $pid"
60 | print_info "Run './stop.sh' to clean up or './start.sh' to restart"
61 | exit 1
62 | fi
63 |
64 | # Server is running
65 | print_success "Server is running"
66 | echo " PID: $pid"
67 |
68 | # Get process information
69 | if command -v ps &> /dev/null; then
70 | process_info=$(ps -p $pid -o pid,ppid,etime,pcpu,pmem,cmd --no-headers 2>/dev/null || true)
71 | if [ -n "$process_info" ]; then
72 | echo " Process Info:"
73 | echo " $process_info"
74 | fi
75 | fi
76 |
77 | # Check log file
78 | if [ -f "server.log" ]; then
79 | log_size=$(wc -c < server.log 2>/dev/null || echo "unknown")
80 | log_lines=$(wc -l < server.log 2>/dev/null || echo "unknown")
81 | print_info "Log file: server.log ($log_lines lines, $log_size bytes)"
82 |
83 | # Show last few lines of log
84 | echo ""
85 | print_info "Recent log entries:"
86 | tail -10 server.log 2>/dev/null | sed 's/^/ /' || print_warning "Could not read log file"
87 | else
88 | print_warning "No log file found"
89 | fi
90 |
91 | # Check environment configuration
92 | echo ""
93 | print_info "Environment Configuration:"
94 | if [ -f ".env" ]; then
95 | print_success ".env file exists"
96 |
97 | # Check key variables (without showing sensitive data)
98 | if grep -q "MSSQL_SERVER=" .env 2>/dev/null; then
99 | server_value=$(grep "MSSQL_SERVER=" .env | cut -d'=' -f2 | sed 's/^[[:space:]]*//' | sed 's/[[:space:]]*$//')
100 | if [ -n "$server_value" ]; then
101 | print_success "MSSQL_SERVER is configured"
102 | else
103 | print_warning "MSSQL_SERVER is empty"
104 | fi
105 | else
106 | print_warning "MSSQL_SERVER not found in .env"
107 | fi
108 |
109 | if grep -q "MSSQL_DATABASE=" .env 2>/dev/null; then
110 | db_value=$(grep "MSSQL_DATABASE=" .env | cut -d'=' -f2 | sed 's/^[[:space:]]*//' | sed 's/[[:space:]]*$//')
111 | if [ -n "$db_value" ]; then
112 | print_success "MSSQL_DATABASE is configured"
113 | else
114 | print_warning "MSSQL_DATABASE is empty"
115 | fi
116 | else
117 | print_warning "MSSQL_DATABASE not found in .env"
118 | fi
119 |
120 | if grep -q "MSSQL_USER=" .env 2>/dev/null; then
121 | print_success "MSSQL_USER is configured"
122 | else
123 | print_info "MSSQL_USER not configured (may be using Windows auth)"
124 | fi
125 | else
126 | print_error ".env file not found"
127 | fi
128 |
129 | # Check virtual environment
130 | echo ""
131 | print_info "Virtual Environment:"
132 | if [ -d "venv" ]; then
133 | print_success "Virtual environment exists"
134 | if [ -n "$VIRTUAL_ENV" ]; then
135 | print_success "Virtual environment is activated"
136 | else
137 | print_info "Virtual environment is not activated in current shell"
138 | fi
139 | else
140 | print_warning "Virtual environment not found"
141 | fi
142 |
143 | echo ""
144 | print_info "Control Commands:"
145 | echo " Start: ./start.sh"
146 | echo " Stop: ./stop.sh"
147 | echo " Status: ./status.sh"
148 | echo " Logs: tail -f server.log"
```
--------------------------------------------------------------------------------
/start.sh:
--------------------------------------------------------------------------------
```bash
1 | #!/bin/bash
2 |
3 | # MSSQL MCP Server Start Script
4 |
5 | set -e # Exit on any error
6 |
7 | echo "🚀 Starting MSSQL MCP Server..."
8 |
9 | # Colors for output
10 | RED='\033[0;31m'
11 | GREEN='\033[0;32m'
12 | YELLOW='\033[1;33m'
13 | BLUE='\033[0;34m'
14 | NC='\033[0m' # No Color
15 |
16 | # Function to print colored output
17 | print_error() {
18 | echo -e "${RED}❌ $1${NC}"
19 | }
20 |
21 | print_success() {
22 | echo -e "${GREEN}✅ $1${NC}"
23 | }
24 |
25 | print_warning() {
26 | echo -e "${YELLOW}⚠️ $1${NC}"
27 | }
28 |
29 | print_info() {
30 | echo -e "${BLUE}ℹ️ $1${NC}"
31 | }
32 |
33 | # Check if Python is installed
34 | if ! command -v python3 &> /dev/null; then
35 | print_error "Python 3 is not installed. Please install Python 3.8 or higher."
36 | exit 1
37 | fi
38 |
39 | # Check Python version
40 | python_version=$(python3 -c "import sys; print('.'.join(map(str, sys.version_info[:2])))")
41 | required_version="3.8"
42 |
43 | if [ "$(printf '%s\n' "$required_version" "$python_version" | sort -V | head -n1)" != "$required_version" ]; then
44 | print_error "Python $python_version is installed, but Python $required_version or higher is required."
45 | exit 1
46 | fi
47 |
48 | print_success "Python $python_version detected"
49 |
50 | # Check if virtual environment exists
51 | if [ ! -d "venv" ]; then
52 | print_warning "Virtual environment not found. Creating one..."
53 | python3 -m venv venv
54 | print_success "Virtual environment created"
55 | fi
56 |
57 | # Activate virtual environment
58 | print_info "Activating virtual environment..."
59 | source venv/bin/activate
60 |
61 | # Check if dependencies are installed
62 | print_info "Checking dependencies..."
63 | if ! python -c "import pyodbc, pydantic, mcp" &> /dev/null; then
64 | print_warning "Dependencies not found. Installing..."
65 | pip install -r requirements.txt
66 | print_success "Dependencies installed"
67 | fi
68 |
69 | # Environment validation
70 | print_info "Validating environment configuration..."
71 |
72 | # Check if .env file exists
73 | if [ ! -f ".env" ]; then
74 | print_error ".env file not found!"
75 | print_info "Please create a .env file based on env.example:"
76 | echo ""
77 | echo " cp env.example .env"
78 | echo ""
79 | print_info "Then edit .env with your database configuration."
80 | exit 1
81 | fi
82 |
83 | # Load environment variables from .env file using Python to handle special characters
84 | eval $(python3 -c "
85 | import os
86 | import shlex
87 | from dotenv import load_dotenv
88 | load_dotenv()
89 | for key, value in os.environ.items():
90 | if key.startswith('MSSQL_') or key in ['TrustServerCertificate', 'Trusted_Connection']:
91 | # Use shlex.quote to properly escape the value
92 | escaped_value = shlex.quote(value)
93 | print(f'export {key}={escaped_value}')
94 | ")
95 |
96 | # Validate required environment variables
97 | validation_failed=false
98 |
99 | validate_env_var() {
100 | local var_name=$1
101 | local var_value=${!var_name}
102 |
103 | if [ -z "$var_value" ]; then
104 | print_error "$var_name is not set in .env file"
105 | validation_failed=true
106 | return 1
107 | else
108 | print_success "$var_name is configured"
109 | return 0
110 | fi
111 | }
112 |
113 | # Check required variables
114 | validate_env_var "MSSQL_SERVER"
115 | validate_env_var "MSSQL_DATABASE"
116 |
117 | # Check if authentication is properly configured
118 | if [ -z "$MSSQL_USER" ] && [ "$Trusted_Connection" != "yes" ]; then
119 | print_error "Either MSSQL_USER must be set or Trusted_Connection must be 'yes'"
120 | validation_failed=true
121 | fi
122 |
123 | if [ -n "$MSSQL_USER" ] && [ -z "$MSSQL_PASSWORD" ]; then
124 | print_error "MSSQL_PASSWORD must be set when MSSQL_USER is provided"
125 | validation_failed=true
126 | fi
127 |
128 | if [ "$validation_failed" = true ]; then
129 | print_error "Environment validation failed. Please check your .env file."
130 | echo ""
131 | print_info "Required variables:"
132 | echo " - MSSQL_SERVER: Your SQL Server hostname/IP"
133 | echo " - MSSQL_DATABASE: Database name to connect to"
134 | echo " - MSSQL_USER: Username (if not using Windows authentication)"
135 | echo " - MSSQL_PASSWORD: Password (if using SQL Server authentication)"
136 | echo ""
137 | print_info "Optional variables:"
138 | echo " - MSSQL_PORT: Port number (default: 1433)"
139 | echo " - MSSQL_DRIVER: ODBC driver (default: {ODBC Driver 17 for SQL Server})"
140 | echo " - TrustServerCertificate: yes/no (default: yes)"
141 | echo " - Trusted_Connection: yes/no (default: no)"
142 | exit 1
143 | fi
144 |
145 | # Test database connection
146 | print_info "Testing database connection..."
147 | if python3 -c "
148 | import os
149 | import pyodbc
150 | from dotenv import load_dotenv
151 |
152 | load_dotenv()
153 |
154 | driver = os.getenv('MSSQL_DRIVER', '{ODBC Driver 17 for SQL Server}')
155 | server = os.getenv('MSSQL_SERVER')
156 | database = os.getenv('MSSQL_DATABASE')
157 | username = os.getenv('MSSQL_USER', '')
158 | password = os.getenv('MSSQL_PASSWORD', '')
159 | port = os.getenv('MSSQL_PORT', '1433')
160 | trust_cert = os.getenv('TrustServerCertificate', 'yes')
161 | trusted_conn = os.getenv('Trusted_Connection', 'no')
162 |
163 | conn_str = f'DRIVER={driver};SERVER={server},{port};DATABASE={database};'
164 | if username and password:
165 | conn_str += f'UID={username};PWD={password};'
166 | conn_str += f'TrustServerCertificate={trust_cert};Trusted_Connection={trusted_conn};'
167 |
168 | try:
169 | conn = pyodbc.connect(conn_str, timeout=10)
170 | cursor = conn.cursor()
171 | cursor.execute('SELECT 1')
172 | cursor.fetchone()
173 | conn.close()
174 | print('Connection successful')
175 | except Exception as e:
176 | print(f'Connection failed: {e}')
177 | exit(1)
178 | " 2>/dev/null; then
179 | print_success "Database connection test passed"
180 | else
181 | print_error "Database connection test failed"
182 | print_info "Please check your database configuration and ensure:"
183 | echo " - SQL Server is running and accessible"
184 | echo " - Database exists and you have access permissions"
185 | echo " - Network connectivity is available"
186 | echo " - ODBC Driver 17 for SQL Server is installed"
187 | exit 1
188 | fi
189 |
190 | # Check if server is already running
191 | if [ -f "server.pid" ]; then
192 | pid=$(cat server.pid)
193 | if ps -p $pid > /dev/null 2>&1; then
194 | print_warning "Server is already running (PID: $pid)"
195 | print_info "Use './stop.sh' to stop the server first"
196 | exit 1
197 | else
198 | print_warning "Stale PID file found, removing..."
199 | rm server.pid
200 | fi
201 | fi
202 |
203 | # Start the server
204 | print_success "All validations passed! Starting MCP server..."
205 | echo ""
206 | print_info "Server will run in the background. Use './stop.sh' to stop it."
207 | print_info "Logs will be written to server.log"
208 | echo ""
209 |
210 | # Start server in background and save PID
211 | nohup python3 src/server.py > server.log 2>&1 &
212 | server_pid=$!
213 | echo $server_pid > server.pid
214 |
215 | # Wait a moment and check if server started successfully
216 | sleep 2
217 | if ps -p $server_pid > /dev/null 2>&1; then
218 | print_success "MSSQL MCP Server started successfully (PID: $server_pid)"
219 | print_info "Server is running in the background"
220 | print_info "View logs with: tail -f server.log"
221 | else
222 | print_error "Failed to start server. Check server.log for details."
223 | rm -f server.pid
224 | exit 1
225 | fi
```
--------------------------------------------------------------------------------
/tests/test_server.py:
--------------------------------------------------------------------------------
```python
1 | #!/usr/bin/env python3
2 | """
3 | Tests for MSSQL MCP Server
4 | """
5 |
6 | import os
7 | import pytest
8 | import asyncio
9 | from unittest.mock import Mock, patch, MagicMock
10 | from src.server import MSSQLServer
11 |
12 |
13 | class TestMSSQLServer:
14 | """Test cases for MSSQLServer class."""
15 |
16 | def test_build_connection_string_with_credentials(self):
17 | """Test connection string building with username/password."""
18 | with patch.dict(os.environ, {
19 | 'MSSQL_SERVER': 'testserver',
20 | 'MSSQL_DATABASE': 'testdb',
21 | 'MSSQL_USER': 'testuser',
22 | 'MSSQL_PASSWORD': 'testpass',
23 | 'MSSQL_PORT': '1433',
24 | 'MSSQL_DRIVER': '{ODBC Driver 17 for SQL Server}'
25 | }):
26 | server = MSSQLServer()
27 | conn_str = server.connection_string
28 |
29 | assert 'SERVER=testserver,1433' in conn_str
30 | assert 'DATABASE=testdb' in conn_str
31 | assert 'UID=testuser' in conn_str
32 | assert 'PWD=testpass' in conn_str
33 | assert 'DRIVER={ODBC Driver 17 for SQL Server}' in conn_str
34 |
35 | def test_build_connection_string_missing_required(self):
36 | """Test connection string building with missing required variables."""
37 | with patch.dict(os.environ, {}, clear=True):
38 | with pytest.raises(ValueError, match="MSSQL_SERVER and MSSQL_DATABASE must be set"):
39 | MSSQLServer()
40 |
41 | @patch('src.server.pyodbc.connect')
42 | def test_get_connection_success(self, mock_connect):
43 | """Test successful database connection."""
44 | mock_connection = Mock()
45 | mock_connect.return_value = mock_connection
46 |
47 | with patch.dict(os.environ, {
48 | 'MSSQL_SERVER': 'testserver',
49 | 'MSSQL_DATABASE': 'testdb'
50 | }):
51 | server = MSSQLServer()
52 | connection = server._get_connection()
53 |
54 | assert connection == mock_connection
55 | mock_connect.assert_called_once()
56 |
57 | @patch('src.server.pyodbc.connect')
58 | def test_get_connection_failure(self, mock_connect):
59 | """Test database connection failure."""
60 | mock_connect.side_effect = Exception("Connection failed")
61 |
62 | with patch.dict(os.environ, {
63 | 'MSSQL_SERVER': 'testserver',
64 | 'MSSQL_DATABASE': 'testdb'
65 | }):
66 | server = MSSQLServer()
67 |
68 | with pytest.raises(Exception, match="Connection failed"):
69 | server._get_connection()
70 |
71 | @pytest.mark.asyncio
72 | async def test_execute_read_query_valid(self):
73 | """Test executing a valid SELECT query."""
74 | with patch.dict(os.environ, {
75 | 'MSSQL_SERVER': 'testserver',
76 | 'MSSQL_DATABASE': 'testdb'
77 | }):
78 | server = MSSQLServer()
79 |
80 | # Mock database connection and cursor
81 | mock_connection = Mock()
82 | mock_cursor = Mock()
83 | mock_connection.cursor.return_value = mock_cursor
84 | mock_cursor.fetchall.return_value = [('John', 25), ('Jane', 30)]
85 | mock_cursor.description = [('name',), ('age',)]
86 |
87 | with patch.object(server, '_get_connection', return_value=mock_connection):
88 | result = await server._execute_read_query("SELECT name, age FROM users")
89 |
90 | assert len(result) == 1
91 | assert "name,age" in result[0].text
92 | assert "John,25" in result[0].text
93 | assert "Jane,30" in result[0].text
94 |
95 | @pytest.mark.asyncio
96 | async def test_execute_read_query_invalid(self):
97 | """Test executing an invalid query (not SELECT)."""
98 | with patch.dict(os.environ, {
99 | 'MSSQL_SERVER': 'testserver',
100 | 'MSSQL_DATABASE': 'testdb'
101 | }):
102 | server = MSSQLServer()
103 |
104 | with pytest.raises(ValueError, match="Only SELECT queries are allowed"):
105 | await server._execute_read_query("DELETE FROM users")
106 |
107 | @pytest.mark.asyncio
108 | async def test_execute_write_query_valid(self):
109 | """Test executing a valid INSERT query."""
110 | with patch.dict(os.environ, {
111 | 'MSSQL_SERVER': 'testserver',
112 | 'MSSQL_DATABASE': 'testdb'
113 | }):
114 | server = MSSQLServer()
115 |
116 | # Mock database connection and cursor
117 | mock_connection = Mock()
118 | mock_cursor = Mock()
119 | mock_connection.cursor.return_value = mock_cursor
120 | mock_cursor.rowcount = 1
121 |
122 | with patch.object(server, '_get_connection', return_value=mock_connection):
123 | result = await server._execute_write_query("INSERT INTO users (name) VALUES ('Test')")
124 |
125 | assert len(result) == 1
126 | assert "1 rows affected" in result[0].text
127 | mock_connection.commit.assert_called_once()
128 |
129 | @pytest.mark.asyncio
130 | async def test_list_tables(self):
131 | """Test listing database tables."""
132 | with patch.dict(os.environ, {
133 | 'MSSQL_SERVER': 'testserver',
134 | 'MSSQL_DATABASE': 'testdb'
135 | }):
136 | server = MSSQLServer()
137 |
138 | # Mock database connection and cursor
139 | mock_connection = Mock()
140 | mock_cursor = Mock()
141 | mock_connection.cursor.return_value = mock_cursor
142 | mock_cursor.fetchall.return_value = [('users', 'BASE TABLE'), ('orders', 'BASE TABLE')]
143 |
144 | with patch.object(server, '_get_connection', return_value=mock_connection):
145 | result = await server._list_tables()
146 |
147 | assert len(result) == 1
148 | assert "users (BASE TABLE)" in result[0].text
149 | assert "orders (BASE TABLE)" in result[0].text
150 |
151 | @pytest.mark.asyncio
152 | async def test_describe_table(self):
153 | """Test describing a table schema."""
154 | with patch.dict(os.environ, {
155 | 'MSSQL_SERVER': 'testserver',
156 | 'MSSQL_DATABASE': 'testdb'
157 | }):
158 | server = MSSQLServer()
159 |
160 | # Mock database connection and cursor
161 | mock_connection = Mock()
162 | mock_cursor = Mock()
163 | mock_connection.cursor.return_value = mock_cursor
164 | mock_cursor.fetchall.return_value = [
165 | ('id', 'int', 'NO', None, None),
166 | ('name', 'varchar', 'YES', None, 255)
167 | ]
168 |
169 | with patch.object(server, '_get_connection', return_value=mock_connection):
170 | result = await server._describe_table('users')
171 |
172 | assert len(result) == 1
173 | assert "Schema for table 'users'" in result[0].text
174 | assert "id | int | NO" in result[0].text
175 | assert "name | varchar | YES" in result[0].text
176 |
177 |
178 | if __name__ == "__main__":
179 | pytest.main([__file__])
```
--------------------------------------------------------------------------------
/src/server.py:
--------------------------------------------------------------------------------
```python
1 | #!/usr/bin/env python3
2 | """
3 | MSSQL MCP Server
4 |
5 | A Model Context Protocol server that provides access to Microsoft SQL Server databases.
6 | Enables Language Models to inspect table schemas and execute SQL queries.
7 | """
8 |
9 | import asyncio
10 | import logging
11 | import os
12 | import sys
13 | import traceback
14 | from typing import Any, Dict, List, Optional, Sequence
15 | from urllib.parse import urlparse
16 |
17 | import pyodbc
18 | from dotenv import load_dotenv
19 | from mcp.server import Server, NotificationOptions
20 | from mcp.server.models import InitializationOptions
21 | from mcp.server.stdio import stdio_server
22 | from mcp.types import (
23 | Resource,
24 | Tool,
25 | TextContent,
26 | ImageContent,
27 | EmbeddedResource,
28 | LoggingLevel
29 | )
30 | from pydantic import AnyUrl
31 |
32 | # Load environment variables
33 | load_dotenv()
34 |
35 | # Configure logging
36 | logging.basicConfig(level=logging.DEBUG, format='%(asctime)s - %(name)s - %(levelname)s - %(message)s')
37 | logger = logging.getLogger("mssql-mcp-server")
38 |
39 | class MSSQLServer:
40 | def __init__(self):
41 | self.connection_string = self._build_connection_string()
42 | self.server = Server("mssql-mcp-server")
43 | self._setup_handlers()
44 |
45 | def _build_connection_string(self) -> str:
46 | """Build MSSQL connection string from environment variables."""
47 | driver = os.getenv("MSSQL_DRIVER", "{ODBC Driver 17 for SQL Server}")
48 | server = os.getenv("MSSQL_SERVER", "localhost")
49 | database = os.getenv("MSSQL_DATABASE", "")
50 | username = os.getenv("MSSQL_USER", "")
51 | password = os.getenv("MSSQL_PASSWORD", "")
52 | port = os.getenv("MSSQL_PORT", "1433")
53 | trust_cert = os.getenv("TrustServerCertificate", "yes")
54 | trusted_conn = os.getenv("Trusted_Connection", "no")
55 |
56 | if not all([server, database]):
57 | raise ValueError("MSSQL_SERVER and MSSQL_DATABASE must be set")
58 |
59 | conn_str = f"DRIVER={driver};SERVER={server},{port};DATABASE={database};"
60 |
61 | if username and password:
62 | conn_str += f"UID={username};PWD={password};"
63 |
64 | conn_str += f"TrustServerCertificate={trust_cert};Trusted_Connection={trusted_conn};"
65 |
66 | return conn_str
67 |
68 | def _get_connection(self):
69 | """Get database connection."""
70 | try:
71 | return pyodbc.connect(self.connection_string)
72 | except Exception as e:
73 | logger.error(f"Failed to connect to database: {e}")
74 | raise
75 |
76 | def _setup_handlers(self):
77 | """Set up MCP handlers."""
78 |
79 | @self.server.list_resources()
80 | async def list_resources() -> List[Resource]:
81 | """List all available database tables as resources."""
82 | try:
83 | with self._get_connection() as conn:
84 | cursor = conn.cursor()
85 | cursor.execute("""
86 | SELECT TABLE_NAME, TABLE_TYPE
87 | FROM INFORMATION_SCHEMA.TABLES
88 | WHERE TABLE_TYPE IN ('BASE TABLE', 'VIEW')
89 | ORDER BY TABLE_TYPE, TABLE_NAME
90 | """)
91 | tables = cursor.fetchall()
92 |
93 | resources = []
94 | for table in tables:
95 | table_name = table[0]
96 | table_type = table[1]
97 | if table_type == 'BASE TABLE':
98 | resources.append(Resource(
99 | uri=AnyUrl(f"mssql://{table_name}/data"),
100 | name=f"Table: {table_name}",
101 | description=f"Data from {table_name} table",
102 | mimeType="text/csv"
103 | ))
104 | else: # VIEW
105 | resources.append(Resource(
106 | uri=AnyUrl(f"mssql://{table_name}/data"),
107 | name=f"View: {table_name}",
108 | description=f"Data from {table_name} view",
109 | mimeType="text/csv"
110 | ))
111 |
112 | return resources
113 | except Exception as e:
114 | logger.error(f"Error listing resources: {e}")
115 | return []
116 |
117 | @self.server.read_resource()
118 | async def read_resource(uri: AnyUrl) -> str:
119 | """Read data from a specific table."""
120 | try:
121 | # Parse the URI to get table name
122 | parsed = urlparse(str(uri))
123 | if parsed.scheme != "mssql":
124 | raise ValueError("Invalid URI scheme")
125 |
126 | table_name = parsed.netloc
127 | if not table_name:
128 | raise ValueError("Table name not specified in URI")
129 |
130 | with self._get_connection() as conn:
131 | cursor = conn.cursor()
132 | # Get first 100 rows
133 | cursor.execute(f"SELECT TOP 100 * FROM [{table_name}]")
134 | rows = cursor.fetchall()
135 |
136 | if not rows:
137 | return "No data found"
138 |
139 | # Get column names
140 | columns = [desc[0] for desc in cursor.description]
141 |
142 | # Format as CSV
143 | csv_data = ",".join(columns) + "\n"
144 | for row in rows:
145 | csv_data += ",".join(str(cell) if cell is not None else "" for cell in row) + "\n"
146 |
147 | return csv_data
148 |
149 | except Exception as e:
150 | logger.error(f"Error reading resource {uri}: {e}")
151 | return f"Error: {str(e)}"
152 |
153 | @self.server.list_tools()
154 | async def list_tools() -> List[Tool]:
155 | """List available tools."""
156 | return [
157 | Tool(
158 | name="read_query",
159 | description="Execute a SELECT query to read data from the database",
160 | inputSchema={
161 | "type": "object",
162 | "properties": {
163 | "query": {
164 | "type": "string",
165 | "description": "SELECT SQL query to execute"
166 | }
167 | },
168 | "required": ["query"]
169 | }
170 | ),
171 | Tool(
172 | name="write_query",
173 | description="Execute an INSERT, UPDATE, or DELETE query",
174 | inputSchema={
175 | "type": "object",
176 | "properties": {
177 | "query": {
178 | "type": "string",
179 | "description": "SQL query to execute (INSERT, UPDATE, DELETE)"
180 | }
181 | },
182 | "required": ["query"]
183 | }
184 | ),
185 | Tool(
186 | name="list_tables",
187 | description="List all tables in the database",
188 | inputSchema={
189 | "type": "object",
190 | "properties": {}
191 | }
192 | ),
193 | Tool(
194 | name="describe_table",
195 | description="Get schema information for a specific table",
196 | inputSchema={
197 | "type": "object",
198 | "properties": {
199 | "table_name": {
200 | "type": "string",
201 | "description": "Name of the table to describe"
202 | }
203 | },
204 | "required": ["table_name"]
205 | }
206 | ),
207 | Tool(
208 | name="create_table",
209 | description="Create a new table in the database",
210 | inputSchema={
211 | "type": "object",
212 | "properties": {
213 | "query": {
214 | "type": "string",
215 | "description": "CREATE TABLE SQL statement"
216 | }
217 | },
218 | "required": ["query"]
219 | }
220 | ),
221 | Tool(
222 | name="create_procedure",
223 | description="Create a new stored procedure",
224 | inputSchema={
225 | "type": "object",
226 | "properties": {
227 | "procedure_script": {
228 | "type": "string",
229 | "description": "Complete T-SQL script to create the stored procedure (including CREATE PROCEDURE statement)"
230 | }
231 | },
232 | "required": ["procedure_script"]
233 | }
234 | ),
235 | Tool(
236 | name="modify_procedure",
237 | description="Modify an existing stored procedure",
238 | inputSchema={
239 | "type": "object",
240 | "properties": {
241 | "procedure_script": {
242 | "type": "string",
243 | "description": "Complete T-SQL script to alter the stored procedure (using ALTER PROCEDURE statement)"
244 | }
245 | },
246 | "required": ["procedure_script"]
247 | }
248 | ),
249 | Tool(
250 | name="delete_procedure",
251 | description="Delete a stored procedure",
252 | inputSchema={
253 | "type": "object",
254 | "properties": {
255 | "procedure_name": {
256 | "type": "string",
257 | "description": "Name of the stored procedure to delete"
258 | }
259 | },
260 | "required": ["procedure_name"]
261 | }
262 | ),
263 | Tool(
264 | name="list_procedures",
265 | description="List all stored procedures in the database",
266 | inputSchema={
267 | "type": "object",
268 | "properties": {}
269 | }
270 | ),
271 | Tool(
272 | name="describe_procedure",
273 | description="Get detailed information about a stored procedure including its definition",
274 | inputSchema={
275 | "type": "object",
276 | "properties": {
277 | "procedure_name": {
278 | "type": "string",
279 | "description": "Name of the stored procedure to describe"
280 | }
281 | },
282 | "required": ["procedure_name"]
283 | }
284 | ),
285 | Tool(
286 | name="execute_procedure",
287 | description="Execute a stored procedure with optional parameters",
288 | inputSchema={
289 | "type": "object",
290 | "properties": {
291 | "procedure_name": {
292 | "type": "string",
293 | "description": "Name of the stored procedure to execute"
294 | },
295 | "parameters": {
296 | "type": "array",
297 | "description": "Optional array of parameter values",
298 | "items": {
299 | "type": "string"
300 | }
301 | }
302 | },
303 | "required": ["procedure_name"]
304 | }
305 | ),
306 | Tool(
307 | name="get_procedure_parameters",
308 | description="Get parameter information for a stored procedure",
309 | inputSchema={
310 | "type": "object",
311 | "properties": {
312 | "procedure_name": {
313 | "type": "string",
314 | "description": "Name of the stored procedure to get parameters for"
315 | }
316 | },
317 | "required": ["procedure_name"]
318 | }
319 | ),
320 | Tool(
321 | name="list_views",
322 | description="List all views in the database",
323 | inputSchema={
324 | "type": "object",
325 | "properties": {}
326 | }
327 | ),
328 | Tool(
329 | name="describe_view",
330 | description="Get detailed information about a view including its definition",
331 | inputSchema={
332 | "type": "object",
333 | "properties": {
334 | "view_name": {
335 | "type": "string",
336 | "description": "Name of the view to describe"
337 | }
338 | },
339 | "required": ["view_name"]
340 | }
341 | ),
342 | Tool(
343 | name="create_view",
344 | description="Create a new view",
345 | inputSchema={
346 | "type": "object",
347 | "properties": {
348 | "view_script": {
349 | "type": "string",
350 | "description": "Complete T-SQL script to create the view (including CREATE VIEW statement)"
351 | }
352 | },
353 | "required": ["view_script"]
354 | }
355 | ),
356 | Tool(
357 | name="modify_view",
358 | description="Modify an existing view",
359 | inputSchema={
360 | "type": "object",
361 | "properties": {
362 | "view_script": {
363 | "type": "string",
364 | "description": "Complete T-SQL script to alter the view (using ALTER VIEW statement)"
365 | }
366 | },
367 | "required": ["view_script"]
368 | }
369 | ),
370 | Tool(
371 | name="delete_view",
372 | description="Delete a view",
373 | inputSchema={
374 | "type": "object",
375 | "properties": {
376 | "view_name": {
377 | "type": "string",
378 | "description": "Name of the view to delete"
379 | }
380 | },
381 | "required": ["view_name"]
382 | }
383 | ),
384 | Tool(
385 | name="list_indexes",
386 | description="List all indexes in the database",
387 | inputSchema={
388 | "type": "object",
389 | "properties": {
390 | "table_name": {
391 | "type": "string",
392 | "description": "Optional table name to filter indexes for a specific table"
393 | }
394 | }
395 | }
396 | ),
397 | Tool(
398 | name="describe_index",
399 | description="Get detailed information about an index",
400 | inputSchema={
401 | "type": "object",
402 | "properties": {
403 | "index_name": {
404 | "type": "string",
405 | "description": "Name of the index to describe"
406 | },
407 | "table_name": {
408 | "type": "string",
409 | "description": "Name of the table the index belongs to"
410 | }
411 | },
412 | "required": ["index_name", "table_name"]
413 | }
414 | ),
415 | Tool(
416 | name="create_index",
417 | description="Create a new index",
418 | inputSchema={
419 | "type": "object",
420 | "properties": {
421 | "index_script": {
422 | "type": "string",
423 | "description": "Complete T-SQL script to create the index (including CREATE INDEX statement)"
424 | }
425 | },
426 | "required": ["index_script"]
427 | }
428 | ),
429 | Tool(
430 | name="delete_index",
431 | description="Delete an index",
432 | inputSchema={
433 | "type": "object",
434 | "properties": {
435 | "index_name": {
436 | "type": "string",
437 | "description": "Name of the index to delete"
438 | },
439 | "table_name": {
440 | "type": "string",
441 | "description": "Name of the table the index belongs to"
442 | }
443 | },
444 | "required": ["index_name", "table_name"]
445 | }
446 | ),
447 | Tool(
448 | name="list_schemas",
449 | description="List all schemas in the database",
450 | inputSchema={
451 | "type": "object",
452 | "properties": {}
453 | }
454 | ),
455 | Tool(
456 | name="list_all_objects",
457 | description="List all database objects (tables, views, procedures, indexes) organized by schema",
458 | inputSchema={
459 | "type": "object",
460 | "properties": {
461 | "schema_name": {
462 | "type": "string",
463 | "description": "Optional schema name to filter objects for a specific schema"
464 | }
465 | }
466 | }
467 | )
468 | ]
469 |
470 | @self.server.call_tool()
471 | async def call_tool(name: str, arguments: Dict[str, Any]) -> List[TextContent]:
472 | """Execute tool calls."""
473 | try:
474 | if name == "read_query":
475 | return await self._execute_read_query(arguments["query"])
476 | elif name == "write_query":
477 | return await self._execute_write_query(arguments["query"])
478 | elif name == "list_tables":
479 | return await self._list_tables()
480 | elif name == "describe_table":
481 | return await self._describe_table(arguments["table_name"])
482 | elif name == "create_table":
483 | return await self._create_table(arguments["query"])
484 | elif name == "create_procedure":
485 | return await self._create_procedure(arguments["procedure_script"])
486 | elif name == "modify_procedure":
487 | return await self._modify_procedure(arguments["procedure_script"])
488 | elif name == "delete_procedure":
489 | return await self._delete_procedure(arguments["procedure_name"])
490 | elif name == "list_procedures":
491 | return await self._list_procedures()
492 | elif name == "describe_procedure":
493 | return await self._describe_procedure(arguments["procedure_name"])
494 | elif name == "execute_procedure":
495 | return await self._execute_procedure(arguments["procedure_name"], arguments.get("parameters"))
496 | elif name == "get_procedure_parameters":
497 | return await self._get_procedure_parameters(arguments["procedure_name"])
498 | elif name == "list_views":
499 | return await self._list_views()
500 | elif name == "describe_view":
501 | return await self._describe_view(arguments["view_name"])
502 | elif name == "create_view":
503 | return await self._create_view(arguments["view_script"])
504 | elif name == "modify_view":
505 | return await self._modify_view(arguments["view_script"])
506 | elif name == "delete_view":
507 | return await self._delete_view(arguments["view_name"])
508 | elif name == "list_indexes":
509 | return await self._list_indexes(arguments.get("table_name"))
510 | elif name == "describe_index":
511 | return await self._describe_index(arguments["index_name"], arguments["table_name"])
512 | elif name == "create_index":
513 | return await self._create_index(arguments["index_script"])
514 | elif name == "delete_index":
515 | return await self._delete_index(arguments["index_name"], arguments["table_name"])
516 | elif name == "list_schemas":
517 | return await self._list_schemas()
518 | elif name == "list_all_objects":
519 | return await self._list_all_objects(arguments.get("schema_name"))
520 | else:
521 | raise ValueError(f"Unknown tool: {name}")
522 | except Exception as e:
523 | logger.error(f"Error executing tool {name}: {e}")
524 | return [TextContent(type="text", text=f"Error: {str(e)}")]
525 |
526 | async def _execute_read_query(self, query: str) -> List[TextContent]:
527 | """Execute a SELECT query."""
528 | if not query.strip().upper().startswith("SELECT"):
529 | raise ValueError("Only SELECT queries are allowed for read_query")
530 |
531 | with self._get_connection() as conn:
532 | cursor = conn.cursor()
533 | cursor.execute(query)
534 | rows = cursor.fetchall()
535 |
536 | if not rows:
537 | return [TextContent(type="text", text="No results found")]
538 |
539 | # Get column names
540 | columns = [desc[0] for desc in cursor.description]
541 |
542 | # Format as CSV
543 | csv_data = ",".join(columns) + "\n"
544 | for row in rows:
545 | csv_data += ",".join(str(cell) if cell is not None else "" for cell in row) + "\n"
546 |
547 | return [TextContent(type="text", text=csv_data)]
548 |
549 | async def _execute_write_query(self, query: str) -> List[TextContent]:
550 | """Execute an INSERT, UPDATE, DELETE, or stored procedure operation query."""
551 | query_upper = query.strip().upper()
552 | allowed_commands = ["INSERT", "UPDATE", "DELETE", "CREATE PROCEDURE", "ALTER PROCEDURE", "DROP PROCEDURE", "EXEC", "EXECUTE", "CREATE VIEW", "ALTER VIEW", "DROP VIEW", "CREATE INDEX", "DROP INDEX"]
553 |
554 | if not any(query_upper.startswith(cmd) for cmd in allowed_commands):
555 | raise ValueError("Only INSERT, UPDATE, DELETE, CREATE/ALTER/DROP PROCEDURE, CREATE/ALTER/DROP VIEW, CREATE/DROP INDEX, and EXEC queries are allowed for write_query")
556 |
557 | with self._get_connection() as conn:
558 | cursor = conn.cursor()
559 | cursor.execute(query)
560 |
561 | # For DDL operations, rowcount might not be meaningful
562 | if query_upper.startswith(("CREATE PROCEDURE", "ALTER PROCEDURE", "DROP PROCEDURE")):
563 | conn.commit()
564 | return [TextContent(type="text", text="Stored procedure operation executed successfully.")]
565 | elif query_upper.startswith(("CREATE VIEW", "ALTER VIEW", "DROP VIEW")):
566 | conn.commit()
567 | return [TextContent(type="text", text="View operation executed successfully.")]
568 | elif query_upper.startswith(("CREATE INDEX", "DROP INDEX")):
569 | conn.commit()
570 | return [TextContent(type="text", text="Index operation executed successfully.")]
571 | elif query_upper.startswith(("EXEC", "EXECUTE")):
572 | conn.commit()
573 | return [TextContent(type="text", text="Stored procedure executed successfully.")]
574 | else:
575 | affected_rows = cursor.rowcount
576 | conn.commit()
577 | return [TextContent(type="text", text=f"Query executed successfully. {affected_rows} rows affected.")]
578 |
579 | async def _list_tables(self) -> List[TextContent]:
580 | """List all tables in the database."""
581 | with self._get_connection() as conn:
582 | cursor = conn.cursor()
583 | cursor.execute("""
584 | SELECT TABLE_NAME, TABLE_TYPE
585 | FROM INFORMATION_SCHEMA.TABLES
586 | WHERE TABLE_TYPE = 'BASE TABLE'
587 | ORDER BY TABLE_NAME
588 | """)
589 | tables = cursor.fetchall()
590 |
591 | if not tables:
592 | return [TextContent(type="text", text="No tables found")]
593 |
594 | result = "Tables in database:\n"
595 | for table in tables:
596 | result += f"- {table[0]} ({table[1]})\n"
597 |
598 | return [TextContent(type="text", text=result)]
599 |
600 | async def _describe_table(self, table_name: str) -> List[TextContent]:
601 | """Get schema information for a table."""
602 | with self._get_connection() as conn:
603 | cursor = conn.cursor()
604 | cursor.execute("""
605 | SELECT
606 | COLUMN_NAME,
607 | DATA_TYPE,
608 | IS_NULLABLE,
609 | COLUMN_DEFAULT,
610 | CHARACTER_MAXIMUM_LENGTH
611 | FROM INFORMATION_SCHEMA.COLUMNS
612 | WHERE TABLE_NAME = ?
613 | ORDER BY ORDINAL_POSITION
614 | """, table_name)
615 | columns = cursor.fetchall()
616 |
617 | if not columns:
618 | return [TextContent(type="text", text=f"Table '{table_name}' not found")]
619 |
620 | result = f"Schema for table '{table_name}':\n"
621 | result += "Column Name | Data Type | Nullable | Default | Max Length\n"
622 | result += "-" * 60 + "\n"
623 |
624 | for col in columns:
625 | max_len = str(col[4]) if col[4] else "N/A"
626 | result += f"{col[0]} | {col[1]} | {col[2]} | {col[3] or 'NULL'} | {max_len}\n"
627 |
628 | return [TextContent(type="text", text=result)]
629 |
630 | async def _create_table(self, query: str) -> List[TextContent]:
631 | """Create a new table."""
632 | if not query.strip().upper().startswith("CREATE TABLE"):
633 | raise ValueError("Only CREATE TABLE statements are allowed")
634 |
635 | with self._get_connection() as conn:
636 | cursor = conn.cursor()
637 | cursor.execute(query)
638 | conn.commit()
639 |
640 | return [TextContent(type="text", text="Table created successfully")]
641 |
642 | async def _create_procedure(self, procedure_script: str) -> List[TextContent]:
643 | """Create a new stored procedure."""
644 | if not procedure_script.strip().upper().startswith("CREATE PROCEDURE"):
645 | raise ValueError("Only CREATE PROCEDURE statements are allowed")
646 |
647 | with self._get_connection() as conn:
648 | cursor = conn.cursor()
649 | cursor.execute(procedure_script)
650 | conn.commit()
651 |
652 | return [TextContent(type="text", text="Procedure created successfully")]
653 |
654 | async def _modify_procedure(self, procedure_script: str) -> List[TextContent]:
655 | """Modify an existing stored procedure."""
656 | if not procedure_script.strip().upper().startswith("ALTER PROCEDURE"):
657 | raise ValueError("Only ALTER PROCEDURE statements are allowed")
658 |
659 | with self._get_connection() as conn:
660 | cursor = conn.cursor()
661 | cursor.execute(procedure_script)
662 | conn.commit()
663 |
664 | return [TextContent(type="text", text="Procedure modified successfully")]
665 |
666 | async def _delete_procedure(self, procedure_name: str) -> List[TextContent]:
667 | """Delete a stored procedure."""
668 | with self._get_connection() as conn:
669 | cursor = conn.cursor()
670 | # SQL Server doesn't support IF EXISTS with parameters in this context
671 | cursor.execute(f"DROP PROCEDURE IF EXISTS [{procedure_name}]")
672 | conn.commit()
673 |
674 | return [TextContent(type="text", text=f"Procedure '{procedure_name}' deleted successfully")]
675 |
676 | async def _list_procedures(self) -> List[TextContent]:
677 | """List all stored procedures in the database."""
678 | with self._get_connection() as conn:
679 | cursor = conn.cursor()
680 | cursor.execute("""
681 | SELECT
682 | o.name AS ProcedureName,
683 | o.create_date AS CreatedDate,
684 | o.modify_date AS ModifiedDate,
685 | CASE
686 | WHEN EXISTS (
687 | SELECT 1 FROM sys.parameters p
688 | WHERE p.object_id = o.object_id
689 | ) THEN 'Yes'
690 | ELSE 'No'
691 | END AS HasParameters
692 | FROM sys.objects o
693 | WHERE o.type = 'P'
694 | AND o.is_ms_shipped = 0 -- Exclude system procedures
695 | ORDER BY o.name
696 | """)
697 | procedures = cursor.fetchall()
698 |
699 | if not procedures:
700 | return [TextContent(type="text", text="No user-defined stored procedures found")]
701 |
702 | result = "Stored procedures in database:\n"
703 | result += "Name | Created | Modified | Has Parameters\n"
704 | result += "-" * 60 + "\n"
705 |
706 | for proc in procedures:
707 | created = proc[1].strftime("%Y-%m-%d") if proc[1] else "N/A"
708 | modified = proc[2].strftime("%Y-%m-%d") if proc[2] else "N/A"
709 | result += f"{proc[0]} | {created} | {modified} | {proc[3]}\n"
710 |
711 | return [TextContent(type="text", text=result)]
712 |
713 | async def _describe_procedure(self, procedure_name: str) -> List[TextContent]:
714 | """Get detailed information about a stored procedure including its definition."""
715 | with self._get_connection() as conn:
716 | cursor = conn.cursor()
717 | cursor.execute("""
718 | SELECT
719 | OBJECT_DEFINITION(OBJECT_ID(?)) AS ProcedureDefinition
720 | """, procedure_name)
721 | row = cursor.fetchone()
722 |
723 | if not row or not row[0]:
724 | return [TextContent(type="text", text=f"Procedure '{procedure_name}' not found")]
725 |
726 | definition = row[0]
727 | result = f"Definition for procedure '{procedure_name}':\n"
728 | result += "=" * 50 + "\n"
729 | result += definition
730 |
731 | return [TextContent(type="text", text=result)]
732 |
733 | async def _execute_procedure(self, procedure_name: str, parameters: Optional[List[str]] = None) -> List[TextContent]:
734 | """Execute a stored procedure with optional parameters."""
735 | if parameters is None:
736 | parameters = []
737 |
738 | with self._get_connection() as conn:
739 | cursor = conn.cursor()
740 |
741 | # Build the EXEC statement
742 | if parameters:
743 | param_placeholders = ', '.join(['?' for _ in parameters])
744 | exec_statement = f"EXEC [{procedure_name}] {param_placeholders}"
745 | cursor.execute(exec_statement, *parameters)
746 | else:
747 | cursor.execute(f"EXEC [{procedure_name}]")
748 |
749 | # Try to fetch results if any
750 | try:
751 | rows = cursor.fetchall()
752 | if rows:
753 | # Get column names if available
754 | if cursor.description:
755 | columns = [desc[0] for desc in cursor.description]
756 | result = f"Procedure '{procedure_name}' executed successfully.\n\nResults:\n"
757 | result += ",".join(columns) + "\n"
758 | for row in rows:
759 | result += ",".join(str(cell) if cell is not None else "" for cell in row) + "\n"
760 | return [TextContent(type="text", text=result)]
761 | else:
762 | return [TextContent(type="text", text=f"Procedure '{procedure_name}' executed successfully. {len(rows)} rows returned.")]
763 | else:
764 | return [TextContent(type="text", text=f"Procedure '{procedure_name}' executed successfully. No results returned.")]
765 | except Exception:
766 | # Some procedures don't return results
767 | return [TextContent(type="text", text=f"Procedure '{procedure_name}' executed successfully.")]
768 |
769 | async def _get_procedure_parameters(self, procedure_name: str) -> List[TextContent]:
770 | """Get parameter information for a stored procedure."""
771 | with self._get_connection() as conn:
772 | cursor = conn.cursor()
773 | cursor.execute("""
774 | SELECT
775 | p.parameter_id,
776 | p.name AS parameter_name,
777 | TYPE_NAME(p.user_type_id) AS data_type,
778 | p.max_length,
779 | p.precision,
780 | p.scale,
781 | p.is_output,
782 | p.has_default_value,
783 | p.default_value
784 | FROM sys.parameters p
785 | INNER JOIN sys.objects o ON p.object_id = o.object_id
786 | WHERE o.name = ? AND o.type = 'P'
787 | ORDER BY p.parameter_id
788 | """, procedure_name)
789 | parameters = cursor.fetchall()
790 |
791 | if not parameters:
792 | return [TextContent(type="text", text=f"No parameters found for procedure '{procedure_name}' or procedure does not exist")]
793 |
794 | result = f"Parameters for procedure '{procedure_name}':\n"
795 | result += "ID | Name | Data Type | Length | Precision | Scale | Output | Has Default | Default Value\n"
796 | result += "-" * 90 + "\n"
797 |
798 | for param in parameters:
799 | param_id = param[0]
800 | name = param[1] or "(return value)"
801 | data_type = param[2]
802 | max_length = param[3] if param[3] != -1 else "MAX"
803 | precision = param[4] if param[4] > 0 else ""
804 | scale = param[5] if param[5] > 0 else ""
805 | is_output = "Yes" if param[6] else "No"
806 | has_default = "Yes" if param[7] else "No"
807 | default_value = param[8] if param[8] else ""
808 |
809 | result += f"{param_id} | {name} | {data_type} | {max_length} | {precision} | {scale} | {is_output} | {has_default} | {default_value}\n"
810 |
811 | return [TextContent(type="text", text=result)]
812 |
813 | async def _list_views(self) -> List[TextContent]:
814 | """List all views in the database."""
815 | with self._get_connection() as conn:
816 | cursor = conn.cursor()
817 | cursor.execute("""
818 | SELECT TABLE_NAME
819 | FROM INFORMATION_SCHEMA.TABLES
820 | WHERE TABLE_TYPE = 'VIEW'
821 | ORDER BY TABLE_NAME
822 | """)
823 | views = cursor.fetchall()
824 |
825 | if not views:
826 | return [TextContent(type="text", text="No views found")]
827 |
828 | result = "Views in database:\n"
829 | for view in views:
830 | result += f"- {view[0]}\n"
831 |
832 | return [TextContent(type="text", text=result)]
833 |
834 | async def _describe_view(self, view_name: str) -> List[TextContent]:
835 | """Get detailed information about a view including its definition."""
836 | with self._get_connection() as conn:
837 | cursor = conn.cursor()
838 | cursor.execute("""
839 | SELECT OBJECT_DEFINITION(OBJECT_ID(?)) AS ViewDefinition
840 | """, view_name)
841 | row = cursor.fetchone()
842 |
843 | if not row or not row[0]:
844 | return [TextContent(type="text", text=f"View '{view_name}' not found")]
845 |
846 | definition = row[0]
847 | result = f"Definition for view '{view_name}':\n"
848 | result += "=" * 50 + "\n"
849 | result += definition
850 |
851 | return [TextContent(type="text", text=result)]
852 |
853 | async def _create_view(self, view_script: str) -> List[TextContent]:
854 | """Create a new view."""
855 | if not view_script.strip().upper().startswith("CREATE VIEW"):
856 | raise ValueError("Only CREATE VIEW statements are allowed")
857 |
858 | with self._get_connection() as conn:
859 | cursor = conn.cursor()
860 | cursor.execute(view_script)
861 | conn.commit()
862 |
863 | return [TextContent(type="text", text="View created successfully")]
864 |
865 | async def _modify_view(self, view_script: str) -> List[TextContent]:
866 | """Modify an existing view."""
867 | if not view_script.strip().upper().startswith("ALTER VIEW"):
868 | raise ValueError("Only ALTER VIEW statements are allowed")
869 |
870 | with self._get_connection() as conn:
871 | cursor = conn.cursor()
872 | cursor.execute(view_script)
873 | conn.commit()
874 |
875 | return [TextContent(type="text", text="View modified successfully")]
876 |
877 | async def _delete_view(self, view_name: str) -> List[TextContent]:
878 | """Delete a view."""
879 | with self._get_connection() as conn:
880 | cursor = conn.cursor()
881 | cursor.execute(f"DROP VIEW IF EXISTS [{view_name}]")
882 | conn.commit()
883 |
884 | return [TextContent(type="text", text=f"View '{view_name}' deleted successfully")]
885 |
886 | async def _list_indexes(self, table_name: Optional[str] = None) -> List[TextContent]:
887 | """List all indexes in the database."""
888 | with self._get_connection() as conn:
889 | cursor = conn.cursor()
890 | if table_name:
891 | cursor.execute("""
892 | SELECT
893 | i.name AS IndexName,
894 | i.type_desc AS IndexType,
895 | i.is_unique AS IsUnique,
896 | i.is_primary_key AS IsPrimaryKey,
897 | i.is_disabled AS IsDisabled
898 | FROM sys.indexes i
899 | INNER JOIN sys.objects o ON i.object_id = o.object_id
900 | WHERE o.name = ? AND i.name IS NOT NULL
901 | ORDER BY i.name
902 | """, table_name)
903 |
904 | result = f"Indexes for table '{table_name}':\n"
905 | result += "Name | Type | Unique | Primary Key | Disabled\n"
906 | result += "-" * 60 + "\n"
907 | else:
908 | cursor.execute("""
909 | SELECT
910 | OBJECT_NAME(i.object_id) AS TableName,
911 | i.name AS IndexName,
912 | i.type_desc AS IndexType,
913 | i.is_unique AS IsUnique,
914 | i.is_primary_key AS IsPrimaryKey,
915 | i.is_disabled AS IsDisabled
916 | FROM sys.indexes i
917 | INNER JOIN sys.objects o ON i.object_id = o.object_id
918 | WHERE o.type = 'U' AND i.name IS NOT NULL
919 | ORDER BY OBJECT_NAME(i.object_id), i.name
920 | """)
921 |
922 | result = "Indexes in database:\n"
923 | result += "Table | Index Name | Type | Unique | Primary Key | Disabled\n"
924 | result += "-" * 80 + "\n"
925 |
926 | indexes = cursor.fetchall()
927 |
928 | if not indexes:
929 | return [TextContent(type="text", text="No indexes found")]
930 |
931 | for index in indexes:
932 | if table_name:
933 | result += f"{index[0]} | {index[1]} | {index[2]} | {index[3]} | {index[4]}\n"
934 | else:
935 | result += f"{index[0]} | {index[1]} | {index[2]} | {index[3]} | {index[4]} | {index[5]}\n"
936 |
937 | return [TextContent(type="text", text=result)]
938 |
939 | async def _describe_index(self, index_name: str, table_name: str) -> List[TextContent]:
940 | """Get detailed information about an index."""
941 | with self._get_connection() as conn:
942 | cursor = conn.cursor()
943 | cursor.execute("""
944 | SELECT
945 | i.name AS IndexName,
946 | i.type_desc AS IndexType,
947 | i.is_unique AS IsUnique,
948 | i.is_primary_key AS IsPrimaryKey,
949 | i.is_disabled AS IsDisabled
950 | FROM sys.indexes i
951 | INNER JOIN sys.objects o ON i.object_id = o.object_id
952 | WHERE i.name = ? AND o.name = ?
953 | """, index_name, table_name)
954 | index = cursor.fetchone()
955 |
956 | if not index:
957 | return [TextContent(type="text", text=f"Index '{index_name}' not found in table '{table_name}'")]
958 |
959 | result = f"Index '{index_name}' in table '{table_name}':\n"
960 | result += "Name | Type | Unique | Primary Key | Disabled\n"
961 | result += "-" * 60 + "\n"
962 | result += f"{index[0]} | {index[1]} | {index[2]} | {index[3]} | {index[4]}\n"
963 |
964 | return [TextContent(type="text", text=result)]
965 |
966 | async def _create_index(self, index_script: str) -> List[TextContent]:
967 | """Create a new index."""
968 | if not index_script.strip().upper().startswith("CREATE INDEX"):
969 | raise ValueError("Only CREATE INDEX statements are allowed")
970 |
971 | with self._get_connection() as conn:
972 | cursor = conn.cursor()
973 | cursor.execute(index_script)
974 | conn.commit()
975 |
976 | return [TextContent(type="text", text="Index created successfully")]
977 |
978 | async def _delete_index(self, index_name: str, table_name: str) -> List[TextContent]:
979 | """Delete an index."""
980 | with self._get_connection() as conn:
981 | cursor = conn.cursor()
982 | cursor.execute(f"DROP INDEX IF EXISTS [{index_name}] ON [{table_name}]")
983 | conn.commit()
984 |
985 | return [TextContent(type="text", text=f"Index '{index_name}' in table '{table_name}' deleted successfully")]
986 |
987 | async def _list_schemas(self) -> List[TextContent]:
988 | """List all schemas in the database."""
989 | with self._get_connection() as conn:
990 | cursor = conn.cursor()
991 | cursor.execute("""
992 | SELECT SCHEMA_NAME(schema_id) AS SchemaName
993 | FROM sys.schemas
994 | ORDER BY SCHEMA_NAME(schema_id)
995 | """)
996 | schemas = cursor.fetchall()
997 |
998 | if not schemas:
999 | return [TextContent(type="text", text="No schemas found")]
1000 |
1001 | result = "Schemas in database:\n"
1002 | for schema in schemas:
1003 | result += f"- {schema[0]}\n"
1004 |
1005 | return [TextContent(type="text", text=result)]
1006 |
1007 | async def _list_all_objects(self, schema_name: Optional[str] = None) -> List[TextContent]:
1008 | """List all database objects (tables, views, procedures, indexes) organized by schema."""
1009 | with self._get_connection() as conn:
1010 | cursor = conn.cursor()
1011 | if schema_name:
1012 | cursor.execute("""
1013 | SELECT
1014 | OBJECT_NAME(object_id) AS ObjectName,
1015 | type_desc AS ObjectType
1016 | FROM sys.objects
1017 | WHERE SCHEMA_NAME(schema_id) = ?
1018 | ORDER BY OBJECT_NAME(object_id)
1019 | """, schema_name)
1020 | else:
1021 | cursor.execute("""
1022 | SELECT
1023 | SCHEMA_NAME(schema_id) AS SchemaName,
1024 | OBJECT_NAME(object_id) AS ObjectName,
1025 | type_desc AS ObjectType
1026 | FROM sys.objects
1027 | ORDER BY SCHEMA_NAME(schema_id), OBJECT_NAME(object_id)
1028 | """)
1029 | objects = cursor.fetchall()
1030 |
1031 | if not objects:
1032 | return [TextContent(type="text", text="No objects found")]
1033 |
1034 | result = "Objects in database:\n"
1035 | for obj in objects:
1036 | result += f"- {obj[0]} | {obj[1]} | {obj[2]}\n"
1037 |
1038 | return [TextContent(type="text", text=result)]
1039 |
1040 | async def run(self):
1041 | """Run the MCP server."""
1042 | try:
1043 | logger.info("Starting MCP server...")
1044 | async with stdio_server() as (read_stream, write_stream):
1045 | logger.info("Server streams established")
1046 | await self.server.run(
1047 | read_stream,
1048 | write_stream,
1049 | InitializationOptions(
1050 | server_name="mssql-mcp-server",
1051 | server_version="1.0.0",
1052 | capabilities=self.server.get_capabilities(
1053 | notification_options=NotificationOptions(),
1054 | experimental_capabilities=None,
1055 | ),
1056 | ),
1057 | )
1058 | except Exception as e:
1059 | logger.error(f"Error in server run: {e}")
1060 | logger.error(f"Traceback: {traceback.format_exc()}")
1061 | raise
1062 |
1063 | async def main():
1064 | """Main entry point."""
1065 | try:
1066 | logger.info("Initializing MSSQL MCP Server...")
1067 | server = MSSQLServer()
1068 | logger.info("Server initialized, starting...")
1069 | await server.run()
1070 | except KeyboardInterrupt:
1071 | logger.info("Server stopped by user")
1072 | except Exception as e:
1073 | logger.error(f"Server error: {e}")
1074 | logger.error(f"Full traceback: {traceback.format_exc()}")
1075 | sys.exit(1)
1076 |
1077 | if __name__ == "__main__":
1078 | asyncio.run(main())
```