How to Use Microsoft SQL Server MCP

MS SQL Server MCP enables seamless AI-powered interactions with your SQL Server databases. It allows you to query data, explore tables, and extract valuable insights—all through a simple and secure interface. This tutorial will guide you through the setup and usage of an MCP (Model Context Protocol) server specifically designed for MS SQL Server. This allows you to connect AI assistants like Claude directly to your database, enabling a new level of data exploration and automation. Let's get started!

💡

Before setting up the Microsoft SQL Server MCP Server, take a look at Apidog—a robust tool for designing, testing, and documenting APIs. Apidog streamlines API integration, offering structured models and smooth collaboration. For enhanced automation and API management, it pairs perfectly with Zapier MCP.

button

What is MS SQL Server MCP?

MS SQL Server MCP is a specialized server that implements the Model Context Protocol (MCP) for secure interaction with Microsoft SQL Server databases. Simply put, it acts as a translator and gatekeeper between your AI assistant and your SQL Server instance.

Instead of directly exposing your database to AI models, which could be risky, the MS SQL Server MCP server provides a controlled and structured interface. This interface allows AI models to:

List available tables in your database.
Read the contents of specific tables.
Execute SQL queries to retrieve specific data.

All of this happens securely, with proper error handling and comprehensive logging, ensuring that your data remains protected.

Key Features of the MS SQL Server MCP Server

The MS SQL Server MCP is equipped with a range of features to simplify and secure interactions with your SQL Server database:

List available SQL Server tables as resources
Read table contents
Execute SQL queries with robust error handling
Secure database access via environment variables
Detailed logging
Automatic installation of system dependencies

MS SQL Server MCP Installation: Getting the MCP Server Ready

Here's how to install the MS SQL Server MCP server:

Install the mssql-mcp-server package:

Open your terminal or command prompt and run the following command:

pip install mssql-mcp-server

This command uses pip, the Python package installer, to download and install the mssql-mcp-server package along with all its dependencies.

Behind the Scenes:

pip: This is the package installer for Python. It's used to download and install software packages from the Python Package Index (PyPI).
install: This tells pip to install a package.
mssql-mcp-server: This is the name of the MCP server package that we want to install.

The pip install command will automatically download and install all the necessary system dependencies, such as FreeTDS (a library that allows Python to communicate with SQL Server). This makes the installation process incredibly easy.

HiMCP.ai - Discover 1682+ MCP Servers

MS SQL Server MCP Configuration: Connecting with Claude Desktop

With the MCP server installed and configured, you can now connect it to your favorite AI assistant, such as Claude Desktop.

Add the MS SQL Server MCP Configuration to Claude Desktop:

Open Claude Desktop and go to the settings (usually a gear icon). Look for a "Developer" or "Advanced" section, where you can edit the configuration file (usually named claude_desktop_config.json).

Add the following configuration to the mcpServers section of the claude_desktop_config.json file:

{
  "mcpServers": {
    "mssql": {
      "command": "uv",
      "args": [
        "--directory",
        "path/to/mssql_mcp_server",
        "run",
        "mssql_mcp_server"
      ],
      "env": {
        "MSSQL_SERVER": "localhost",
        "MSSQL_USER": "your_username",
        "MSSQL_PASSWORD": "your_password",
        "MSSQL_DATABASE": "your_database"
      }
    }
  }
}

Important Notes:

command: This specifies the command to run the MCP server. In this case, we're using "uv", assuming you have the uv package manager installed.
args: This is an array of arguments to pass to the command. The --directory argument specifies the path to the directory where the mssql_mcp_server package is installed. You need to replace "path/to/mssql_mcp_server" with the actual path to this directory. You can determine the installed location by opening the Python terminal and running the following commands: "import mssql_mcp_server" then "print(mssql_mcp_server.__file__)", this will display the location of the mssql_mcp_server package.
env: This is an object that specifies the environment variables to pass to the MCP server. Make sure to replace "localhost", "your_username", "your_password", and "your_database" with the actual values for your SQL Server instance.

Restart Claude Desktop: To ensure that the changes take effect, restart Claude Desktop completely. This involves closing the application and reopening.

MS SQL Server MCP Usage: Testing the Connection

Now that you've configured Claude Desktop, you can test the connection to your SQL Server database.

Use Claude to list the available tables:

In Claude Desktop, type a command like:

 >> "List the tables in my SQL Server database"

If everything is set up correctly, Claude should be able to list the tables in your database.

Use Claude to read the contents of a table:

Type a command like:

>> "Give me a description of each table based on their contents"

Claude should be able to give descriptions of all tables in your database.

Use Claude to create chats:

With Claude, you can create chats and seamlessly visualize table data within the conversation.

Running MS SQL Server MCP as a Standalone Server

In addition to using the MCP server with Claude Desktop, you can also run it as a standalone server. This allows you to connect to it from other applications or AI models.

Clone the repo:

Open your terminal or command prompt and run the following command:

git clone https://github.com/RichardHan/mssql_mcp_server.git
cd mssql_mcp_server

Install Dependencies:

Run the command:

pip install -r requirements.txt

Run the Server:

Run the command:

python -m mssql_mcp_server

This command will start the MCP server. The server will listen for connections on a specific port (usually 5000).

You can then connect to the server from other applications using the MCP.

Development: Contributing to the MS SQL Server MCP Project

If you're interested in contributing to the MS SQL Server MCP project, here's how to set up a development environment:

Clone the Repository:

Open your terminal or command prompt and run the following command:

git clone https://github.com/RichardHan/mssql_mcp_server.git
cd mssql_mcp_server

This will download the code from GitHub and move you into the project directory.

Create a Virtual Environment:

python -m venv venv
source venv/bin/activate  # or `venv\Scripts\activate` on Windows

This creates a virtual environment to isolate the project's dependencies.

Install Development Dependencies:

pip install -r requirements-dev.txt

This installs the dependencies required for development, such as testing libraries and code linters.

Run Tests:

pytest ...

This runs the project's unit tests to ensure that the code is working correctly. Replace "..." with the name of the test file you wish to run, or remove it to run all the tests.

Conclusion

You've now successfully set up and configured the MS SQL Server MCP server! This opens up a world of possibilities for integrating AI with your SQL Server databases. You can now use AI assistants like Claude to explore your data, generate insights, and automate tasks. The MCP ensures secure and controlled access to your data, while providing a standardized interface for connecting different AI tools.

So, go forth and unleash the power of your data! Explore the capabilities of the MS SQL Server MCP server and discover new ways to leverage AI in your data analysis workflows.

button