# Directory Structure
```
├── .gitignore
├── .python-version
├── pyproject.toml
├── README.md
├── sql_mcp.py
└── uv.lock
```
# Files
--------------------------------------------------------------------------------
/.python-version:
--------------------------------------------------------------------------------
```
1 | 3.13
2 |
```
--------------------------------------------------------------------------------
/.gitignore:
--------------------------------------------------------------------------------
```
1 | # Python-generated files
2 | __pycache__/
3 | *.py[oc]
4 | build/
5 | dist/
6 | wheels/
7 | *.egg-info
8 |
9 | # Virtual environments
10 | .venv
11 |
```
--------------------------------------------------------------------------------
/README.md:
--------------------------------------------------------------------------------
```markdown
1 | # MCP SQL Server
2 |
3 | A FastMCP server that provides SQL database interaction tools via a conversational AI interface.
4 |
5 | ## Overview
6 |
7 | This project creates a server that exposes MS SQL Server operations through a conversational AI interface. It uses the FastMCP framework to provide tools for querying and manipulating SQL data, allowing users to interact with databases using natural language.
8 |
9 | ## Features
10 |
11 | - Execute SQL queries and view results
12 | - List available tables in the database
13 | - Describe table structure with column information
14 | - Execute non-query operations (INSERT, UPDATE, DELETE)
15 | - List available ODBC drivers on the system
16 | - View database information and server details
17 |
18 | ## Requirements
19 |
20 | - Python 3.7+
21 | - pyodbc
22 | - asyncio
23 | - FastMCP framework
24 | - Microsoft SQL Server
25 | - ODBC Driver 17 for SQL Server
26 |
27 | ## Installation
28 |
29 | 1. Install Python dependencies:
30 |
31 | ```bash
32 | pip install pyodbc asyncio fastmcp
33 | ```
34 |
35 | 2. Ensure you have Microsoft SQL Server installed and the ODBC Driver 17 for SQL Server.
36 |
37 | 3. Configure the connection settings in the script:
38 |
39 | ```python
40 | # Connection parameters
41 | SERVER = "server\\instance" # Change to your SQL Server instance
42 | DATABASE = "db_name" # Change to your database name
43 | ```
44 |
45 | ## Usage
46 |
47 | Run the server:
48 |
49 | ```bash
50 | python mcp_sql_server.py
51 | ```
52 |
53 | The server will initialize and establish a connection to the specified SQL Server database.
54 |
55 | ## Available Tools
56 |
57 | ### query_sql
58 |
59 | Execute a SQL query and return the results.
60 |
61 | ```
62 | query_sql(query: str = None) -> str
63 | ```
64 |
65 | - If no query is provided, it defaults to `SELECT * FROM [dbo].[Table_1]`
66 | - Returns query results as a formatted string
67 |
68 | ### list_tables
69 |
70 | List all tables available in the database.
71 |
72 | ```
73 | list_tables() -> str
74 | ```
75 |
76 | - Returns a list of table names as a string
77 |
78 | ### describe_table
79 |
80 | Get the structure of a specific table.
81 |
82 | ```
83 | describe_table(table_name: str) -> str
84 | ```
85 |
86 | - `table_name`: Name of the table to describe
87 | - Returns column information including names and data types
88 |
89 | ### execute_nonquery
90 |
91 | Execute INSERT, UPDATE, DELETE or other non-query SQL statements.
92 |
93 | ```
94 | execute_nonquery(sql: str) -> str
95 | ```
96 |
97 | - `sql`: The SQL statement to execute
98 | - Returns operation results, including number of affected rows
99 | - Automatically handles transactions (commit/rollback)
100 |
101 | ### list_odbc_drivers
102 |
103 | List all available ODBC drivers on the system.
104 |
105 | ```
106 | list_odbc_drivers() -> str
107 | ```
108 |
109 | - Returns a comma-separated list of installed ODBC drivers
110 |
111 | ### database_info
112 |
113 | Get general information about the connected database.
114 |
115 | ```
116 | database_info() -> str
117 | ```
118 |
119 | - Returns server name, database name, SQL Server version, current server time, and table count
120 |
121 | ## Architecture
122 |
123 | The server uses an asynchronous architecture to avoid blocking operations:
124 |
125 | 1. **Lifecycle Management**: The `app_lifespan` context manager handles database connection setup and teardown.
126 |
127 | 2. **Non-blocking Operations**: Database operations run in a separate thread using `asyncio.get_event_loop().run_in_executor()` to prevent blocking the main event loop.
128 |
129 | 3. **Error Handling**: All operations include comprehensive error handling with useful error messages.
130 |
131 | ## Error Handling
132 |
133 | The server handles various error conditions:
134 |
135 | - Database connection failures
136 | - SQL query syntax errors
137 | - Table not found errors
138 | - Permission-related issues
139 |
140 | All errors are logged and appropriate error messages are returned to the client.
141 |
142 | ## Customization
143 |
144 | To add new database tools or modify existing ones, follow the pattern used in the existing tools:
145 |
146 | ```python
147 | @mcp.tool()
148 | async def your_new_tool(ctx: Context, param1: str) -> str:
149 | """Documentation for your tool"""
150 | try:
151 | conn = ctx.request_context.lifespan_context["conn"]
152 |
153 | if conn is None:
154 | return "Database connection is not available."
155 |
156 | def your_db_operation():
157 | # Your database operations here
158 | pass
159 |
160 | loop = asyncio.get_event_loop()
161 | result = await loop.run_in_executor(None, your_db_operation)
162 |
163 | # Process and return results
164 | return "Your result"
165 | except Exception as e:
166 | return f"Error: {str(e)}"
167 | ```
168 |
169 | ## Security Considerations
170 |
171 | - The server uses Windows Authentication ("Trusted_Connection=yes")
172 | - Consider implementing input validation for SQL queries to prevent SQL injection
173 | - Restrict database user permissions based on the principle of least privilege
174 |
175 | ## Troubleshooting
176 |
177 | Common issues:
178 |
179 | 1. **Connection errors**: Verify the SQL Server instance name and ensure it's running
180 | 2. **ODBC driver errors**: Confirm ODBC Driver 17 for SQL Server is installed
181 | 3. **Permission errors**: Check that the Windows user running the application has appropriate SQL Server permissions
182 |
183 | ## License
184 |
185 | [Your License Information]
186 |
187 | ## Contact
188 |
189 | [Your Contact Information]
190 |
```
--------------------------------------------------------------------------------
/pyproject.toml:
--------------------------------------------------------------------------------
```toml
1 | [project]
2 | name = "sql-mcp-server"
3 | version = "0.1.0"
4 | description = "Add your description here"
5 | readme = "README.md"
6 | requires-python = ">=3.13"
7 | dependencies = [
8 | "mcp[cli]>=1.5.0",
9 | "pyodbc",
10 | ]
11 |
```
--------------------------------------------------------------------------------
/sql_mcp.py:
--------------------------------------------------------------------------------
```python
1 | from contextlib import asynccontextmanager
2 | from collections.abc import AsyncIterator
3 | import logging
4 | import sys
5 | import pyodbc
6 | import asyncio
7 | from mcp.server.fastmcp import Context, FastMCP
8 |
9 | # Set up logging
10 | logging.basicConfig(level=logging.DEBUG)
11 | logger = logging.getLogger("server_module")
12 |
13 | # Connection parameters
14 | SERVER = "LEGION\\SQLEXPRESS"
15 | DATABASE = "test"
16 |
17 | @asynccontextmanager
18 | async def app_lifespan(server: FastMCP) -> AsyncIterator[dict]:
19 | """Manage application lifecycle with type-safe context"""
20 | logger.debug("Initializing database connection")
21 | conn = None
22 |
23 | try:
24 | # Connect using a loop.run_in_executor to avoid blocking
25 | def connect_db():
26 | connection_string = (
27 | f"DRIVER={{ODBC Driver 17 for SQL Server}};"
28 | f"SERVER={SERVER};"
29 | f"DATABASE={DATABASE};"
30 | f"Trusted_Connection=yes;"
31 | )
32 | logger.debug(f"Connection string: {connection_string}")
33 | return pyodbc.connect(connection_string)
34 |
35 | loop = asyncio.get_event_loop()
36 | conn = await loop.run_in_executor(None, connect_db)
37 | logger.debug("Database connection established successfully")
38 |
39 | # Yield a dictionary instead of a dataclass to match example
40 | yield {"conn": conn}
41 | except Exception as e:
42 | logger.error(f"Database connection error: {type(e).__name__}: {str(e)}", exc_info=True)
43 | # Continue without database but with empty dict
44 | yield {"conn": None}
45 | finally:
46 | if conn:
47 | logger.debug("Closing database connection")
48 | await asyncio.get_event_loop().run_in_executor(None, conn.close)
49 |
50 | # Create an MCP server with the lifespan
51 | mcp = FastMCP("My MS SQL Integrated App", lifespan=app_lifespan)
52 |
53 | @mcp.tool()
54 | async def query_sql(ctx: Context, query: str = None) -> str:
55 | """
56 | Tool to query the SQL database with a custom query.
57 |
58 | Args:
59 | query: The SQL query to execute. If not provided, will run a default query.
60 |
61 | Returns:
62 | The query results as a string.
63 | """
64 | try:
65 | # Access the connection using dictionary access
66 | conn = ctx.request_context.lifespan_context["conn"]
67 |
68 | if conn is None:
69 | return "Database connection is not available. Check server logs for details."
70 |
71 | # Use default query if none provided
72 | if not query:
73 | query = "SELECT * FROM [dbo].[Table_1]"
74 |
75 | logger.debug(f"Executing query: {query}")
76 |
77 | # Execute query in a non-blocking way
78 | def run_query():
79 | cursor = conn.cursor()
80 | try:
81 | cursor.execute(query)
82 | if cursor.description: # Check if the query returns results
83 | columns = [column[0] for column in cursor.description]
84 | results = []
85 | for row in cursor.fetchall():
86 | results.append(dict(zip(columns, row)))
87 | return {"success": True, "results": results, "rowCount": len(results)}
88 | else:
89 | # For non-SELECT queries (INSERT, UPDATE, etc.)
90 | return {"success": True, "rowCount": cursor.rowcount, "message": f"Query affected {cursor.rowcount} rows"}
91 | except Exception as e:
92 | return {"success": False, "error": str(e)}
93 | finally:
94 | cursor.close()
95 |
96 | loop = asyncio.get_event_loop()
97 | result = await loop.run_in_executor(None, run_query)
98 |
99 | if result["success"]:
100 | if "results" in result:
101 | return f"Query results: {result['results']}"
102 | else:
103 | return result["message"]
104 | else:
105 | return f"Query error: {result['error']}"
106 | except Exception as e:
107 | logger.error(f"Query execution error: {type(e).__name__}: {str(e)}")
108 | return f"Error: {str(e)}"
109 |
110 | @mcp.tool()
111 | async def list_tables(ctx: Context) -> str:
112 | """List all tables in the database that can be queried."""
113 | try:
114 | conn = ctx.request_context.lifespan_context["conn"]
115 |
116 | if conn is None:
117 | return "Database connection is not available."
118 |
119 | def get_tables():
120 | cursor = conn.cursor()
121 | cursor.execute("SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE'")
122 | tables = [row[0] for row in cursor.fetchall()]
123 | cursor.close()
124 | return tables
125 |
126 | loop = asyncio.get_event_loop()
127 | tables = await loop.run_in_executor(None, get_tables)
128 |
129 | return f"Available tables: {tables}"
130 | except Exception as e:
131 | return f"Error listing tables: {str(e)}"
132 |
133 | @mcp.tool()
134 | async def describe_table(ctx: Context, table_name: str) -> str:
135 | """
136 | Get the structure of a specific table.
137 |
138 | Args:
139 | table_name: Name of the table to describe
140 |
141 | Returns:
142 | Column information for the specified table
143 | """
144 | try:
145 | conn = ctx.request_context.lifespan_context["conn"]
146 |
147 | if conn is None:
148 | return "Database connection is not available."
149 |
150 | def get_structure():
151 | cursor = conn.cursor()
152 | cursor.execute(f"SELECT COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '{table_name}'")
153 | columns = []
154 | for row in cursor.fetchall():
155 | col_name, data_type, max_length = row
156 | if max_length:
157 | columns.append(f"{col_name} ({data_type}({max_length}))")
158 | else:
159 | columns.append(f"{col_name} ({data_type})")
160 | cursor.close()
161 | return columns
162 |
163 | loop = asyncio.get_event_loop()
164 | structure = await loop.run_in_executor(None, get_structure)
165 |
166 | if structure:
167 | return f"Structure of table '{table_name}':\n" + "\n".join(structure)
168 | else:
169 | return f"Table '{table_name}' not found or has no columns."
170 | except Exception as e:
171 | return f"Error describing table: {str(e)}"
172 |
173 | @mcp.tool()
174 | async def execute_nonquery(ctx: Context, sql: str) -> str:
175 | """
176 | Execute a non-query SQL statement (INSERT, UPDATE, DELETE, etc.).
177 |
178 | Args:
179 | sql: The SQL statement to execute
180 |
181 | Returns:
182 | Result of the operation
183 | """
184 | try:
185 | conn = ctx.request_context.lifespan_context["conn"]
186 |
187 | if conn is None:
188 | return "Database connection is not available."
189 |
190 | def run_nonquery():
191 | try:
192 | cursor = conn.cursor()
193 | cursor.execute(sql)
194 | row_count = cursor.rowcount
195 | # Commit changes
196 | conn.commit()
197 | cursor.close()
198 | return {"success": True, "rowCount": row_count}
199 | except Exception as e:
200 | # Rollback in case of error
201 | conn.rollback()
202 | return {"success": False, "error": str(e)}
203 |
204 | loop = asyncio.get_event_loop()
205 | result = await loop.run_in_executor(None, run_nonquery)
206 |
207 | if result["success"]:
208 | return f"Operation successful. Rows affected: {result['rowCount']}"
209 | else:
210 | return f"Operation failed: {result['error']}"
211 | except Exception as e:
212 | return f"Error executing SQL: {str(e)}"
213 |
214 | @mcp.tool()
215 | async def list_odbc_drivers(ctx: Context) -> str:
216 | """List available ODBC drivers on the system"""
217 | try:
218 | def get_drivers():
219 | return pyodbc.drivers()
220 |
221 | drivers = await asyncio.get_event_loop().run_in_executor(None, get_drivers)
222 | return f"Available ODBC drivers: {', '.join(drivers)}"
223 | except Exception as e:
224 | return f"Error listing drivers: {str(e)}"
225 |
226 | @mcp.tool()
227 | async def database_info(ctx: Context) -> str:
228 | """Get general information about the connected database"""
229 | try:
230 | conn = ctx.request_context.lifespan_context["conn"]
231 |
232 | if conn is None:
233 | return "Database connection is not available."
234 |
235 | def get_info():
236 | cursor = conn.cursor()
237 |
238 | # Get SQL Server version
239 | cursor.execute("SELECT @@VERSION")
240 | version = cursor.fetchone()[0]
241 |
242 | # Get database name and size
243 | cursor.execute("""
244 | SELECT
245 | DB_NAME() AS DatabaseName,
246 | CONVERT(VARCHAR(50), GETDATE(), 120) AS CurrentDateTime,
247 | (SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE') AS TableCount
248 | """)
249 | db_info = cursor.fetchone()
250 |
251 | cursor.close()
252 | return {
253 | "version": version,
254 | "database": db_info[0],
255 | "current_time": db_info[1],
256 | "table_count": db_info[2]
257 | }
258 |
259 | loop = asyncio.get_event_loop()
260 | info = await loop.run_in_executor(None, get_info)
261 |
262 | return (
263 | f"Database Information:\n"
264 | f"Server: {SERVER}\n"
265 | f"Database: {info['database']}\n"
266 | f"Server Version: {info['version'].split('\\n')[0]}\n"
267 | f"Current Server Time: {info['current_time']}\n"
268 | f"Number of Tables: {info['table_count']}"
269 | )
270 | except Exception as e:
271 | return f"Error getting database info: {str(e)}"
272 |
273 | # Run the server
274 | if __name__ == "__main__":
275 | try:
276 | logger.info("Starting MCP server")
277 | mcp.run()
278 | except Exception as e:
279 | logger.critical(f"Server startup failed: {e}", exc_info=True)
280 | sys.exit(1)
```