sql-mcp-server

A Model Context Protocol (MCP) server built with mcp-framework for SQL Server database interactions.

Quick Start

# Install dependencies
npm install

# Build the project
npm run build

# Start the server
npm start

Project Structure

sql-mcp-server/
├── src/
│   ├── services/       # Services for the application
│   │   └── sql-service.ts
│   ├── tools/          # MCP Tools
│   │   ├── execute-select-query-tool.ts
│   │   ├── get-database-schema-tool.ts
│   │   └── get-databases-tool.ts
│   └── index.ts        # Server entry point
├── .env                # Environment configuration
├── package.json
└── tsconfig.json

Available Tools

The project comes with several SQL-focused tools:

1. get-databases-tool - Lists available databases on the SQL Server

2. get-database-schema-tool - Retrieves schema information for specified databases

3. execute-select-query-tool - Runs parameterized SELECT queries against the database

SQL Service

The `SqlService` class provides core functionality for:

• Creating connection strings using environment variables
• Executing queries against SQL Server databases

Environment Configuration

Configure your database connection in the `.env` file:

DB_SERVER=localhost\SQLEXPRESS
DB_DATABASE=AdventureWorks2019
DB_USERNAME=readonly_user
DB_PASSWORD=StrongPassword!123
DB_PORT=1433

AdventureWorks Sample Database

This project uses the AdventureWorks sample database. You can download and install it from:

• Microsoft Docs: Install and Configure AdventureWorks

Follow the instructions on the Microsoft documentation page to download the database backup file and restore it to your SQL Server instance.

Creating a Read-Only User

To create a read-only user for SQL Server that the application can use:

-- Create a new login and user in master database
CREATE LOGIN readonly_user WITH PASSWORD = 'StrongPassword!123';
GO

-- Switch to your target database
USE AdventureWorks2019;
GO

-- create user for the MasterDB login 
CREATE USER readonly_user FOR LOGIN readonly_user;

-- Add the user to the db_datareader role for read-only access
ALTER ROLE db_datareader ADD MEMBER readonly_user;
GO

> Note: You'll need to create the SQL Server login first using SQL Server Management Studio or with the `CREATE LOGIN` command before running these commands.

Using with VS Code

You can configure the MCP server to run within VS Code by following these steps:

1. Create an `.vscode/mcp.json` file in your project:

{
  "servers": {
    "sql-mcp-server": {
      "url": "http://localhost:1337/mcp"
    }
  }
}

Building and Testing

1. Make changes to your tools

2. Run `npm run build` to compile

3. The server will automatically load your tools on startup

Debugging

You can debug your MCP server using the `mcp-debug` tool:

# Start the server in one terminal
npm start

# In another terminal, run mcp-debug
npx mcp-debug

The `mcp-debug` tool provides a CLI interface to:

• List available tools on your MCP server
• Execute tools with specific parameters
• View the full responses from your server

This helps you test your tools before integrating them with an LLM application. For more information, see the MCP Framework HTTP Quickstart.

Learn More

• MCP Framework Github
• MCP Framework Docs