Skip to content

what-is-mcp.md

Latest commit

 

History

History
254 lines (185 loc) · 10.7 KB

what-is-mcp.md

File metadata and controls

254 lines (185 loc) · 10.7 KB
Raw

Understanding MCP and pymssql-mcp

This guide explains what MCP is, how it works, and how pymssql-mcp connects your Microsoft SQL Server database to AI assistants like Claude.

What is MCP?

MCP (Model Context Protocol) is an open standard created by Anthropic that allows AI assistants to connect to external tools and data sources. Think of it as a "plugin system" for AI.

The Problem MCP Solves

Without MCP, AI assistants like Claude can only:

  • Answer questions from their training data
  • Help with text in the conversation
  • Have no access to your specific systems or data

With MCP, AI assistants can:

  • Connect to your databases and query real data
  • Read and write files on your system
  • Call APIs and web services
  • Execute commands and run programs
  • Remember information across conversations

How MCP Works

┌─────────────────┐     ┌─────────────────┐     ┌─────────────────┐
│                 │     │                 │     │                 │
│  Claude Desktop │◄───►│   MCP Server    │◄───►│   Your System   │
│  (AI Assistant) │     │   (pymssql-mcp) │     │   (SQL Server)  │
│                 │     │                 │     │                 │
└─────────────────┘     └─────────────────┘     └─────────────────┘
     You talk to             Translates            Your actual
     Claude here             requests              data lives here
  1. You ask Claude a question like "Show me all customers in California"
  2. Claude recognizes it needs database access and calls the MCP server
  3. The MCP server (pymssql-mcp) translates this into a SQL query
  4. SQL Server returns the data
  5. pymssql-mcp formats the results and sends them back to Claude
  6. Claude presents the information to you in a readable format

MCP Components

Component What It Is Example
MCP Host The AI application that uses MCP Claude Desktop, Claude Code
MCP Server A program that provides tools/data pymssql-mcp (this project)
Tools Actions the AI can perform execute_query, insert_row
Resources Data the AI can access Database knowledge, syntax help

What is pymssql-mcp?

pymssql-mcp is an MCP server specifically for Microsoft SQL Server databases.

It allows Claude to:

  • Query your SQL Server database using natural language
  • Read and write rows in tables
  • Explore table structures and schemas
  • Execute stored procedures
  • Learn and remember information about your database

Why Use pymssql-mcp?

Without pymssql-mcp:

You: "How many customers do we have in California?" Claude: "I don't have access to your database. You would need to run a query like SELECT COUNT(*) FROM Customers WHERE State = 'CA'..."

With pymssql-mcp:

You: "How many customers do we have in California?" Claude: "You have 1,247 customers in California. The largest by revenue is Acme Corp (CustomerID: 12345)."

Claude can actually connect to your database, run the query, and give you real answers.

Where Does pymssql-mcp Run?

pymssql-mcp runs as a local process on your computer, alongside Claude Desktop.

Local Mode (Default)

Your Computer
┌────────────────────────────────────────────────┐
│                                                │
│  ┌──────────────┐      ┌──────────────┐       │
│  │    Claude    │      │  pymssql-mcp │       │
│  │   Desktop    │◄────►│   (local)    │       │
│  └──────────────┘      └──────┬───────┘       │
│                               │               │
└───────────────────────────────┼───────────────┘
                                │
                                ▼
                    ┌──────────────────────┐
                    │     SQL Server       │
                    │       Server         │
                    └──────────────────────┘
                    (Can be local or remote)
  • pymssql-mcp runs on your machine
  • Credentials stay on your machine
  • Connection goes directly to your database server
  • Only you can access this instance

Centralized Mode (Team Deployment)

For teams, pymssql-mcp can run as an HTTP server:

┌─────────────────┐     ┌─────────────────┐     ┌─────────────────┐
│ User 1's Claude │     │                 │     │                 │
│     Desktop     │────►│                 │     │                 │
├─────────────────┤     │   pymssql-mcp   │     │   SQL Server    │
│ User 2's Claude │────►│   HTTP Server   │────►│     Server      │
│     Desktop     │     │                 │     │                 │
├─────────────────┤     │                 │     │                 │
│ User 3's Claude │────►│                 │     │                 │
│     Desktop     │     │                 │     │                 │
└─────────────────┘     └─────────────────┘     └─────────────────┘
  • Single pymssql-mcp instance serves multiple users
  • Shared knowledge base
  • Centralized credential management

