Mssql MCP

Microsoft SQL Server MCP Skill

Query and manage SQL Server databases through natural language interface with built-in safety features.

Configuration

Required Environment Variables

SERVER_NAME=myserver.database.windows.net
DATABASE_NAME=MyDatabase

Optional Environment Variables

READONLY=true                  # Enable read-only mode
CONNECTION_TIMEOUT=30          # Connection timeout (seconds)
TRUST_SERVER_CERTIFICATE=true  # Accept self-signed certificates

Authentication Options

SQL Authentication:

USER=dbusername
PASSWORD=dbpassword

Windows/Azure AD: Use integrated authentication (no explicit credentials needed)

Core Capabilities

1. Query Execution

Execute SQL queries through natural language requests.

Examples:

• "Get all users from California"
• "Show top 100 orders by date"
• "Calculate total revenue by month"
• "Find products with price > $50"

Security: Requires WHERE clauses for safety.

2. Data Modification

Create, update, and delete records.

Insert:

• "Add new user: John Doe, john@example.com"
• "Create product: Laptop, $999"

Update:

• "Update user ID 123 email to new@example.com"
• "Change order status to 'shipped' for order 456"

Delete:

• "Delete orders older than 1 year"
• "Remove user with ID 789"

Security: UPDATE and DELETE require WHERE clauses.

3. Schema Management

Create and modify database structures.

Create Tables:

• "Create users table with id, name, email"
• "Add products table with id, name, price, category"

Modify Schema:

• "Add phone column to customers table"
• "Create index on email in users"
• "Add foreign key from orders to customers"

Drop Objects:

• "Drop table temp_data"
• "Remove index idx_email from users"

4. Database Inspection

Explore database structure and metadata.

Tables:

• "List all tables"
• "Show tables in database"

Schema:

• "Describe users table"
• "Show structure of orders table"
• "What columns are in products table?"

Relationships:

• "List foreign keys"
• "Show table relationships"
• "Display indexes on orders table"

Common Query Patterns

Filtering

"WHERE" conditions for targeting specific records:
- "users from New York"
- "orders created this month"
- "products with price between $10 and $50"
- "customers who signed up in 2024"

Aggregations

Calculations across datasets:
- "count of users by state"
- "total revenue by quarter"
- "average order value"
- "maximum price in each category"

Sorting & Limiting

Ordering and constraining results:
- "top 10 orders by amount"
- "latest 50 users"
- "highest grossing products"
- "most recent transactions"

Joins & Relationships

Combining data from multiple tables:
- "orders with customer names"
- "products with their category names"
- "users with their order counts"
- "customers and their total spend"

Safety Features

Mandatory WHERE Clauses

SELECT Queries: Must include filtering conditions to prevent full table scans.

Good: "Get users where state = 'CA'" Bad: "Get all users" (blocked)

UPDATE Statements: Must specify which records to update.

Good: "Update email for user ID 123" Bad: "Update all emails" (blocked)

DELETE Statements: Must specify which records to delete.

Good: "Delete orders older than 2 years" Bad: "Delete all orders" (blocked)

Read-Only Mode

Set READONLY=true to:

• Disable INSERT, UPDATE, DELETE
• Allow only SELECT and schema inspection
• Safe for production database exploration
• Prevent accidental data changes

SQL Server Data Types

Numeric

• INT, BIGINT, SMALLINT, TINYINT
• DECIMAL(p,s), NUMERIC(p,s)
• FLOAT, REAL
• MONEY, SMALLMONEY

String

• VARCHAR(n), NVARCHAR(n)
• CHAR(n), NCHAR(n)
• TEXT, NTEXT (deprecated)

Date/Time

• DATE, TIME
• DATETIME, DATETIME2
• SMALLDATETIME
• DATETIMEOFFSET

Binary

• BINARY(n), VARBINARY(n)
• IMAGE (deprecated)

Other

• BIT (boolean)
• UNIQUEIDENTIFIER (GUID)
• XML
• JSON (via NVARCHAR)

