Let’s try using Microsoft SQL Server MCP server with Claude Desktop

For Thai Version: ลองใช้ Microsoft SQL Server mcp server กับ Claude Desktop กัน

For this blog, I will document the use of Claude Desktop + Microsoft SQL Server MCP server and think about use cases to see what it can help you to save time & cost

What is MCP?

MCP (Model Context Protocol) is a common language that allows LLMs to connect with their surroundings, such as APIs or hardware, to perform actions (output) and retrieve information from databases, APIs, or files without having to imagine it. There are several companies working on MCP, so you can find them in the market, such as smithery.ai / github.com/modelcontextprotocol / mcp.so /  mcpservers.org

MCP Microsoft SQL Server

The MCP is a common language, and having a common language encourages more people to develop it, both from official sources and the community. When choosing to use it, you need to choose wisely. For MSSQL Server, there are several contributors

I tried using dotnet, and it crashed (I'll debug further)

For this blog, I will try the mssql-mcp-server by JexinSam. Next, I will document how to use it. Let's proceed.

Preparation

After installing the necessary runtime, let's install the mssql_mcp_server from https://github.com/JexinSam/mssql_mcp_server using command.

pip install mssql-mcp-server

After installation, we need to find out where the installed files are located because we need to use that path during the MCP setup in Claude Desktop with the command.

pip show mssql-mcp-server

Creating a new user in MSSQL Server

To prevent it from damaging the database, we might allow it to have read/write/update permissions only. Do not grant DELETE or DROP permissions for safety. Or if it's just for viewing, SELECT permissions are sufficient. In my case, it will be POSDB (using AI Gen DB for testing).

-- สร้างผู้ใช้ใหม่
CREATE LOGIN mpcread WITH PASSWORD = 'StrongPassword123!';

CREATE LOGIN mpcread WITH PASSWORD = 'mpcread ';
CREATE USER mpcread FOR LOGIN mpcread ;

-- กำหนดสิทธิ์ขั้นต่ำที่จำเป็น อย่าให้สิทธิ์ DELETE หรือ DROP เพื่อความปลอดภัย หรือ ถ้าแค่ View เอา SELECT อย่างเดียวพอ อย่างของผมจะเป็น POS 
GRANT SELECT ON SCHEMA::POS TO mpcread;
GRANT INSERT ON SCHEMA::POS TO mpcread;
GRANT UPDATE ON SCHEMA::POS TO mpcread;

However, the data we send will be public, as Claude is a closed model.

Connect to Claude Desktop

Open the menu File >> Settings, go to the Develop menu, and click Edit Config to find the file claude_desktop_config.json.

  • macOS: ~/Library/Application Support/Claude/claude_desktop_config.json
  • Windows: %APPDATA%\Claude\claude_desktop_config.json

Open the file claude_desktop_config.json and then edit the information as follows.

{
  "mcpServers": {
    "mssql": {
      "command": "uv",
      "args": [
        "--directory",
        "PATH_FROM_COMMAND pip show mssql-mcp-server + \\mssql_mcp_server ",
        "run",
        "mssql_mcp_server"
      ],
      "env": {
        "MSSQL_DRIVER": "YOUR_ODBC_DRIVER_NAME",
        "MSSQL_HOST": "YOUR_SERVER_NAME_OR_IP,YOUR_PORT_IF_CUSTOM",
        "MSSQL_DATABASE": "YOUR_DB_NAME",
        "MSSQL_USER": "YOUR_DB_USER",
        "MSSQL_PASSWORD": "YOUR_DB_PASS"
      }
    }
  }
}

Sample Config

{
  "mcpServers": {
    "mssql": {
      "command": "uv",
      "args": [
        "--directory",
        "C:\\Users\\chatri.ngam\\.pyenv\\pyenv-win\\versions\\3.13.5\\Lib\\site-packages\\mssql_mcp_server",
        "run",
        "mssql_mcp_server"
      ],
      "env": {
        "MSSQL_DRIVER": "ODBC Driver 17 for SQL Server",
        "MSSQL_HOST": "192.168.1.4,14330",
        "MSSQL_DATABASE": "POS_DB",
        "MSSQL_USER": "mcpread",
        "MSSQL_PASSWORD": "StrongPassword123!"
      }
    }
  }
}

Close Claude Desktop (make sure to close it without any processes running). When you reopen it, it should look like the image.

Prompt 

List Schema for table company

 When Claude Desktop trigger a MCP, it will ask for our permission.

The result obtained matches the actual data in the database.

Next, Use Case, Create An Data Dictionary document from current Database.

Let's try another task. I happen to have another database where I tried to implement a scraping feature for securities, so I will ask about it.

Thai Prompt: ฉันต้องการ Bond ที่มีอายุมากที่สุดใน Database
English Prompt: I want the oldest bond in the database.

The steps follow the image. The AI will pull the schema and try & error until it can join. If there are comments or foreign keys (FK) / primary keys (PK), or if we tell basic information of database schema with Prompt, it will guide the AI.

The MCP, if we expand its thinking step on Claude Desktop. it will show as Request (Query) / Response (the results from SQL).

That's it. Actually, if connected to other MCP servers, we can have it generate a complete Data Dictionary document. Next time, let's try connecting multiple MCPs or using open-source options as well.

Reference

Discover more from naiwaen@DebuggingSoft

Subscribe to get the latest posts sent to your email.

Tags

Related Posts