How Claude Uses pymssql-mcp

When you install pymssql-mcp and configure Claude Desktop, Claude gains new abilities called tools. Here's what happens:

1. Tool Discovery

When Claude Desktop starts, it discovers what tools pymssql-mcp provides:

  • connect - Connect to the database
  • execute_query - Run SQL queries
  • read_rows - Read table data
  • describe_table - Explore table structure
  • ...and 20+ more tools

2. Natural Language Understanding

When you ask a question, Claude decides which tools to use:

You: "What columns are in the Customers table?"

Claude thinks: "The user wants to know about table structure. I should use the describe_table tool."

3. Tool Execution

Claude calls the tool with appropriate parameters:

describe_table(table="Customers")

4. Response Formatting

Claude receives the raw data and presents it clearly:

Claude: "The Customers table has 8 columns:

  • CustomerID (int) - Primary key
  • Name (nvarchar) - Customer name
  • Email (varchar) - Email address, nullable ..."

SQL Server Concepts

pymssql-mcp works with Microsoft SQL Server databases. Here are some key concepts:

Concept Description Example
Database Container for tables and objects SalesDB, HRDatabase
Schema Namespace within a database dbo, Sales, HR
Table Collection of rows with defined columns dbo.Customers
View Virtual table based on a query dbo.vw_ActiveCustomers
Stored Procedure Pre-compiled SQL statements dbo.sp_GetCustomerOrders

Table Naming

Tables in SQL Server are referenced as schema.table:

  • dbo.Customers - Customers table in dbo schema
  • Sales.Orders - Orders table in Sales schema
  • Customers - Defaults to dbo.Customers

Security Considerations

What pymssql-mcp Can Access

pymssql-mcp connects to your database with the credentials you provide. It can:

  • Read any table/view the user account can read
  • Write to tables (unless read-only mode is enabled)
  • Execute stored procedures
  • Switch between databases on the server

Built-in Safety Features

  • Read-only mode - Prevents all write operations (INSERT, UPDATE, DELETE)
  • Command blocking - Dangerous commands like DROP, TRUNCATE are blocked
  • Query validation - Only safe query patterns are allowed
  • Result limiting - Large queries are automatically limited
  • Database blocklist - Certain databases can be hidden entirely
  • Schema restrictions - Limit access to specific schemas only

Best Practices

  1. Use read-only mode when exploring or for most users
  2. Create a dedicated database user with minimal permissions
  3. Review the blocked commands and add more if needed
  4. Keep credentials secure - never commit them to source control
  5. Use the database blocklist to hide sensitive databases

Common Questions

Do I need to learn SQL?

No! That's the point of pymssql-mcp. You can ask questions in plain English:

  • "Show me all open orders" instead of SELECT * FROM Orders WHERE Status = 'Open'
  • "Count customers by state" instead of SELECT State, COUNT(*) FROM Customers GROUP BY State

Claude will generate the appropriate queries.

Does Claude understand my specific database?

Initially, no. But pymssql-mcp has a knowledge persistence feature. As Claude explores your database, it can save what it learns:

  • "dbo.Customers is the customer master table"
  • "StatusCode 1=Active, 2=Inactive, 3=Suspended"
  • "OrderTotal is calculated from LineItems"

This knowledge persists across sessions, so Claude gets smarter over time.

Can multiple people use the same pymssql-mcp?

Yes, with HTTP mode. One pymssql-mcp server can serve multiple Claude Desktop users, and they can share a common knowledge base.

Is my data sent to Anthropic?

Your database queries and results pass through Claude (Anthropic's AI), similar to if you typed the data into a chat. Review Anthropic's privacy policy if this is a concern for your data.

The pymssql-mcp server itself runs locally and doesn't send data anywhere except to Claude and your database.

What SQL Server versions are supported?

pymssql-mcp uses the pymssql package which supports:

  • SQL Server 2012 and later
  • Azure SQL Database
  • Azure SQL Managed Instance

Next Steps

Ready to get started?

  1. Installation Guide - Install pymssql-mcp and configure Claude Desktop
  2. Quickstart Guide - Get running in 10 minutes
  3. Configuration Reference - All configuration options
  4. Usage Examples - See what you can do
  5. Tools Reference - Detailed tool documentation