Common Schema Patterns

Users Table

CREATE TABLE users (
    id INT IDENTITY(1,1) PRIMARY KEY,
    username NVARCHAR(50) UNIQUE NOT NULL,
    email NVARCHAR(255) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    created_at DATETIME2 DEFAULT GETUTCDATE(),
    updated_at DATETIME2 DEFAULT GETUTCDATE(),
    is_active BIT DEFAULT 1
);

Orders Table

CREATE TABLE orders (
    id INT IDENTITY(1,1) PRIMARY KEY,
    customer_id INT NOT NULL FOREIGN KEY REFERENCES customers(id),
    order_date DATETIME2 DEFAULT GETUTCDATE(),
    total_amount DECIMAL(10,2) NOT NULL,
    status VARCHAR(20) DEFAULT 'pending',
    created_at DATETIME2 DEFAULT GETUTCDATE()
);

Products Table

CREATE TABLE products (
    id INT IDENTITY(1,1) PRIMARY KEY,
    name NVARCHAR(100) NOT NULL,
    description NVARCHAR(MAX),
    price DECIMAL(10,2) NOT NULL,
    category_id INT FOREIGN KEY REFERENCES categories(id),
    stock_quantity INT DEFAULT 0,
    created_at DATETIME2 DEFAULT GETUTCDATE()
);

Best Practices

1. Query Optimization

• Limit result sets: "top N records"
• Include time ranges: "in the last 30 days"
• Use specific filters: "where status = 'active'"
• Aggregate when possible: "count by category"

2. Data Safety

• Query before modifying: verify target records
• Use transactions: for related changes
• Backup before schema changes
• Test in development first

3. Performance

• Add indexes on frequently queried columns
• Filter early with WHERE clauses
• Avoid SELECT * patterns
• Use appropriate data types

4. Security

• Use read-only mode for analysis
• Minimum privilege accounts
• Never hardcode credentials
• Audit all modifications
• Review generated queries

Troubleshooting

Connection Failures

• Verify SERVER_NAME format
• Check network/firewall
• Confirm SQL Server is running
• Validate credentials
• Set TRUST_SERVER_CERTIFICATE if needed

Query Errors

• Add WHERE clauses for filtering
• Reduce result set size
• Check syntax in generated SQL
• Verify column names exist

Permission Denied

• Check account permissions
• Verify READONLY mode isn't blocking writes
• Confirm database user roles
• Review table-level permissions

Timeout Issues

• Increase CONNECTION_TIMEOUT
• Optimize queries with indexes
• Reduce data volume with filters
• Check server performance

T-SQL Quick Reference

Common Functions

-- String functions
LEN(column), SUBSTRING(col, start, length), CONCAT(a, b)
UPPER(col), LOWER(col), TRIM(col), REPLACE(col, old, new)

-- Date functions
GETDATE(), GETUTCDATE(), DATEADD(day, 7, date)
DATEDIFF(day, start, end), YEAR(date), MONTH(date)
FORMAT(date, 'yyyy-MM-dd')

-- Aggregate functions
COUNT(*), SUM(col), AVG(col), MIN(col), MAX(col)
COUNT(DISTINCT col)

-- NULL handling
ISNULL(col, default), COALESCE(col1, col2, default)
NULLIF(a, b)

Window Functions

-- Ranking
ROW_NUMBER() OVER (ORDER BY col)
RANK() OVER (PARTITION BY cat ORDER BY col)
DENSE_RANK() OVER (ORDER BY col DESC)

-- Aggregates
SUM(amount) OVER (ORDER BY date)
AVG(price) OVER (PARTITION BY category)
LAG(col, 1) OVER (ORDER BY date)
LEAD(col, 1) OVER (ORDER BY date)

Common Table Expressions

WITH cte AS (
    SELECT column1, column2
    FROM table
    WHERE condition
)
SELECT * FROM cte;

Additional Resources

• SQL Server Documentation
• T-SQL Reference
• Azure SQL Database
• MssqlMcp GitHub