FIS IntelliMatch MCP Server for MS SQL

A Model Context Protocol (MCP) server that connects AI assistants directly to your FIS IntelliMatch SQL Server database for day-to-day BAU (Business As Usual) operations.

Security first: All queries run as parameterized statements. Only SELECT is permitted — write operations, DDL, and SQL comments are blocked at the guard layer. Zero npm vulnerabilities.

Table of Contents

• What It Does

• Prerequisites

• Installation

• Configuration

• Build & Run

• VS Code Integration — Cline Plugin

• VS Code Integration — Roo Code Plugin

• Claude Desktop Integration

• Available Tools (19)

• Table Name Customisation

• Troubleshooting

• BAU Use Cases & Example Prompts

• Security Notes

Related MCP server: MSSQL MCP Server

What It Does

Gives your AI assistant (via Cline, Roo Code, or Claude Desktop) direct, read-only access to IntelliMatch data so you can ask questions like:

• "Show me all aged breaks older than 5 days in USD"

• "What is the match rate for today's recon?"

• "Did all overnight jobs complete successfully?"

• "Find the item with reference TXN-20240613-001"

• "Generate a daily BAU summary for today"

Prerequisites

Check your Node version:

node --version   # should print v20.x.x or higher
npm --version    # should print 10.x.x or higher

Installation

1. Clone or copy the project

# If you received this as a zip, extract to your preferred location, e.g.:
C:\NewInitiatives\mcp\mcp-mssql\

2. Install dependencies

Open a terminal in the project folder and run:

npm install

Expected output:

added 167 packages, and audited 168 packages
found 0 vulnerabilities

Configuration

1. Create your .env file

Copy the example file and fill in your credentials:

# Windows
copy .env.example .env

# PowerShell
Copy-Item .env.example .env

Open .env and set your values:

# ── SQL Server Connection ──────────────────────────────────────────────────────
MSSQL_SERVER=your-sql-server-hostname
MSSQL_DATABASE=IntelliMatch
MSSQL_USER=im_readonly_user
MSSQL_PASSWORD=your_secure_password
MSSQL_PORT=1433

# Encryption (recommended: true for production)
MSSQL_ENCRYPT=true

# Set true only for self-signed / dev certificates
MSSQL_TRUST_CERT=false

# ── Table Name Overrides (only change if your schema differs) ──────────────────
IM_TBL_ITEM=dbo.ITEM
IM_TBL_MATCH=dbo.MATCH_RESULT
IM_TBL_EXCEPTION=dbo.EXCEPTION
IM_TBL_RECON_GROUP=dbo.RECON_GROUP
IM_TBL_JOB_LOG=dbo.JOB_LOG
IM_TBL_ERROR_LOG=dbo.ERROR_LOG
IM_TBL_DATA_LOAD=dbo.DATA_LOAD_LOG
IM_TBL_AUDIT_LOG=dbo.AUDIT_LOG

# ── Query Safety ───────────────────────────────────────────────────────────────
MAX_ROWS=500

Tip: The .env file is never committed. Keep credentials out of source control.

2. Verify your SQL Server connectivity

# Quick connectivity test using sqlcmd (if installed)
sqlcmd -S your-server -d IntelliMatch -U im_readonly_user -Q "SELECT @@VERSION"

3. Recommended SQL Server permissions

Grant your service account the minimum required permissions:

-- Run as DBA on the IntelliMatch database
CREATE LOGIN im_readonly_user WITH PASSWORD = 'your_secure_password';
USE IntelliMatch;
CREATE USER im_readonly_user FOR LOGIN im_readonly_user;

-- Grant read-only access to the IntelliMatch schema
ALTER ROLE db_datareader ADD MEMBER im_readonly_user;

-- Optional: allow viewing execution plans
GRANT SHOWPLAN TO im_readonly_user;

Build & Run

Build (compile TypeScript → JavaScript)

npm run build

Output is written to the dist/ folder. Compiled once; run repeatedly without rebuilding unless source changes.

Test the server manually

npm start

The server starts and listens on stdin/stdout (MCP protocol). You will see:

FIS IntelliMatch MCP server started (19 tools available)

Press Ctrl+C to stop.

Development mode (no build step required)

npm run dev

Uses tsx to run TypeScript directly — useful during development.

VS Code Integration — Cline Plugin

Cline is an AI coding assistant for VS Code that supports MCP servers.

Step 1 — Install Cline

1. Open VS Code

2. Press Ctrl+Shift+X to open Extensions

3. Search for Cline (publisher: saoudrizwan)

4. Click Install

Step 2 — Open Cline MCP Settings

Option A — Via the Cline UI:

1. Click the Cline icon in the VS Code sidebar

2. Click the MCP Servers icon (plug icon) in the Cline panel

