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

# Microsoft SQL Server

> Connect SQL Server or Azure SQL to CloudThinker for schema discovery, record inspection and aggregation, and approval-gated row changes

Connect your SQL Server or Azure SQL database to let [Tony](/guide/agents/tony) (Database Engineer) discover your schema, read and aggregate records, and make single-row changes you approve one at a time.

The connection reaches your **tables** directly. It cannot run arbitrary SQL, call stored procedures, or change your schema; reads happen without a prompt, and every create, update, and delete asks you first, one call at a time.

## Supported platforms

| Platform                                  | Supported | Notes                                                                              |
| ----------------------------------------- | --------- | ---------------------------------------------------------------------------------- |
| **SQL Server**                            | Yes       | 2016 or later, on-premises or self-managed                                         |
| **Azure SQL Database / Managed Instance** | Yes       | Managed, no version to choose                                                      |
| **SQL Server on Azure VMs / Azure Arc**   | Yes       | 2016 or later                                                                      |
| **SQL Server 2014 and earlier**           | No        | Below the minimum version                                                          |
| **SQL database in Microsoft Fabric**      | No        | Does not accept SQL Server logins, so this connection's credential cannot reach it |

## Prerequisites

* A SQL Server or Azure SQL database reachable from CloudThinker on its SQL port, `1433` by default.
* Permission to create a login or a database user and grant it read access.
* The tables you want the agent to reach in a normal user schema. Objects in `sys` and `INFORMATION_SCHEMA` are never exposed, whatever the user is granted.

## Setup

