MSSQL MCP Server

Instructions

Implementation Reference

• src/index.ts:43-90 (handler)

  The main handler function for the 'connect-database' tool. It validates the input configuration, sanitizes the database name, parses the config using Zod schema, establishes the connection via MSSQLManager, and returns success or error messages.

  async ({ server, port, database, user, password, encrypt, trustServerCertificate }) => {
    try {
      // 驗證連接配置
      const configValidation = validateConnectionConfig({ server, user, password, port })
      if (!configValidation.isValid) {
        return {
          content: [
            {
              type: 'text' as const,
              text: `連接配置錯誤:\n${configValidation.errors.join('\n')}`
            }
          ]
        }
      }
  
      const config = DatabaseConfigSchema.parse({
        server,
        port,
        database: database ? sanitizeDatabaseName(database) : undefined,
        user,
        password,
        options: {
          encrypt,
          trustServerCertificate,
        }
      })
  
      await mssqlManager.connect(config)
  
      return {
        content: [
          {
            type: 'text' as const,
            text: `成功連接到 MSSQL 伺服器: ${server}${database ? `\n目前資料庫: ${database}` : '\n未指定資料庫'}`
          }
        ]
      }
    } catch (error) {
      return {
        content: [
          {
            type: 'text' as const,
            text: `連接失敗: ${formatError(error)}`
          }
        ]
      }
    }
  }

• src/index.ts:33-41 (schema)

  Input schema definition for the connect-database tool, specifying parameters like server, port, database, credentials, and connection options using Zod.

  inputSchema: {
    server: z.string().describe('MSSQL 伺服器位址'),
    port: z.number().optional().default(1433).describe('連接埠號 (預設: 1433)'),
    database: z.string().optional().describe('資料庫名稱 (可選)'),
    user: z.string().describe('使用者名稱'),
    password: z.string().describe('密碼'),
    encrypt: z.boolean().optional().default(true).describe('是否加密連接 (預設: true)'),
    trustServerCertificate: z.boolean().optional().default(false).describe('是否信任伺服器憑證 (預設: false)'),
  }

• src/index.ts:28-91 (registration)

  Registration of the 'connect-database' tool with the MCP server, including title, description, input schema, and handler function.

  server.registerTool(
    'connect-database',
    {
      title: '連接資料庫',
      description: '連接到 MSSQL 資料庫伺服器',
      inputSchema: {
        server: z.string().describe('MSSQL 伺服器位址'),
        port: z.number().optional().default(1433).describe('連接埠號 (預設: 1433)'),
        database: z.string().optional().describe('資料庫名稱 (可選)'),
        user: z.string().describe('使用者名稱'),
        password: z.string().describe('密碼'),
        encrypt: z.boolean().optional().default(true).describe('是否加密連接 (預設: true)'),
        trustServerCertificate: z.boolean().optional().default(false).describe('是否信任伺服器憑證 (預設: false)'),
      }
    },
    async ({ server, port, database, user, password, encrypt, trustServerCertificate }) => {
      try {
        // 驗證連接配置
        const configValidation = validateConnectionConfig({ server, user, password, port })
        if (!configValidation.isValid) {
          return {
            content: [
              {
                type: 'text' as const,
                text: `連接配置錯誤:\n${configValidation.errors.join('\n')}`
              }
            ]
          }
        }
  
        const config = DatabaseConfigSchema.parse({
          server,
          port,
          database: database ? sanitizeDatabaseName(database) : undefined,
          user,
          password,
          options: {
            encrypt,
            trustServerCertificate,
          }
        })
  
        await mssqlManager.connect(config)
  
        return {
          content: [
            {
              type: 'text' as const,
              text: `成功連接到 MSSQL 伺服器: ${server}${database ? `\n目前資料庫: ${database}` : '\n未指定資料庫'}`
            }
          ]
        }
      } catch (error) {
        return {
          content: [
            {
              type: 'text' as const,
              text: `連接失敗: ${formatError(error)}`
            }
          ]
        }
      }
    }
  )

• src/types.ts:4-16 (schema)

  Zod schema for DatabaseConfig used in the handler to parse and validate the connection configuration before passing to MSSQLManager.connect.

  export const DatabaseConfigSchema = z.object({
    server: z.string(),
    port: z.number().optional().default(1433),
    database: z.string().optional(),
    user: z.string(),
    password: z.string(),
    options: z.object({
      encrypt: z.boolean().optional().default(true),
      trustServerCertificate: z.boolean().optional().default(false),
      connectionTimeout: z.number().optional().default(30000),
      requestTimeout: z.number().optional().default(30000),
    }).optional()
  })

• src/database.ts:14-45 (helper)

  MSSQLManager.connect method called by the tool handler to establish the actual SQL Server connection pool using the 'mssql' library.

  async connect(config: DatabaseConfig): Promise<void> {
    try {
      // 如果已有連接，先關閉
      if (this.pool) {
        await this.disconnect()
      }
  
      this.config = config
      this.currentDatabase = config.database || null
  
      const poolConfig: sql.config = {
        server: config.server,
        port: config.port,
        database: config.database,
        user: config.user,
        password: config.password,
        options: config.options || {
          encrypt: true,
          trustServerCertificate: false,
          connectionTimeout: 30000,
          requestTimeout: 30000,
        }
      }
  
      this.pool = new sql.ConnectionPool(poolConfig)
      await this.pool.connect()
      
      console.log(`已連接到 MSSQL 伺服器: ${config.server}${config.database ? `, 資料庫: ${config.database}` : ''}`)
    } catch (error) {
      throw new Error(`連接資料庫失敗: ${error instanceof Error ? error.message : String(error)}`)
    }
  }