> ## Documentation Index
> Fetch the complete documentation index at: https://docs.ocient.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Ocient MCP Server

> Connect AI agents to Ocient with the Ocient MCP Server. Discover schemas, execute SQL statements, and extend the tool surface with user-defined MCP tools.

export const Python = "Python®";

export const OKTA = "OKTA®";

export const Ocient = "Ocient®";

export const Google = "Google®";

The Model Context Protocol (MCP) is an open standard that gives AI agents a consistent way to discover and invoke tools. The {Ocient} MCP Server implements this standard for the Ocient System. Using the Ocient MCP Server, AI agents and AI-powered code editors can explore schemas and tables, execute SQL statements, and invoke custom tools that you define in the database using SQL.

Ocient distributes the MCP server as the {Python} package `ocientmcp`. The package works with any client that implements the MCP specification. The server returns all results as structured JSON.

<Info>
  A single Ocient MCP Server instance connects to one Ocient System. To use AI agents with multiple Ocient Systems, execute one server instance for each system.
</Info>

## Ocient MCP Server and Its Tools

The Ocient MCP Server delegates tool discovery and tool execution to the connected Ocient System. The database stores the definitions of all MCP tools — both the system tools that ship with the Ocient System and the user-defined tools that you create. Also, the server can discover them dynamically at runtime by querying the `sys.mcp_tools` system catalog table.

This architecture has these characteristics:

* The server itself defines only the `execute_statement` tool. The server discovers all other tools by querying the `sys.mcp_tools` system catalog table, exposes them to MCP clients as standard MCP tools, and executes them using the `CALL MCP TOOL` SQL statement.
* New user-defined tools become available to MCP clients immediately after you create them, without a server upgrade or restart.
* The Ocient System governs all tools with standard access control privileges.
* Because all tools are remote, no tools are available while the connection to the Ocient System is down. In this case, the server returns a clear connection-failure error as tool output so that the AI agent can report the issue.

## Prerequisites