<Steps>
  <Step title="Create a dedicated user">
    **SQL Server and Azure SQL Managed Instance** — create a login in `master`, then a user for it in your database:

    ```sql theme={null}
    -- In master
    CREATE LOGIN cloudthinker WITH PASSWORD = '<strong-password>';
    -- In your database
    CREATE USER cloudthinker FOR LOGIN cloudthinker;
    ```

    **Azure SQL Database** — create the user directly in your database, with no login in `master`, the form Microsoft recommends because it keeps the database portable:

    ```sql theme={null}
    CREATE USER cloudthinker WITH PASSWORD = '<strong-password>';
    ```
  </Step>

  <Step title="Grant read access to only what the agent should see">
    Grant on the schema that holds the tables you want reachable, or tighter, one table at a time:

    ```sql theme={null}
    GRANT SELECT ON SCHEMA :: dbo TO cloudthinker;
    -- or: GRANT SELECT ON OBJECT::dbo.orders TO cloudthinker;
    ```

    Adding the user to `db_datareader` also works, but it grants read access to every table in the database — a schema or object grant is the better boundary.
  </Step>

  <Step title="Allow network access">
    * **Azure SQL Database and Managed Instance**: add CloudThinker to the server firewall rules.
    * **SQL Server**: allow inbound `1433` from CloudThinker, and confirm the server accepts SQL Server authentication rather than Windows authentication only.
  </Step>

  <Step title="Add the connection in CloudThinker">
    Go to **Connections → Microsoft SQL Server** and fill in the single **Connection string** field:

    ```
    Server=<host>,1433;Initial Catalog=<database>;User ID=cloudthinker;Password=<password>;Encrypt=True;
    ```

    Click **Connect**. CloudThinker opens the connection, reads the tables the user can see, and the **Connected** message reports what it loaded. A failure comes back with the reason SQL Server gave — see [Troubleshooting](#troubleshooting).
  </Step>
</Steps>

## Connection details

One field carries everything: an ADO.NET connection string for the user you created above.

| Keyword                  | What to put                                                                            |
| ------------------------ | -------------------------------------------------------------------------------------- |
| `Server`                 | Host and port in SQL Server's own form, `sql.example.com,1433` — a comma, not a colon  |
| `Initial Catalog`        | The database name. **Required** when the user was created directly in the database     |
| `User ID` / `Password`   | The user created in step 1                                                             |
| `Encrypt`                | `True`                                                                                 |
| `TrustServerCertificate` | Leave it out, or `False`. Set `True` only for a self-signed or internal-CA certificate |

<Warning>
  With `Encrypt=True` and `TrustServerCertificate=False`, Microsoft's client encrypts traffic **only if the server presents a verifiable certificate** — otherwise the connection fails rather than falling back to plaintext. That failure is the most common one on a first connect against a self-hosted server.
</Warning>

## Required permissions

`GRANT SELECT` on the schema (Setup step 2) is enough for schema discovery, reading records, and aggregation. Only if you want the agent to change data:

```sql theme={null}
GRANT INSERT, UPDATE, DELETE ON SCHEMA :: dbo TO cloudthinker;
```

Grant these on the specific schema you want changeable, never database-wide. The grant is the durable boundary: the per-call approval prompt decides whether CloudThinker asks, the grant decides whether SQL Server allows it. A user holding only `SELECT` cannot write, no matter what anyone approves.

## Agent capabilities

Once connected, Tony can:

| Capability            | Description                                                                                 |
| --------------------- | ------------------------------------------------------------------------------------------- |
| **Schema discovery**  | List the reachable tables with their columns                                                |
| **Record inspection** | Read rows with column selection, filtering, sorting, and paging                             |
| **Aggregation**       | Count, sum, average, minimum, and maximum, with grouping and having                         |
| **Row changes**       | Insert a row, or update and delete a row by its primary key — each one after you approve it |

What the connection cannot do, by design: no arbitrary SQL, no stored procedures, no schema changes, and no joins across tables in a single read — each read covers one table.

### Verify the connection

```text theme={null}
@tony #report the SQL Server tables you can reach and their columns
```

### Example prompts

```text theme={null}
@tony #report how many orders were placed per status in the last 30 days
@tony #report the ten most recent rows in dbo.orders
@tony #recommend which columns in dbo.orders look like they need an index
```

## Write access

There is no connection-wide write switch. Every insert, update, and delete is approved individually, in the conversation, before it runs — and needs the matching `INSERT`, `UPDATE`, or `DELETE` grant on the table.

Two things to weigh:

* **Updates and deletes are keyed, not filtered.** The agent addresses one row by its primary key, so a mistyped filter cannot sweep a table. A table without a primary key cannot be updated or deleted through this connection at all.
* **Approval is per call, not per session.** Approving one delete does not approve the next.

If you never want the agent to change data, do not grant `INSERT`, `UPDATE`, or `DELETE`. That is stronger than declining each prompt.

## Troubleshooting

<Accordion title="Login failed for user">
  Confirm the user exists in the right place — a login lives in `master`, a user created with `WITH PASSWORD` lives in your database — and that the server accepts SQL Server authentication. Retype the password rather than pasting it; a stray space or line break fails here.
</Accordion>

<Accordion title="The connection fails on a certificate error">
  The server did not present a certificate the client could verify, and the client refused to continue unencrypted. Install a trusted certificate, or — for a self-signed or internal-CA certificate on a private network — add `TrustServerCertificate=True`. Traffic stays encrypted, but the server's identity is no longer checked.
</Accordion>

<Accordion title="The server is unreachable, or the connection times out">
  Check that `Server` uses a comma (`host,1433`), that Azure SQL firewall rules include CloudThinker, and — for SQL Server — that the firewall allows inbound `1433` and the server is listening on TCP/IP, which is off by default on some installations.
</Accordion>

<Accordion title="The agent cannot find a table it should see">
  The user needs `SELECT` on that table or its schema — a table the user cannot read does not appear at all, and `sys` and `INFORMATION_SCHEMA` are always excluded. Name the table with its schema, for example `dbo.orders`, since an unqualified name can be ambiguous.
</Accordion>

<Accordion title="An update or delete is refused, and the table has no primary key">
  Updates and deletes address exactly one row by its primary key. Without one, the operation is refused; reading and aggregating still work. Add a primary key if you want the agent to change the table.
</Accordion>

<Accordion title="A column never appears in results">
  Some data types are not carried over this connection: `geography`, `geometry`, `hierarchyid`, `json`, `rowversion`, `sql_variant`, `vector`, and `xml`. The table still works; those columns are not returned.
</Accordion>

<Accordion title="The agent will not run the SQL I gave it">
  Expected — this connection has no SQL console and no stored-procedure access. Ask for the result you want, a filtered read or a grouped count, rather than a statement to execute.
</Accordion>

## Security

* **Least privilege** — grant only the permissions the agents need for your use case; start read-only and widen later.
* **Read-only by default** — use read-only credentials unless you want agents to make changes through this connection.
* **Rotate credentials** — rotate keys and tokens on your normal schedule; CloudThinker picks up the new value when you update the connection.
* **Revoke on offboarding** — remove the credential at the provider when you delete a connection or a teammate leaves.

- **Grant the schema, not the database** — scope `SELECT` to the schema holding the tables the agent should reach; everything else stays invisible.
- **Read-only by omission** — withhold `INSERT`, `UPDATE`, and `DELETE` and the connection is permanently read-only, regardless of what is approved in a conversation.

## Related

<CardGroup cols={2}>
  <Card title="Tony Agent" icon="database" href="/guide/agents/tony">
    Database-focused optimization agent
  </Card>

  <Card title="PostgreSQL Connection" icon="https://mintcdn.com/cloudthinker/aLd-ttc-SCW-aFky/images/icons/postgresql.svg?fit=max&auto=format&n=aLd-ttc-SCW-aFky&q=85&s=8bb2ac033d0a2ccbef51154a76e1e819" href="/guide/connections/postgresql" width="24" height="24" data-path="images/icons/postgresql.svg">
    Similar setup for PostgreSQL databases
  </Card>
</CardGroup>