MSSQL MCP Server

Instructions

Implementation Reference

• src/index.ts:150-168 (handler)

  The core handler function that processes the query arguments, establishes a connection pool if needed, executes the SQL query using mssql, and returns the results as a formatted JSON string in the MCP content format.

  async handleQuery(args: QueryArgs): Promise<{ content: Array<{ type: string; text: string }> }> {
    try {
      const config = this.getConnectionConfig(args);
      const pool = await this.getPool(config);
      const result = await pool.request().query(args.query);
  
      return {
        content: [
          {
            type: 'text',
            text: JSON.stringify(result.recordset, null, 2),
          },
        ],
      };
    } catch (error) {
      const message = error instanceof Error ? error.message : String(error);
      throw new McpError(ErrorCode.InternalError, `Database error: ${message}`);
    }
  }

• src/index.ts:177-222 (schema)

  The JSON input schema for the 'query' tool as returned in ListTools, defining properties for connection parameters and the required SQL query with validation rules via oneOf.

  inputSchema: {
    type: 'object',
    properties: {
      connectionString: {
        type: 'string',
        description: 'Full connection string (alternative to individual parameters)',
      },
      host: {
        type: 'string',
        description: 'Database server hostname',
      },
      port: {
        type: 'number',
        description: 'Database server port (default: 1433)',
      },
      database: {
        type: 'string',
        description: 'Database name (default: master)',
      },
      username: {
        type: 'string',
        description: 'Database username',
      },
      password: {
        type: 'string',
        description: 'Database password',
      },
      query: {
        type: 'string',
        description: 'SQL query to execute',
      },
      encrypt: {
        type: 'boolean',
        description: 'Enable encryption (default: false)',
      },
      trustServerCertificate: {
        type: 'boolean',
        description: 'Trust server certificate (default: true)',
      },
    },
    required: ['query'],
    oneOf: [
      { required: ['connectionString'] },
      { required: ['host', 'username', 'password'] },
    ],
  },

• src/index.ts:171-226 (registration)

  Registers the 'query' tool in the MCP server's ListToolsRequest handler by returning it in the tools array with name, description, and input schema.

  this.server.setRequestHandler(ListToolsRequestSchema, (_request: ListToolsRequest) =>
    Promise.resolve({
      tools: [
        {
          name: 'query',
          description: 'Execute a SQL query on a MSSQL database',
          inputSchema: {
            type: 'object',
            properties: {
              connectionString: {
                type: 'string',
                description: 'Full connection string (alternative to individual parameters)',
              },
              host: {
                type: 'string',
                description: 'Database server hostname',
              },
              port: {
                type: 'number',
                description: 'Database server port (default: 1433)',
              },
              database: {
                type: 'string',
                description: 'Database name (default: master)',
              },
              username: {
                type: 'string',
                description: 'Database username',
              },
              password: {
                type: 'string',
                description: 'Database password',
              },
              query: {
                type: 'string',
                description: 'SQL query to execute',
              },
              encrypt: {
                type: 'boolean',
                description: 'Enable encryption (default: false)',
              },
              trustServerCertificate: {
                type: 'boolean',
                description: 'Trust server certificate (default: true)',
              },
            },
            required: ['query'],
            oneOf: [
              { required: ['connectionString'] },
              { required: ['host', 'username', 'password'] },
            ],
          },
        },
      ],
    })
  );

• src/index.ts:228-245 (registration)

  Registers the handling of CallToolRequest for the 'query' tool by checking the name, validating arguments, and delegating to the handleQuery method.

  this.server.setRequestHandler(
    CallToolRequestSchema,
    async (
      request: CallToolRequest
    ): Promise<{ content: Array<{ type: string; text: string }> }> => {
      const params = request.params as { name: string; arguments: unknown };
  
      if (params.name !== 'query') {
        throw new McpError(ErrorCode.MethodNotFound, `Unknown tool: ${params.name}`);
      }
  
      if (!isValidQueryArgs(params.arguments)) {
        throw new McpError(ErrorCode.InvalidRequest, 'Invalid query arguments');
      }
  
      return this.handleQuery(params.arguments);
    }
  );

• src/index.ts:25-72 (helper)

  Type guard function to validate that the tool arguments conform to the QueryArgs interface, used in the CallTool handler.

  const isValidQueryArgs = (args: unknown): args is QueryArgs => {
    const candidate = args as Record<string, unknown>;
  
    if (typeof candidate !== 'object' || candidate === null) {
      return false;
    }
  
    // Query is required
    if (typeof candidate.query !== 'string') {
      return false;
    }
  
    // Either connectionString OR (host + username + password) must be provided
    if (candidate.connectionString !== undefined) {
      if (typeof candidate.connectionString !== 'string') {
        return false;
      }
    } else {
      if (typeof candidate.host !== 'string') {
        return false;
      }
      if (typeof candidate.username !== 'string') {
        return false;
      }
      if (typeof candidate.password !== 'string') {
        return false;
      }
    }
  
    // Optional parameters
    if (candidate.port !== undefined && typeof candidate.port !== 'number') {
      return false;
    }
    if (candidate.database !== undefined && typeof candidate.database !== 'string') {
      return false;
    }
    if (candidate.encrypt !== undefined && typeof candidate.encrypt !== 'boolean') {
      return false;
    }
    if (
      candidate.trustServerCertificate !== undefined &&
      typeof candidate.trustServerCertificate !== 'boolean'
    ) {
      return false;
    }
  
    return true;
  };