MSSQL Read-Only MCP Server for Claude Code

An MCP (Model Context Protocol) server that lets Claude Code run read-only queries against Microsoft SQL Server. Supports SQL auth and Azure AD. All connections use ApplicationIntent=ReadOnly and queries are validated to block any write operations.

Based on bilims/mcp-sqlserver with added Azure AD authentication, hardcoded read-only intent, and automatic schema caching.

Tools

| Tool | Purpose | | ------------------ | -------------------------------------------------------------------------------------------- | | execute_query | Run read-only SELECT queries. Automatically includes the full database schema on first call. | | list_tables | List all tables in a database or schema | | list_views | List all views in a database or schema | | describe_table | Get column details for a specific table | | get_foreign_keys | Get foreign key relationships | | get_table_stats | Get row counts and table sizes | | list_databases | List all databases on the server | | get_server_info | Get SQL Server version and edition | | test_connection | Verify the connection works | | snapshot_schema | Force-regenerate the schema cache file |

Read-Only Safety

Three independent layers prevent any write operations:

1. Connection level — ApplicationIntent=ReadOnly is hardcoded (routes to read replicas when available)
2. Query validation — Only SELECT and WITH statements are allowed. 17 keywords are blocked (INSERT, UPDATE, DELETE, DROP, EXEC, GRANT, etc.) plus SQL injection pattern detection
3. Database permissions — Use a db_datareader-only account for defense in depth

Setup

Prerequisites

• Node.js 18+
• Claude Code
• Azure CLI (only if using Azure AD auth): https://learn.microsoft.com/en-us/cli/azure/install-azure-cli

Step 1: Clone, install, and build

git clone https://github.com/trainerroad/mcp-sqlserver.git ~/.claude/mcp-sqlserver
cd ~/.claude/mcp-sqlserver
npm install
npm run build

Step 2: Choose your authentication method

Option A: Azure AD (recommended for Azure SQL)

1. Install the Azure CLI if you don't have it:

   # Windows (winget)
   winget install Microsoft.AzureCLI
   
   # macOS
   brew install azure-cli
   
   # Linux
   curl -sL https://aka.ms/InstallAzureCLIDeb | sudo bash

2. Sign in with the account that has database access:

   az login

3. Register the MCP server:

   claude mcp add mssql-readonly --scope project -- node .claude/mcp-sqlserver/dist/index.js

   claude mcp add mssql-readonly -s user \
     -e SQLSERVER_HOST=your-server.database.windows.net \
     -e SQLSERVER_DATABASE=your-database \
     -e SQLSERVER_AUTH_MODE=aad-default \
     -e SQLSERVER_ENCRYPT=true \
     -e SQLSERVER_TRUST_CERT=false \
     -e SQLSERVER_DOMAIN_SOURCE_PATH=/path/to/your-csharp-project \
     -- node ~/.claude/mcp-sqlserver/dist/index.js

   SQLSERVER_DOMAIN_SOURCE_PATH is optional. Set it to the root of a C# project that contains EntityFramework/Domain/Configurations/ to enrich the schema cache with entity-to-table mappings, column renames, and relationship metadata. Omit it if you don't use EF configurations.

Option B: SQL Server authentication

claude mcp add mssql-readonly -s user \
  -e SQLSERVER_HOST=your-server.database.windows.net \
  -e SQLSERVER_DATABASE=your-database \
  -e SQLSERVER_USER=your-username \
  -e SQLSERVER_PASSWORD=your-password \
  -e SQLSERVER_ENCRYPT=true \
  -e SQLSERVER_TRUST_CERT=false \
  -e SQLSERVER_DOMAIN_SOURCE_PATH=/path/to/your-csharp-project \
  -- node ~/.claude/mcp-sqlserver/dist/index.js

For on-premises SQL Server with self-signed certificates, set SQLSERVER_TRUST_CERT=true.

Step 3: Verify

claude mcp list

Should show: mssql-readonly: ... ✓ Connected

Step 4: Start a new Claude Code session