3. Click Edit MCP Settings

Option B — Edit the settings file directly:

Open this file in VS Code:

%APPDATA%\Code\User\globalStorage\saoudrizwan.claude-dev\settings\cline_mcp_settings.json

PowerShell shortcut:

code "$env:APPDATA\Code\User\globalStorage\saoudrizwan.claude-dev\settings\cline_mcp_settings.json"

Step 3 — Add the IntelliMatch MCP Server

Add the following entry inside the mcpServers object. Replace the path and credentials:

{
  "mcpServers": {
    "intellimatch-mssql": {
      "command": "node",
      "args": ["C:\\NewInitiatives\\mcp\\mcp-mssql\\dist\\index.js"],
      "env": {
        "MSSQL_SERVER": "your-sql-server-hostname",
        "MSSQL_DATABASE": "IntelliMatch",
        "MSSQL_USER": "im_readonly_user",
        "MSSQL_PASSWORD": "your_secure_password",
        "MSSQL_PORT": "1433",
        "MSSQL_ENCRYPT": "true",
        "MSSQL_TRUST_CERT": "false",
        "MAX_ROWS": "500"
      },
      "disabled": false,
      "alwaysAllow": []
    }
  }
}

Note: Use double backslashes \\ in JSON path strings on Windows.

Step 4 — Verify in Cline

1. Reload VS Code (Ctrl+Shift+P → Developer: Reload Window)

2. Open Cline panel → MCP Servers

3. You should see intellimatch-mssql listed with a green indicator

4. Click the server name to see all 19 available tools

Step 5 — Test in Cline chat

In the Cline chat input, type:

Use the im_test_connection tool to check the IntelliMatch database connection

VS Code Integration — Roo Code Plugin

Roo Code (formerly Roo-Cline) is a Cline fork with additional features and also supports MCP servers.

Step 1 — Install Roo Code

1. Open VS Code

2. Press Ctrl+Shift+X

3. Search for Roo Code (publisher: RooVeterinaryInc)

4. Click Install

Step 2 — Open Roo Code MCP Settings

Option A — Via Roo Code UI:

1. Click the Roo Code icon in the VS Code sidebar

2. Click the Settings (gear) icon

3. Select MCP Servers → Edit MCP Settings

Option B — Edit the settings file directly:

%APPDATA%\Code\User\globalStorage\rooveterinaryinc.roo-cline\settings\cline_mcp_settings.json

PowerShell shortcut:

code "$env:APPDATA\Code\User\globalStorage\rooveterinaryinc.roo-cline\settings\cline_mcp_settings.json"

Step 3 — Add the IntelliMatch MCP Server

The JSON format is identical to Cline:

{
  "mcpServers": {
    "intellimatch-mssql": {
      "command": "node",
      "args": ["C:\\NewInitiatives\\mcp\\mcp-mssql\\dist\\index.js"],
      "env": {
        "MSSQL_SERVER": "your-sql-server-hostname",
        "MSSQL_DATABASE": "IntelliMatch",
        "MSSQL_USER": "im_readonly_user",
        "MSSQL_PASSWORD": "your_secure_password",
        "MSSQL_PORT": "1433",
        "MSSQL_ENCRYPT": "true",
        "MSSQL_TRUST_CERT": "false",
        "MAX_ROWS": "500"
      },
      "disabled": false,
      "alwaysAllow": []
    }
  }
}

Step 4 — Configure Roo Code Modes (Optional)

Roo Code supports custom AI modes. You can create an IntelliMatch BAU mode:

1. Open Roo Code settings → Modes

2. Click Add Custom Mode

3. Fill in:

   • Name: IntelliMatch BAU

   • System Prompt:

     You are an IntelliMatch reconciliation analyst assistant. 
     You have access to the IntelliMatch MSSQL database via MCP tools.
     Always use parameterised tool calls. Never guess data — query it.
     For break investigation, start with im_get_break_details, 
     then cross-reference with im_search_items.

4. Enable the intellimatch-mssql MCP server for this mode

Step 5 — Verify in Roo Code

1. Reload VS Code

2. Open Roo Code panel → check MCP server status shows green

3. Test with: "Run im_test_connection to check the IntelliMatch DB"

Claude Desktop Integration

If you use Claude Desktop alongside VS Code:

Edit %APPDATA%\Claude\claude_desktop_config.json:

{
  "mcpServers": {
    "intellimatch-mssql": {
      "command": "node",
      "args": ["C:\\NewInitiatives\\mcp\\mcp-mssql\\dist\\index.js"],
      "env": {
        "MSSQL_SERVER": "your-sql-server-hostname",
        "MSSQL_DATABASE": "IntelliMatch",
        "MSSQL_USER": "im_readonly_user",
        "MSSQL_PASSWORD": "your_secure_password",
        "MSSQL_PORT": "1433",
        "MSSQL_ENCRYPT": "true",
        "MSSQL_TRUST_CERT": "false"
      }
    }
  }
}

