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 (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

1. Create a dedicated user

   SQL Server and Azure SQL Managed Instance — create a login in master, then a user for it in your database:

   -- 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:

   CREATE USER cloudthinker WITH PASSWORD = '<strong-password>';
   
2. 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:

   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.

3. 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.
4. 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.

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

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.

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:

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

@tony #report the SQL Server tables you can reach and their columns

Example prompts

@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

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.

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.

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.

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.

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.

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.

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.

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

Tony Agent

Database-focused optimization agent

PostgreSQL Connection

Similar setup for PostgreSQL databases