The MCP server only loads in new sessions. Try:

• "Test the SQL Server connection"
• "List all tables in the database"
• "Show me the top 10 rows from the Users table"

Schema Cache

On the first execute_query call in a session, the server automatically:

1. Checks for a cached schema file at .schema-cache/<database-name>.md (relative to the install directory)
2. If none exists, queries the database for all tables, columns, primary keys, and foreign keys
3. Writes a compact markdown cache file and includes it in the response

This means Claude Code gets full schema context on the first query — no extra tool calls needed. Subsequent queries in the same session skip the schema (already in context).

To refresh the cache after schema changes, call the snapshot_schema tool.

To use a custom cache path, set the SQLSERVER_SCHEMA_CACHE_PATH environment variable.

Domain Entity Mappings (Optional)

If you work with a C# project that uses Entity Framework, set SQLSERVER_DOMAIN_SOURCE_PATH to the project root containing EntityFramework/Domain/Configurations/ files. The schema cache will be enriched with:

• Entity-to-table name mappings (e.g., WorkoutRecord -> CyclingActivity table)
• Property-to-column renames
• Relationship navigation paths for JOIN construction
• TPH discriminator columns

This helps Claude translate domain concepts to accurate SQL queries.

Environment Variables

| Variable | Required | Default | Description | | ------------------------------ | --------------------- | ------------ | ---------------------------------------------------- | | SQLSERVER_HOST | Yes | localhost | Server hostname | | SQLSERVER_DATABASE | No | master | Default database | | SQLSERVER_AUTH_MODE | No | sql | Azure AD Auth Mode | | SQLSERVER_USER | For SQL auth | | SQL Server username | | SQLSERVER_PASSWORD | For SQL auth | | SQL Server password | | SQLSERVER_CLIENT_ID | No | | Azure AD application (client) ID | | SQLSERVER_CLIENT_SECRET | For service principal | | Azure AD client secret | | SQLSERVER_TENANT_ID | No | | Azure AD tenant ID | | SQLSERVER_PORT | No | 1433 | Server port | | SQLSERVER_ENCRYPT | No | true | Enable TLS encryption | | SQLSERVER_TRUST_CERT | No | true | Trust server certificate (set false for Azure SQL) | | SQLSERVER_MAX_ROWS | No | 1000 | Max rows per query (up to 10,000) | | SQLSERVER_CONNECTION_TIMEOUT | No | 30000 | Connection timeout in ms | | SQLSERVER_REQUEST_TIMEOUT | No | 60000 | Query timeout in ms | | SQLSERVER_SCHEMA_CACHE_PATH | No | Auto-derived | Override schema cache file path | | SQLSERVER_DOMAIN_SOURCE_PATH | No | | Path to C# project root with EF configurations |

Azure AD Auth Modes

| Mode | Use Case | Credential Source | | ----------------------- | ------------------------------- | -------------------------------------------------------------------------------- | | aad-default | Developer machines, Azure VMs | az login, managed identity, env vars — tries multiple sources automatically | | aad-password | Username/password with Azure AD | Requires SQLSERVER_USER, SQLSERVER_PASSWORD, SQLSERVER_CLIENT_ID | | aad-service-principal | CI/CD, automation | Requires SQLSERVER_CLIENT_ID, SQLSERVER_CLIENT_SECRET, SQLSERVER_TENANT_ID |

Troubleshooting

Azure AD: "AADSTS" errors

• Run az account show to verify you're signed in with the correct account
• Run az login to re-authenticate if your token has expired
• Ensure your Azure AD account has been granted access to the database (CREATE USER [[email protected]] FROM EXTERNAL PROVIDER)

Connection refused / timeout

• Verify the server hostname and port are correct
• Check firewall rules — Azure SQL requires your IP to be allowed
• For Azure SQL, ensure SQLSERVER_ENCRYPT=true and SQLSERVER_TRUST_CERT=false

Permission errors on queries

• The account needs at minimum db_datareader role membership
• For schema discovery, access to INFORMATION_SCHEMA views and sys. catalog views is required

