# Azure SQL MCP Server

A Model Context Protocol (MCP) server for connecting to Azure SQL databases. This server provides tools to query, explore, and interact with Azure SQL databases through Claude Desktop or any MCP-compatible client.

## Features

- Execute arbitrary SQL queries
- Browse database tables and schemas
- Get sample data from tables
- Configuration validation
- Azure Active Directory authentication via DefaultAzureCredential

## Prerequisites

Before starting, ensure you have these installed:

- **Python 3.11+** - Managed via [pyenv](https://github.com/pyenv/pyenv) (recommended) or system Python
- **Node.js 18+** - For MCP Inspector ([Download](https://nodejs.org/))
- **uv** - Fast Python package manager ([Install guide](https://github.com/astral-sh/uv))
- **ODBC Driver 17 for SQL Server** - ([Download](https://learn.microsoft.com/en-us/sql/connect/odbc/download-odbc-driver-for-sql-server))
- **Azure credentials** - Azure CLI, VS Code, or Managed Identity
- **npx** - Comes with Node.js (used to run MCP Inspector)

### Installing Prerequisites

#### Install Node.js and npx
```bash
# Windows: Download from https://nodejs.org/
# Or use winget
winget install OpenJS.NodeJS

# macOS
brew install node

# Linux
curl -fsSL https://deb.nodesource.com/setup_18.x | sudo -E bash -
sudo apt-get install -y nodejs

# Verify installation
node --version
npx --version
```

#### Install uv
```bash
# Windows (PowerShell)
powershell -c "irm https://astral.sh/uv/install.ps1 | iex"

# macOS/Linux
curl -LsSf https://astral.sh/uv/install.sh | sh

# Verify installation
uv --version
```

#### Install pyenv (Optional but Recommended)
```bash
# Windows
# Use pyenv-win: https://github.com/pyenv-win/pyenv-win

# macOS
brew install pyenv

# Linux
curl https://pyenv.run | bash
```

## Installation & Setup

### 1. Clone the Repository

```bash
git clone <your-repo-url>
cd mcp_component
```

### 2. Set Python Version (if using pyenv)

```bash
# Set global Python version
pyenv global 3.11.4

# Or set local version for this project only
pyenv local 3.11.4

# Verify
python --version
```

### 3. Create and Activate Virtual Environment

```bash
# Create virtual environment using uv
uv venv

# Activate the virtual environment
# Windows (PowerShell)
.venv\Scripts\activate

# Windows (CMD)
.venv\Scripts\activate.bat

# macOS/Linux
source .venv/bin/activate
```

### 4. Install Dependencies

```bash
# Option 1: Sync from pyproject.toml (recommended)
uv sync

# Option 2: Install from requirements.txt
uv pip install -r requirements.txt
uv add -r requirements.txt

# Verify installation
uv pip list
```

> **Pro Tip:** If you need to add new packages later:
> ```bash
> # Add a single package
> uv add package-name
> 
> # Add multiple packages from requirements.txt
> uv add -r requirements.txt
> ```

### 5. Configure Environment Variables

Create a `.env` file in the project root:

```bash
# Copy the example
cp .env.example .env

# Or create manually
```

Edit `.env` with your Azure SQL credentials:

```env
SERVER_NAME=your-server.database.windows.net
DATABASE=your-database-name
```

**Example:**
```env
SERVER_NAME=mycompany-sql.database.windows.net
DATABASE=Northwind
```

> **Note:** You can test the server without a real database! Leave the default values to test the MCP server functionality. The tools will indicate configuration is needed, but the server will run fine.

### 6. Authenticate with Azure

Make sure you're authenticated with Azure using one of these methods:

```bash
# Option 1: Azure CLI (recommended for local testing)
az login

# Option 2: Use environment variables
# Add to .env file:
# AZURE_TENANT_ID=your-tenant-id
# AZURE_CLIENT_ID=your-client-id
# AZURE_CLIENT_SECRET=your-client-secret

# Option 3: Use VS Code Azure Account extension
# (automatically works if signed in)
```

Verify Azure authentication:
```bash
az account show
```

## Testing with MCP Inspector

The MCP Inspector is an interactive tool for testing your MCP server before connecting it to Claude Desktop.

### Install and Run Inspector

```bash
# Run the inspector (npx will auto-install it if needed)
npx @modelcontextprotocol/inspector uv --directory . run server.py
```

**What happens:**
1. `npx` downloads and runs the MCP Inspector (first time only)
2. Opens automatically in your default browser at `http://localhost:5173`
3. Shows all available tools in the left sidebar
4. Displays request/response JSON for debugging

### Inspector Interface Overview

- **Left Panel:** List of available tools
- **Center Panel:** Tool parameters and execution
- **Right Panel:** JSON request/response output
- **Bottom:** Server logs and errors (stderr)

### Testing Workflow

1. **Start the Inspector** (command above)
2. **Check Configuration:**
   - Click on `check_database_config` tool
   - Click "Run Tool"
   - Verify your database settings

3. **Test Tools:**
   - Try `get_tables` to see available tables
   - Use `get_table_schema` with a table name
   - Execute `get_sample_data` to preview data

4. **Debug Issues:**
   - Check the stderr output for error messages
   - Verify your `.env` file is correct
   - Ensure Azure authentication is working

### Available Tools

#### 1. `check_database_config`
Check if your database credentials are properly configured.

**No parameters required**

**Example Response:**
```json
{
  "configured": true,
  "message": "Configuration OK",
  "server_name": "myserver.database.windows.net",
  "database": "Northwind"
}
```

#### 2. `execute_query`
Execute any SQL query against your database.

**Parameters:**
- `query` (string): SQL query to execute

**Example:**
```json
{
  "query": "SELECT TOP 5 * FROM Customers ORDER BY CompanyName"
}
```

**Response:**
```json
{
  "rows": 5,
  "columns": ["CustomerID", "CompanyName", "ContactName"],
  "data": [...]
}
```

#### 3. `get_tables`
Get a list of all tables in the database.

**No parameters required**

**Example Response:**
```json
{
  "tables": [
    {
      "schema": "dbo",
      "name": "Customers",
      "full_name": "dbo.Customers"
    },
    {
      "schema": "dbo",
      "name": "Orders",
      "full_name": "dbo.Orders"
    }
  ]
}
```

#### 4. `get_table_schema`
Get detailed schema information for a specific table.

**Parameters:**
- `table_name` (string): Name of the table (e.g., "Customers" or "dbo.Customers")

**Example:**
```json
{
  "table_name": "Customers"
}
```

**Response:**
```json
{
  "table": "Customers",
  "columns": [
    {
      "COLUMN_NAME": "CustomerID",
      "DATA_TYPE": "nchar",
      "IS_NULLABLE": "NO",
      "COLUMN_DEFAULT": null,
      "CHARACTER_MAXIMUM_LENGTH": 5
    }
  ]
}
```

#### 5. `get_sample_data`
Get sample rows from a table.

**Parameters:**
- `table_name` (string): Name of the table
- `limit` (integer, optional): Number of rows (default: 5, max: 100)

**Example:**
```json
{
  "table_name": "Customers",
  "limit": 10
}
```

**Response:**
```json
{
  "table": "Customers",
  "sample_rows": 10,
  "total_columns": 11,
  "columns": ["CustomerID", "CompanyName", ...],
  "data": [...]
}
```

## Using with Claude Desktop

Once you've tested with the Inspector, connect to Claude Desktop:

### 1. Locate Claude Desktop Config

**Windows:** `%APPDATA%\Claude\claude_desktop_config.json`  
**macOS:** `~/Library/Application Support/Claude/claude_desktop_config.json`  
**Linux:** `~/.config/Claude/claude_desktop_config.json`

### 2. Add Server Configuration

Edit or create the config file:

**Windows:**
```json
{
  "mcpServers": {
    "azure-sql": {
      "command": "uv",
      "args": [
        "--directory",
        "C:\\Users\\YourUsername\\path\\to\\mcp_component",
        "run",
        "server.py"
      ]
    }
  }
}
```

**macOS/Linux:**
```json
{
  "mcpServers": {
    "azure-sql": {
      "command": "uv",
      "args": [
        "--directory",
        "/Users/yourname/projects/mcp_component",
        "run",
        "server.py"
      ]
    }
  }
}
```

> **Important:** Replace the path with your actual absolute project path!

### 3. Restart Claude Desktop

After saving the config:
1. **Completely quit** Claude Desktop (not just close the window)
2. Restart the application
3. Look for the **🔌 plug icon** in the interface
4. Click it to verify "azure-sql" server is connected (green indicator)

### 4. Test with Claude

Try asking Claude:
- "Can you check if my database is configured?"
- "What tables are available in my database?"
- "Show me the schema for the Customers table"
- "Get me 5 sample rows from the Orders table"
- "Execute this query: SELECT COUNT(*) FROM Products"

## Project Structure

```
mcp_component/
├── .env                      # Environment variables (create this - not in git)
├── .env.example             # Example environment file
├── .gitignore               # Git ignore rules
├── .python-version          # Python version for pyenv
├── pyproject.toml           # Project metadata and dependencies
├── requirements.txt         # Python dependencies
├── uv.lock                  # Dependency lock file
├── server.py                # Main entry point wrapper
├── mcp_server/
│   ├── __init__.py
│   └── server.py           # MCP server implementation
├── README.md               # This file
└── .venv/                  # Virtual environment (created by uv venv)
```

## Troubleshooting

### "npx: command not found"

**Cause:** Node.js/npm is not installed or not in PATH

**Solution:**
```bash
# Verify Node.js installation
node --version
npm --version

# If not installed, install Node.js from https://nodejs.org/
# Or use a package manager (see Prerequisites section)
```

### "uv: command not found"

**Cause:** uv is not installed or not in PATH

**Solution:**
```bash
# Windows (PowerShell as Administrator)
powershell -c "irm https://astral.sh/uv/install.ps1 | iex"

# Then restart your terminal
# Verify
uv --version
```

### Inspector shows "error" notification

**Cause:** Server crashed during startup or has import errors

**Solution:** 
1. Check the **stderr output** in the Inspector console for detailed error messages
2. Common issues:
   - Python dependencies not installed: Run `uv sync`
   - Wrong Python version: Run `python --version` (need 3.11+)
   - Import errors: Make sure you're running from the project root
   - Azure auth issues: Run `az login`

### "Database not configured" error

**Expected behavior** if you haven't set up your `.env` file with real credentials. The server will still run—you just won't be able to query data until you configure it.

**Solution:** Update `.env` with your actual Azure SQL Server details:
```env
SERVER_NAME=your-actual-server.database.windows.net
DATABASE=your-actual-database-name
```

### Connection timeout or authentication errors

**Solutions:** 
1. **Firewall:** Verify your Azure SQL firewall allows your IP address
   ```bash
   # Check current IP
   curl https://api.ipify.org
   # Add it to Azure SQL firewall rules in Azure Portal
   ```

2. **Authentication:** Run `az login` to authenticate
   ```bash
   az login
   az account show
   ```

3. **Permissions:** Check that your Azure account has access to the database
   ```bash
   az sql db show --resource-group <rg-name> --server <server-name> --name <db-name>
   ```

4. **ODBC Driver:** Verify ODBC Driver 17 is installed
   ```powershell
   # Windows - Check installed ODBC drivers
   Get-OdbcDriver
   
   # Should show "ODBC Driver 17 for SQL Server"
   ```

### Tools not appearing in Claude Desktop

**Solutions:**
1. **Config path:** Double-check the config file is in the correct location:
   ```powershell
   # Windows - Open config directory
   explorer %APPDATA%\Claude
   
   # Verify claude_desktop_config.json exists
   ```

2. **Absolute path:** Ensure the project path in `args` is absolute and correct
   ```json
   // ❌ Wrong - relative path
   "C:\\mcp_component"
   
   // ✅ Correct - absolute path
   "C:\\Users\\YourName\\Projects\\mcp_component"
   ```

3. **Restart properly:** Completely quit Claude Desktop (not just minimize)
   - Windows: Right-click system tray icon → Exit
   - macOS: Cmd+Q or Claude → Quit Claude

4. **Check logs:** Look for errors in Claude Desktop logs:
   - **Windows:** `%APPDATA%\Claude\logs`
   - **macOS:** `~/Library/Logs/Claude`

5. **Test with Inspector first:** Verify the server works:
   ```bash
   npx @modelcontextprotocol/inspector uv --directory . run server.py
   ```

### "ODBC Driver not found"

**Solution:** Install ODBC Driver 17 for SQL Server:

**Windows:**
```powershell
# Download and run installer from:
# https://go.microsoft.com/fwlink/?linkid=2249004

# Verify installation
Get-OdbcDriver | Where-Object {$_.Name -like "*SQL Server*"}
```

**macOS:**
```bash
brew tap microsoft/mssql-release https://github.com/Microsoft/homebrew-mssql-release
brew update
brew install msodbcsql17

# Verify
odbcinst -q -d
```

**Linux (Ubuntu/Debian):**
```bash
curl https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add -
curl https://packages.microsoft.com/config/ubuntu/$(lsb_release -rs)/prod.list | sudo tee /etc/apt/sources.list.d/mssql-release.list
sudo apt-get update
sudo ACCEPT_EULA=Y apt-get install -y msodbcsql17

# Verify
odbcinst -q -d
```

### "Module not found" errors

**Solution:**
```bash
# Ensure virtual environment is activated
# Windows
.venv\Scripts\activate

# macOS/Linux
source .venv/bin/activate

# Reinstall dependencies
uv sync

# Or
uv pip install -r requirements.txt
```

## Development

### Adding Custom Tools

You can add your own tools to `mcp_server/server.py`:

```python
@mcp.tool()
async def my_custom_tool(param: str) -> str:
    """Description of what this tool does
    
    Args:
        param: Description of the parameter
    """
    try:
        engine = await get_azure_engine()
        
        # Your custom logic here
        query = text("SELECT * FROM MyTable WHERE column = :param")
        
        with engine.connect() as connection:
            result = connection.execute(query, {"param": param})
            data = [dict(row) for row in result]
        
        return json.dumps({"result": data}, indent=2)
        
    except Exception as e:
        return json.dumps({"error": str(e)}, indent=2)
```

**Best Practices:**
- Always wrap tools in try/except
- Return JSON strings (use `json.dumps()`)
- Use parameterized queries to prevent SQL injection
- Add descriptive docstrings for Claude to understand the tool
- Test new tools in the Inspector before using in Claude Desktop


### Updating Dependencies

```bash
# Add a new package
uv add package-name

# Update all packages
uv sync --upgrade

# Remove a package
uv remove package-name
```

## Authentication Details

This server uses `DefaultAzureCredential` from Azure Identity SDK, which tries multiple authentication methods in order:

1. **Environment variables** - `AZURE_TENANT_ID`, `AZURE_CLIENT_ID`, `AZURE_CLIENT_SECRET`
2. **Managed Identity** - Works automatically in Azure services (App Service, Functions, VMs)
3. **Azure CLI** - Uses `az login` credentials (✅ recommended for local development)
4. **Visual Studio Code** - Uses VS Code Azure Account extension
5. **Interactive browser** - Falls back to browser login

**For local testing (recommended):**
```bash
az login
```

**For production (recommended):**
- Use Managed Identity in Azure services
- Or use Service Principal with environment variables

## Contributing

Contributions are welcome! Please:

1. Fork the repository
2. Create a feature branch (`git checkout -b feature/XXX-XXX`)
3. Make your changes
4. Test with the MCP Inspector
5. Commit your changes (`git commit -m 'Add XXXX feature'`)
6. Push to the branch (`git push origin feature/XXX-XXX`)
7. Open a Pull Request

## Resources

- **MCP Documentation:** https://modelcontextprotocol.io
- **MCP Inspector:** https://github.com/modelcontextprotocol/inspector
- **Azure SQL Documentation:** https://learn.microsoft.com/en-us/azure/azure-sql/
- **uv Documentation:** https://github.com/astral-sh/uv
- **FastMCP Documentation:** https://github.com/jlowin/fastmcp