Restart Claude Desktop after saving.

Available Tools (19)

Connection & Discovery

Reconciliation

Breaks & Exceptions

Items & Transactions

Job Monitoring

Ad-hoc Query

Table Name Customisation

IntelliMatch deployments vary. If your schema uses different table names, override them in .env (or in the env block of your MCP config):

# Example: if your items table is called dbo.IM_ITEMS
IM_TBL_ITEM=dbo.IM_ITEMS

# Example: if breaks are in a different schema
IM_TBL_EXCEPTION=reconciliation.BREAKS

Use im_list_tables to discover what tables exist in your database, then im_describe_table to inspect columns before configuring overrides.

Troubleshooting

MCP server not appearing in Cline / Roo Code

• Confirm the dist/index.js path exists: run npm run build first

• Use forward-slash paths in JSON on Windows: C:/NewInitiatives/mcp/mcp-mssql/dist/index.js or escape backslashes: C:\\NewInitiatives\\mcp\\mcp-mssql\\dist\\index.js

• Reload VS Code after editing settings JSON

Connection failed / timeout

Error executing "im_test_connection": ConnectionError: Failed to connect

• Verify the SQL Server is reachable: Test-NetConnection your-server -Port 1433

• Check firewall rules allow port 1433 from your machine

• Confirm MSSQL_USER and MSSQL_PASSWORD are correct

• If using Windows Auth (no user/password), remove MSSQL_USER/MSSQL_PASSWORD from env — mssql will use the process identity

Self-signed certificate error

Error: The certificate chain was issued by an authority that is not trusted

Set in your env:

MSSQL_TRUST_CERT=true

Table not found / invalid object name

Invalid object name 'dbo.ITEM'

Your IntelliMatch deployment uses different table names. Run:

im_list_tables          → discover tables in dbo schema
im_describe_table       → verify column names

Then set the correct names via IM_TBL_* environment variables.

Query returns no rows but data exists

• Check VALUE_DATE format — must be YYYY-MM-DD

• Verify the date column matches your IntelliMatch schema (some use SETTLE_DATE or TXN_DATE)

• Use im_execute_select to run a direct SELECT TOP 5 * FROM dbo.ITEM to inspect actual data

Node version errors

SyntaxError: Cannot use import statement

Upgrade Node.js to v20 LTS or later. The server uses ES modules which require Node 20+.

Project Structure

mcp-mssql/
├── src/
│   ├── index.ts              MCP server entry point
│   ├── config.ts             DB config + table name resolution
│   ├── database.ts           Connection pool singleton
│   ├── guard.ts              SQL safety: SELECT-only + input sanitisation
│   └── tools/
│       ├── index.ts          Tool registry + dispatcher
│       ├── connection.ts     Connection & schema discovery tools
│       ├── recon.ts          Reconciliation tools
│       ├── breaks.ts         Break & exception tools
│       ├── items.ts          Item & transaction tools
│       ├── jobs.ts           Job monitoring tools
│       └── query.ts          Ad-hoc SELECT tool
├── dist/                     Compiled JavaScript (generated by npm run build)
├── .env.example              Environment variable template
├── .env                      Your local config (never commit this)
├── package.json
├── tsconfig.json
└── README.md

BAU Use Cases & Example Prompts

Real-world scenarios and the prompts you can type directly into Cline or Roo Code chat.

Connecting to the Database

Before anything else, verify the connection is healthy:

Prompt:

Test the IntelliMatch database connection and show me the server version.

Calls im_test_connection — confirms SQL Server is reachable and returns the database name and version.

Prompt:

Show me the current MCP configuration — what tables is it using and what is the row limit?

Calls im_get_configuration — displays all table name mappings and settings without exposing the password.

Discover the Schema (First-time Setup)

If you are unsure whether the default table names match your IntelliMatch deployment:

Prompt:

List all tables in the dbo schema so I can see what IntelliMatch tables exist.

Calls im_list_tables — shows every table in the dbo schema.

Prompt:

Describe the ITEM table so I can see its column names and data types.

Calls im_describe_table with table_name: "ITEM" — shows all columns, types, and nullability.

Prompt:

Describe the EXCEPTION table and tell me which columns hold the break amount and age.

Helps identify the right column names before customising your .env table overrides.

Morning BAU Checks

The most common start-of-day workflow:

Prompt:

Give me a full daily BAU summary for today including recon status and open breaks.

Calls im_get_daily_summary — one-shot morning health check covering match rates and break overview.

Prompt:

Did all overnight batch jobs complete successfully? Show me today's job status.