* Access to an Ocient System with a database user account.
* The [uv](https://docs.astral.sh/uv/) Python package manager, which provides the `uvx` command.
* Network connectivity from the machine that executes the server to the SQL port of the Ocient System.

## Deployment Models

The `ocientmcp` package provides three entrypoints.

| **Entrypoint** | **Transport** | **Description** |
| - | - | - |
| `ocientmcp-stdio` | Stdio | Executes the server as a local subprocess of the MCP client. Each client process communicates with one server process, and each server process maintains one Ocient connection. |
| `ocientmcp-http` | Streamable HTTP | Executes the server as a remote service that multiple MCP clients share. Each connected client has its own logical Ocient connection and session. |
| `ocientmcp-http-proxy` | Stdio proxy to Streamable HTTP | Wraps a remote Streamable HTTP server in a local Stdio subprocess for MCP clients that do not support the Streamable HTTP transport natively. |

The server supports the Stdio and Streamable HTTP transports of the current MCP specification. The server does not support the legacy HTTP with Server-Sent Events (SSE) transport.

### Deploy Locally with Stdio

Configure your MCP client to launch the `ocientmcp-stdio` entrypoint. The server supports two authentication modes and determines its one Ocient connection from environment variables in this order:

1. If you set the `OCIENT_DSN` environment variable, the server connects using the full Data Source Name (DSN) and ignores the SSO environment variables.
2. Otherwise, the server requires the `OCIENT_HOSTS` and `OCIENT_DATABASE` environment variables and connects using single sign-on (SSO).

**SSO Mode (recommended)**

On the first launch, your browser opens to the SSO identity provider of your organization, such as {OKTA} or {Google}. After you authenticate, the server caches the security token at `~/.cache/ocientmcp/mcp-token-cache.json` so that subsequent launches reconnect silently.

This example shows an MCP client configuration that connects with SSO.

```json JSON theme={null}
{
  "mcpServers": {
    "ocient": {
      "command": "uvx",
      "args": ["--from", "ocientmcp", "ocientmcp-stdio"],
      "env": {
        "OCIENT_HOSTS": "host1.ocient.com,host2.ocient.com",
        "OCIENT_DATABASE": "mydb"
      }
    }
  }
}
```

SSO mode supports these optional environment variables.

| **Environment Variable** | **Default** | **Description** |
| - | - | - |
| `OCIENT_PORT` | `4050` | The SQL port of the Ocient System. |
| `OCIENT_TLS` | `unverified` | The Transport Layer Security (TLS) mode for the connection: `off` (off), `unverified` (unverified), or `on` (on). |
| `OCIENT_IDENTITY_PROVIDER` | Server-configured | The name of the SSO identity provider, for example, `okta` for Okta or `google` for Google. |

You can also pass these options as the `--port`, `--tls`, and `--identity-provider` command-line options, respectively.

<Info>
  If a cached SSO token expires, delete the `~/.cache/ocientmcp/mcp-token-cache.json` file and restart the server to authenticate again.
</Info>

**DSN Mode**

This example shows an MCP client configuration that connects with a DSN.

```json JSON theme={null}
{
  "mcpServers": {
    "ocient": {
      "command": "uvx",
      "args": ["--from", "ocientmcp", "ocientmcp-stdio"],
      "env": {
        "OCIENT_DSN": "ocient://username:password@host:port/database"
      }
    }
  }
}
```

The server opens the connection on the first tool invocation and reuses the connection for all subsequent calls. To connect to a different database, update the environment variables and restart the server.

### Deploy Remotely with Streamable HTTP

A database administrator starts the server with the `ocientmcp-http` entrypoint and an authentication file. If you omit the `--auth-file` option, the server looks for the file at `/var/opt/ocient/mcp/auth.yaml`. For details about the authentication file, see [Authentication](#authentication).

```shell Shell theme={null}
uvx ocientmcp-http --auth-file /var/opt/ocient/mcp/auth.yaml
```

Users then configure their MCP clients with the URL of the remote server and an API key from the authentication file:

* If the MCP client supports Streamable HTTP natively, configure the client directly with the server URL and pass the API key as a Bearer token.
* If the MCP client supports only the Stdio transport, configure the `ocientmcp-http-proxy` entrypoint with the `--url` option and pass the API key in the `OCIENT_MCP_API_KEY` environment variable.

This example shows an MCP client configuration that uses the proxy entrypoint.

```json JSON theme={null}
{
  "mcpServers": {
    "ocient": {
      "command": "uvx",
      "args": [
        "--from", "ocientmcp",
        "ocientmcp-http-proxy",
        "--url", "http://localhost:8000/mcp"
      ],
      "env": {
        "OCIENT_MCP_API_KEY": "secret_key_abc_123"
      }
    }
  }
}
```

## Authentication

### Stdio Authentication

With the Stdio transport, credentials flow through the environment. The server connects with a DSN in this format when you set the `OCIENT_DSN` environment variable.

```none Text theme={null}
ocient://username:password@host:port/database
```

If you do not set `OCIENT_DSN`, the server connects using browser-based SSO with the `OCIENT_HOSTS` and `OCIENT_DATABASE` environment variables. For the optional SSO environment variables, see [Deploy Locally with Stdio](#deploy-locally-with-stdio).

The server does not log or echo the DSN string because the DSN contains the username and password.

### Streamable HTTP Authentication

With the Streamable HTTP transport, a database administrator configures a YAML authentication file and starts the server with the `--auth-file` option pointing to that file. The file maps API keys to connection credentials, where each API key maps to one of these values.

* A DSN connection string.
* An object with a `dsn` key for SSO access and a `token_source` key with the path of the JSON file for the SSO token. Only machine-to-machine SSO is supported for the Streamable HTTP transport, and the Google identity provider is supported for SSO access.

```yaml YAML theme={null}
# /var/opt/ocient/mcp/auth.yaml

# DSN access
secret_key_abc_123: ocient://user1:pass1@host:port/db1
secret_key_abc_456: ocient://user2:pass2@host:port/db2

# SSO access
secret_key_sso_123:
  dsn: ocient://user:password@host:port/database?handshake=sso&identityprovider=google_sso
  token_source: /path/to/token_source.json
```

Administrators can add, modify, and delete keys in the authentication file to manage access.

Follow these security practices for remote deployments:

* The authentication file must have `600` file permissions because the file contains plain-text DSNs. The server validates the file permissions at startup.
* API keys are Bearer tokens over HTTP. Use TLS for production deployments. The server logs a warning if it executes over HTTP without TLS.

## Connection Behavior

The server can manage connections automatically with these actions:

* For the Stdio transport, a single DSN or SSO configuration maps to one persistent connection. The server opens the connection on the first tool invocation and reuses it for all subsequent calls.
* For the Streamable HTTP transport, each API key maps to one set of connection credentials, and each connected client has its own logical connection and session. Because multiple connections can be open concurrently, idle connections have a 30-minute time-to-live, after which the server closes them. The server reconnects automatically on the next tool invocation.
* If the Ocient connection goes down during a session, the server returns a connection-failure error as tool output instead of a transport-level failure, so that the AI agent can inform you of the issue.

## Tool Reference

### Static Tools

The server defines one static tool named `execute_statement` in its own codebase. This tool is always available while the server executes.

| **Tool** | **Arguments** | **Description** |
| - | - | - |
| `execute_statement` | `statement`, `parameters` (optional) | Executes an arbitrary SQL query or command against the connected Ocient System and returns the result set or row count as structured data. The optional `parameters` argument specifies values for a parameterized SQL statement. |

On SQL errors such as syntax errors, permission errors, or missing objects, the `execute_statement` tool returns a descriptive tool error that contains the Ocient error message instead of a transport-level failure. The error messages are descriptive enough for an AI agent to self-correct and retry.

### Dynamic Tool Discovery

The server discovers all other tools dynamically by querying the `sys.mcp_tools` system catalog table and exposes them to MCP clients as standard MCP tools. The MCP name of each discovered tool is the schema and tool name joined by two underscores. For example, the system tool `show_tables` appears to MCP clients as `sys__show_tables`, and a user-defined tool `analytics.busiest_hours` appears as `analytics__busiest_hours`. AI agents invoke discovered tools directly, without a separate discovery step.

When an AI agent invokes a discovered tool, the server executes the tool in the database using the `CALL MCP TOOL` SQL statement with the privileges of the connected user.

### System Tools

System tools ship with the Ocient System in the `sys` schema and belong to the `System Information` toolset. These tools are always visible, and you cannot drop them. Every user has the EXECUTE privilege on system tools by default, and a security administrator can revoke and regrant this privilege. The system scopes results to the privileges of the caller.

| **Tool** | **Arguments** | **Description** |
| - | - | - |
| `show_columns` | `schema` for a schema or `table_name` for a table | Shows all columns in the specified schema or table. |
| `show_schemas` | None | Shows all user-defined schemas in the database. |
| `show_system_schemas` | None | Shows the system schemas in the database. |
| `show_system_tables` | None | Shows all system tables and views (`sys` and `information_schema`). |
| `show_tables` | `schema` (optional) | Shows all user-defined tables in the database, optionally filtered by schema. |
| `show_views` | `schema` (optional) | Shows all user-defined views in the database, optionally filtered by schema. |

### User-Defined MCP Tools

You can extend the MCP tool surface with your own tools. A user-defined MCP tool is a self-contained SQL definition that the Ocient System stores as a standard metadata object. After you create a tool, any connected MCP client can discover and invoke it by name, without knowing the underlying query logic.

Manage user-defined tools with these SQL statements. For the complete syntax, parameters, and examples, see [Model Context Protocol Tools](/model-context-protocol-tools).

| **SQL Statement** | **Description** |
| - | - |
| `CREATE MCP TOOL` | Creates a user-defined MCP tool from a SQL definition. |
| `CALL MCP TOOL` | Executes a user-defined or system MCP tool with the specified arguments. |
| `DROP MCP TOOL` | Removes a user-defined MCP tool. |
| `EXPORT MCP TOOL` | Returns the `CREATE MCP TOOL` SQL statement that recreates a user-defined MCP tool. |

This example creates a tool that lists the user-defined tables within a schema. The `DESCRIPTION` clause provides the text that MCP clients display to AI agents, and the `TOOLSET` clause assigns the tool to a toolset with a string that can contain spaces. The tool body references its arguments with the `@` prefix.

```sql SQL theme={null}
CREATE MCP TOOL my_schema.my_tool(schema VARCHAR)
    DESCRIPTION 'This tool lists user-defined tables within a schema.'
    TOOLSET 'System Information'
    AS BEGIN TOOL
        RETURN SELECT * FROM information_schema.tables
        WHERE table_schema NOT IN ('sys', 'information_schema')
        AND table_type = 'BASE TABLE'
        AND (@schema IS NULL OR table_schema = @schema);
    END TOOL;
```

User-defined MCP tools have these characteristics:

* Tools support the SQL language for their definitions.
* Every tool has a toolset, which groups related tools.
* The `ALTER` statement is not supported for MCP tools. To change a tool, use the `CREATE OR REPLACE MCP TOOL` SQL statement.
* The tool object supports the CREATE, VIEW, DROP, and EXECUTE privileges.
* The `CALL MCP TOOL` SQL statement executes the SQL definition of the tool with the privileges of the caller. There is no privilege escalation.

The `sys.mcp_tools` system catalog table lists all MCP tools in the database, including system tools and user-defined tools, with their arguments, descriptions, toolsets, definitions, and tool types. For details, see [System Catalog](/system-catalog#model-context-protocol).

## Usage Considerations

Consider these caveats for privileges, workload management, limiting result sets, and generated queries:

* The system scopes all tool results to the privileges of the connected database user. For shared or agent-driven deployments, create a dedicated database user with the minimum privileges the agent requires, and revoke the EXECUTE privilege on any system tools you do not want the agent to use. For details, see [Data Control Language (DCL) Statement Reference](/data-control-language-dcl-statement-reference).
* SQL statements that AI agents submit through the MCP server execute like any other SQL statement and participate in workload management. You can use service classes to control the priority of agent-driven queries. For details, see [Workload Management and Service Classes](/workload-management-and-service-classes).
* AI agents can request large result sets. Where possible, define user-defined MCP tools that aggregate or limit results instead of exposing broad queries, and include `LIMIT` clauses in exploratory queries.
* Agent-driven queries appear in the `sys.queries` and `sys.completed_queries` system catalog tables like any other query. For details, see [System Catalog](/system-catalog).

## Limitations

The Ocien MCP Server has these limitations:

* A single server instance connects to one Ocient System. Multi-system routing is not supported.
* With the Stdio transport, connecting to a different database requires a server restart.
* The legacy HTTP with SSE transport is not supported.
* User-defined MCP tools support SQL definitions only.
* MCP tools do not support the `ALTER` statement.
* Only machine-to-machine SSO is supported for the Streamable HTTP transport.
* No tools are available while the connection to the Ocient System is down.

### Related Links

[Data Analytics Workflow Using the Ocient MCP Server](/data-analytics-workflow-using-the-ocient-mcp-server)

[Build an AI Agent with the Ocient MCP Server](/build-an-ai-agent-with-the-ocient-mcp-server)

[Model Context Protocol Tools](/model-context-protocol-tools)

[Ocient Python Module (pyocient)](/ocient-python-module-pyocient)

[Workload Management and Service Classes](/workload-management-and-service-classes)