sql-mcp-server

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:
   • Create a directory named .vscode in your project root if it doesn't exist
   • Create a file named mcp.json inside the .vscode directory with the following content:

{
  "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