Calls im_get_job_status for today — highlights any FAILED or still-RUNNING jobs.

Prompt:

Check if all data loads completed for today. Flag any that failed or have errors.

Calls im_get_load_status — shows records received vs loaded and highlights failures.

Prompt:

Show me any ERROR-level log entries from today's processing.

Calls im_get_error_log with severity ERROR and today's date.

Prompt:

Are there any jobs still running or waiting right now?

Calls im_get_pending_processes — shows live job status.

Reconciliation Status

Prompt:

What is today's match rate across all recon groups? Show matched vs unmatched counts.

Calls im_get_recon_summary for today's date.

Prompt:

Show me the match statistics for the past week broken down by currency, 
including the total break amount for each currency.

Calls im_get_match_statistics with a 7-day date range.

Prompt:

List all active reconciliation groups and their types.

Calls im_get_recon_groups with active_only: true.

Prompt:

What is the match rate for the NOSTRO recon group for today?

Calls im_get_recon_summary filtered to the NOSTRO recon group.

Break & Exception Investigation

Prompt:

Show me all open breaks in USD for today sorted by largest amount first.

Calls im_get_breaks with currency: "USD" and today's date.

Prompt:

Find all open breaks older than 5 days — I need to escalate the aged items.

Calls im_get_aged_breaks with age_days: 5.

Prompt:

Show me the break aging report — how many breaks are in each age bucket 
and what is the total break amount?

Calls im_break_aging_report — gives a bucket summary (0-2, 3-5, 6-10, 11-30, 30+ days).

Prompt:

Get me the full details of exception EXC-2024-00123 including both sides of the break.

Calls im_get_break_details with exception_id: "EXC-2024-00123".

Prompt:

I need to review all open GBP breaks for the entity "LONDON-DESK" 
older than 3 days — can you pull those up?

Calls im_get_aged_breaks with age_days: 3, currency: "GBP", entity: "LONDON-DESK".

Item & Transaction Investigation

Prompt:

Find the item with reference TXN-20240613-4521 and show me its full details 
including whether it matched.

Calls im_search_items with item_ref: "TXN-20240613-4521", then im_get_item_details for the result.

Prompt:

Show me all unmatched items for today in EUR — I want to investigate why they didn't match.

Calls im_get_unmatched_items with currency: "EUR" and today's date.

Prompt:

Search for all EXCEPTION items from the PRIME-BROKER recon group for 2024-06-12.

Calls im_search_items with match_status: "EXCEPTION", recon_group: "PRIME-BROKER", value_date: "2024-06-12".

Prompt:

Item ITEM-98765 is showing as unmatched. Get its full details including 
any linked match or exception.

Calls im_get_item_details with item_id: "ITEM-98765" — shows match result and linked exception if any.

Ad-hoc SQL Investigation

When the built-in tools don't cover your specific query, use im_execute_select:

Prompt:

Run this query for me:
SELECT VALUE_DATE, CURRENCY, COUNT(*) AS item_count, SUM(AMOUNT) AS total_amount
FROM dbo.ITEM
WHERE VALUE_DATE >= '2024-06-01'
GROUP BY VALUE_DATE, CURRENCY
ORDER BY VALUE_DATE DESC, total_amount DESC

Calls im_execute_select — executes any SELECT safely with row capping.

Prompt:

Query the top 20 largest unmatched items by amount across all currencies for this month.

Claude will write the SELECT and call im_execute_select on your behalf.

Prompt:

Show me items from the SWIFT source system that arrived today but have no match yet, 
ordered by amount descending. Max 50 rows.

Claude constructs and executes a targeted SELECT query.

Multi-step Investigation Workflow

Cline and Roo Code can chain multiple tool calls in a single conversation:

Prompt:

I need to investigate today's reconciliation health. 
Please:
1. Check the connection is live
2. Show today's recon summary
3. List any failed jobs
4. Pull the top 10 aged breaks over 3 days
5. Give me your assessment of what needs attention today

The AI calls im_test_connection → im_get_recon_summary → im_get_job_status (filtered FAILED) → im_get_aged_breaks and then summarises the findings.

Prompt:

A trader is querying an item with reference "BOND-EUR-20240613-0042". 
Find it, show its match status, and if it has a break get the full break details.

Multi-step: im_search_items → im_get_item_details → im_get_break_details (if needed).

Security Notes

• Read-only by design: guard.ts blocks all write keywords before any query executes

• No raw string SQL: every user-supplied value goes through a parameterised request.input() call

• Row cap: all tools enforce a TOP N limit (default 500, max 2000) to prevent large data dumps

• No vulnerable packages: npm audit reports 0 vulnerabilities at install time

• Credentials in env only: the dist/ output contains no credentials; they are injected at runtime via environment variables