Schema cache not generating

• Ensure the server process has write access to its install directory (or set SQLSERVER_SCHEMA_CACHE_PATH to a writable location)
• Check stderr output for errors: claude mcp list shows server logs on failure

Claude Code Automated Setup Instructions

When a user asks you to set up this MCP server, follow these steps exactly:

Step 1: Clone the repo

Clone into the user's Claude config directory:

• Windows (Git Bash): ~/.claude/mcp-sqlserver resolves to /c/Users/<username>/.claude/mcp-sqlserver
• macOS/Linux: ~/.claude/mcp-sqlserver

git clone https://github.com/trainerroad/mcp-sqlserver.git ~/.claude/mcp-sqlserver

If the directory already exists, run git -C ~/.claude/mcp-sqlserver pull instead.

Step 2: Install dependencies and build

cd ~/.claude/mcp-sqlserver && npm install && npm run build

Step 3: Determine auth method

Ask the user how they authenticate to their SQL Server:

• Azure AD — Continue to Step 3a.
• SQL auth — They need a username and password. Skip to Step 4.

Step 3a: Verify Azure CLI is installed and signed in

Run az --version to check if the Azure CLI is installed. If the command is not found, stop and tell the user:

Azure CLI is required for Azure AD authentication but is not installed. Install it from https://learn.microsoft.com/en-us/cli/azure/install-azure-cli

Quick install commands:

• Windows: winget install Microsoft.AzureCLI
• macOS: brew install azure-cli
• Linux: curl -sL https://aka.ms/InstallAzureCLIDeb | sudo bash

After installing, restart your terminal and run this setup again.

Do NOT continue with the remaining steps until az --version succeeds.

Once Azure CLI is confirmed installed, run az account show --query "{name:name, user:user.name}" -o table to check the login status.

• If signed in, confirm the account shown is the one with database access. If not, tell the user to run az login and sign in with the correct account.
• If not signed in (error), tell the user to run az login and complete the browser sign-in flow, then re-run az account show to confirm.

Step 4: Register the MCP server

Use the absolute path to the built entry point:

• Windows: C:/Users/<username>/.claude/mcp-sqlserver/dist/index.js
• macOS: /Users/<username>/.claude/mcp-sqlserver/dist/index.js
• Linux: /home/<username>/.claude/mcp-sqlserver/dist/index.js

If the user has a C# project with EF configurations, ask for the path and include -e SQLSERVER_DOMAIN_SOURCE_PATH=<path>. This is optional.

For Azure AD:

claude mcp add mssql-readonly -s user \
  -e SQLSERVER_HOST=<server> \
  -e SQLSERVER_DATABASE=<database> \
  -e SQLSERVER_AUTH_MODE=aad-default \
  -e SQLSERVER_ENCRYPT=true \
  -e SQLSERVER_TRUST_CERT=false \
  -e SQLSERVER_DOMAIN_SOURCE_PATH=<path-to-csharp-project> \
  -- node <ABSOLUTE_PATH>/dist/index.js

For SQL auth:

claude mcp add mssql-readonly -s user \
  -e SQLSERVER_HOST=<server> \
  -e SQLSERVER_DATABASE=<database> \
  -e SQLSERVER_USER=<username> \
  -e SQLSERVER_PASSWORD=<password> \
  -e SQLSERVER_ENCRYPT=true \
  -e SQLSERVER_TRUST_CERT=false \
  -e SQLSERVER_DOMAIN_SOURCE_PATH=<path-to-csharp-project> \
  -- node <ABSOLUTE_PATH>/dist/index.js

Step 5: Verify

Run claude mcp list and confirm the mssql-readonly server shows as connected.

Tell the user: Setup complete! You must restart your Claude Code session for the MCP server to load. Start a new session and try "Test the SQL Server connection" or "List all tables in the database".

Note: MCP servers are loaded at session startup. The server will NOT be available in the current session — the user must start a new one.

Note: The MCP server will only be available in new Claude Code sessions, not the current one.