Enterprise-Grade Database MCP Server

This project provides a production-quality template for building a secure Micro-Capability Platform (MCP) server using the FastMCP framework. It demonstrates best practices for creating an AI-accessible gateway to corporate databases (Oracle, PostgreSQL, etc.) with robust authentication and a clean architectural design.

Architecture Overview

This template implements several critical architectural patterns:

• Middleware-Based Authentication: Bearer token authentication is handled at the middleware layer, completely decoupled from business logic.
• Database Abstraction Layer: Tools interact with stub functions rather than directly with the database, enabling clean separation of concerns and simplified testing.
• Hybrid Tool Design:
  • Low-Level Tools: Generic SQL execution with schema context for complex queries
  • High-Level Tools: Purpose-built interfaces for common operations with reduced complexity
• Schema Validation: Pydantic-based validation ensures data integrity and provides rich metadata for all tool parameters.

Prerequisites

• Ubuntu 22.04 LTS (or compatible Linux distribution)
• Python 3.10 or higher
• Network access for package installation

Section 1: Ubuntu Server Deployment

Step 1: System Preparation

Update the system and install required packages:

sudo apt update
sudo apt install python3-pip python3.10-venv -y

Step 2: Project Setup

Create Project Directory

mkdir -p ~/mcp-server
cd ~/mcp-server

Copy Server Code

Place the server.py file in the project directory.

Create Python Virtual Environment

python3 -m venv venv

Activate Virtual Environment

source venv/bin/activate

Create Requirements File

echo "fastmcp[server]" > requirements.txt

Install Dependencies

pip install -r requirements.txt

Step 3: Authentication Configuration

Create the bearer token file:

# Generate a secure token (example using openssl)
openssl rand -hex 32 > mcp_secret.token

# Or manually create with your own token
echo "YOUR_SECURE_TOKEN_HERE" > mcp_secret.token

Security Note: Ensure this file has restricted permissions:

chmod 600 mcp_secret.token

Step 4: Initial Testing

Run the server directly to verify functionality:

python server.py

Step 5: Production Deployment with Systemd

For production environments, configure the server as a systemd service to ensure automatic startup and restart on failure.

Create Service File

sudo nano /etc/systemd/system/mcp-server.service

Add Service Configuration

Replace /home/ubuntu/mcp-server with your actual project path:

Description=Enterprise Database MCP Server
After=network.target

[Service]
Type=simple
User=ubuntu
Group=ubuntu
WorkingDirectory=/home/ubuntu/mcp-server
ExecStart=/home/ubuntu/mcp-server/venv/bin/python /home/ubuntu/mcp-server/server.py
Restart=always
RestartSec=3
StandardOutput=journal
StandardError=journal

[Install]
WantedBy=multi-user.target```

Enable and Start Service

```bash
sudo systemctl daemon-reload
sudo systemctl enable mcp-server.service
sudo systemctl start mcp-server.service

erify Service Status

bash

sudo systemctl status mcp-server.service

bash

sudo systemctl status mcp-server.service Expected output should show active (running) in green.

View Service Logs

bash

sudo journalctl -u mcp-server.service -f Section 2: Verification Testing Use the following curl commands to verify server functionality. Replace YOUR_SERVER_IP with your server's IP address and YOUR_SECURE_TOKEN_HERE with the token from mcp_secret.token.

Test 1: Authentication Failure (Expected) This test verifies that requests without proper authentication are rejected:

bash

curl -X POST http://YOUR_SERVER_IP:9009/mcp
-H "Content-Type: application/json"
-d '{ "jsonrpc": "2.0", "id": 1, "method": "tools/call", "params": { "name": "get_employee_details", "arguments": { "employee_id": 101 } } }' Expected Response:

json

{"detail":"Authorization header is missing."} Test 2: High-Level Tool - Get Employee Details This test calls a specific, purpose-built tool:

bash

curl -X POST http://YOUR_SERVER_IP:9009/mcp
-H "Content-Type: application/json"
-H "Authorization: Bearer YOUR_SECURE_TOKEN_HERE"
-d '{ "jsonrpc": "2.0", "id": 1, "method": "tools/call", "params": { "name": "get_employee_details", "arguments": { "employee_id": 101 } } }' Expected Response:

json

{ "jsonrpc": "2.0", "id": 1, "result": { "content": [ { "type": "text", "text": "{"status": "success", "employee_data": {"EMPLOYEE_ID": 101, "FIRST_NAME": "John", "LAST_NAME": "Doe", "SALARY": 95000, "DEPARTMENT_ID": 2}}" } ] } } Test 3: Low-Level Tool - Execute SQL Query This test demonstrates the generic SQL execution capability:

bash

curl -X POST http://YOUR_SERVER_IP:9009/mcp
-H "Content-Type: application/json"
-H "Authorization: Bearer YOUR_SECURE_TOKEN_HERE"
-d '{ "jsonrpc": "2.0", "id": 1, "method": "tools/call", "params": { "name": "execute_sql_query", "arguments": { "query": "SELECT DEPARTMENT_ID, DEPARTMENT_NAME FROM DEPARTMENTS" } } }' Expected Response:

json

{ "jsonrpc": "2.0", "id": 1, "result": { "content": [ { "type": "text", "text": "{"status": "success", "row_count": 2, "results": [{"DEPARTMENT_ID": 1, "DEPARTMENT_NAME": "Engineering"}, {"DEPARTMENT_ID": 2, "DEPARTMENT_NAME": "Sales"}]}" } ] } } Test 4: Security Validation Verify that non-SELECT queries are rejected:

curl -X POST http://YOUR_SERVER_IP:9009/mcp
-H "Content-Type: application/json"
-H "Authorization: Bearer YOUR_SECURE_TOKEN_HERE"
-d '{ "jsonrpc": "2.0", "id": 1, "method": "tools/call", "params": { "name": "execute_sql_query", "arguments": { "query": "DELETE FROM EMPLOYEES WHERE EMPLOYEE_ID = 101" } } }' Expected Response:

json

{ "jsonrpc": "2.0", "id": 1, "result": { "content": [ { "type": "text", "text": "{"status": "error", "message": "Security Violation: Only SELECT queries are permitted."}" } ] } } Section 3: Production Database Integration The provided template uses stub functions for database interaction. To integrate with a real database:

For Oracle Database Install Oracle Client Libraries

Follow Oracle's official documentation for installing Instant Client on your platform.

Install Python Driver

bash

pip install oracledb Update _execute_readonly_query Function

Replace the stub implementation with:

python

import oracledb

def _execute_readonly_query(sql: str, params: dict = None) -> List[Dict[str, Any]]: """Executes a read-only SQL query against the Oracle database.""" if not sql.strip().upper().startswith("SELECT"): raise ValueError("Security Violation: Only SELECT queries are permitted.")

connection = oracledb.connect(
    user=os.getenv("DB_USER"),
    password=os.getenv("DB_PASSWORD"),
    dsn=os.getenv("DB_DSN")
)

try:
    cursor = connection.cursor()
    cursor.execute(sql, params or {})
    columns = [col[0] for col in cursor.description]
    results = [dict(zip(columns, row)) for row in cursor.fetchall()]
    return results
finally:
    connection.close()

For PostgreSQL Install Python Driver

bash

pip install psycopg2-binary Update _execute_readonly_query Function

Replace the stub implementation with:

python

import psycopg2 import psycopg2.extras

def _execute_readonly_query(sql: str, params: dict = None) -> List[Dict[str, Any]]: """Executes a read-only SQL query against the PostgreSQL database.""" if not sql.strip().upper().startswith("SELECT"): raise ValueError("Security Violation: Only SELECT queries are permitted.")

connection = psycopg2.connect(
    host=os.getenv("DB_HOST"),
    database=os.getenv("DB_NAME"),
    user=os.getenv("DB_USER"),
    password=os.getenv("DB_PASSWORD")
)

try:
    cursor = connection.cursor(cursor_factory=psycopg2.extras.RealDictCursor)
    cursor.execute(sql, params or {})
    results = [dict(row) for row in cursor.fetchall()]
    return results
finally:
    connection.close()

Section 4: Simtheory Platform Deployment [Reserved for Simtheory-specific deployment instructions]

Troubleshooting Service Fails to Start Check the service logs:

bash

sudo journalctl -u mcp-server.service -n 50 Common issues:

Missing mcp_secret.token file Incorrect file permissions Python virtual environment not properly configured Authentication Errors Verify the token file exists and contains the correct token:

bash

cat mcp_secret.token Ensure the token matches exactly what you're sending in the Authorization header.

Port Already in Use Check if port 9009 is already occupied:

sudo lsof -i :9009 To use a different port, modify the last line in server.py:

uvicorn.run(app, host="0.0.0.0", port=YOUR_PORT_NUMBER) Security Considerations Token Management: Store bearer tokens securely. Never commit mcp_secret.token to version control. Database Credentials: Use environment variables for database credentials. Never hardcode them in source files. Read-Only Access: Configure a dedicated read-only database user for the MCP server. Network Security: Use firewall rules to restrict access to port 9009 to authorized clients only. HTTPS: In production, place the MCP server behind a reverse proxy (nginx, Apache) with SSL/TLS termination.