Investigate records in a selected database
Give AI a restricted view of order or processing tables without providing the SQL login password. Keep database selection explicit and limit the rows and columns returned.
Databases · MCP integration
Give AI SQL Server context. Keep database access deliberate.
Connect Microsoft SQL Server to a scoped GateRook MCP endpoint using SQL authentication over TCP. Select the databases and schemas your AI may inspect, keep the login credentials in GateRook, and review write requests before they run in Approval mode.
Connection in GateRook
stage-sqlserver
SQL Server
stage / QA- Name / environment
- stage-sqlserver / stage
- Host / port
- sqlserver.example.test / 1433
- Databases
- OrdersStage
- User / password
- gaterook_reader / enter in GateRook
The route
Your AI requests a tool. GateRook checks the endpoint policy, resolves credentials locally, and calls the configured service.
Understand the request flowAsk your AI
Read up to ten pending orders from OrdersStage through stage-sqlserver. Do not change records or switch databases.
sql_query · arguments
{
"connection": "stage-sqlserver",
"database": "OrdersStage",
"sql": "SELECT TOP (10) id, status, total FROM dbo.orders WHERE status = 'pending' ORDER BY id",
"limit": 10
}Example result
{
"columns": [
"id",
"status",
"total"
],
"rows": [
[
48213,
"pending",
"149.00"
],
[
48214,
"pending",
"89.50"
]
],
"rowCount": 2,
"truncated": false
}Sample configuration and output, not a live connection. Replace example values with your environment. Tool names here are connector names; the endpoint may expose a connection-specific name.
Put context to work
What you can do with SQL Server and AI
Inspect table structure before querying
Use schema discovery and table descriptions to inspect columns, keys and indexes. Schema-qualified names keep the query aligned with the intended database objects.
Review a small data change
Enable sql_execute in Approval mode for a specific workflow. Review the selected database, exact statement and available estimated impact, then verify the result with a separate read and inspect Activity.
From service to MCP
How to connect SQL Server
GateRook must be running on a computer that can reach your service. Open Connections, choose Add connection, and select SQL Server. Give the connection a name and environment.
New to GateRook? Start with the full setup guide.Prepare a SQL login and network access
Use a dedicated SQL-authenticated login and grant only the SELECT permissions needed for the initial workflow. Check the TCP port, selected database access and database user default schema. The computer running GateRook must be able to reach the server.
Add SQL Server and select databases
Open Connections, choose Add connection and SQL Server. Enter the host, port and protected login credentials. Load the database list and select the databases this connection may expose; avoid adding unrelated databases.
Set TLS, schema scope and limits
Keep verify-full and add the private CA when required. Set Default schema to the database user default, normally dbo. Apply the schema allowlist and query limits; use schema-qualified table names for a predictable target.
Test permissions before enabling tools
Run Test connection and review effective permissions and available TLS status. A restricted reader may not be allowed to inspect server TLS metadata; this does not require granting server-state access. Start with read tools and leave sql_execute Off until changes are needed.
Choose the endpoint and tool modes
Add the tested connection to an endpoint. In Tools & policies, enable only the tools needed for the task. Start with Read only where supported; use Approval for any selected mutation and leave other tools Off. A connection alone does not grant an AI client access.
Connect your AI and verify the first call
In Clients, choose your AI app and endpoint, review the connection details, and connect. Try this guide’s example in Playground before asking your AI. Open Activity to inspect the tool, target, decision and returned result. The getting started guide covers this shared flow.
Connection settings
Configure SQL Server in GateRook
These are example values for the connection form. Enter credentials into GateRook’s secret fields, not into an AI prompt. Requirements and alternatives are noted per field.
| Setting | Example | How to use it |
|---|---|---|
Name / environment | stage-sqlserver / stage | Choose SQL Server in Connections. The example names and data are illustrative, not a preconfigured service. |
Host / port | sqlserver.example.test / 1433 | Use the server host and known TCP port. A named instance also needs its actual TCP port; do not enter an instance name in place of the host. |
Databases | OrdersStage | Load and select only the databases needed by the endpoint. If multiple databases are selected, each tool call must name its database explicitly. |
User / password | gaterook_reader / enter in GateRook | Use a SQL login with SELECT grants on the intended objects. Windows integrated authentication and Microsoft Entra authentication are not supported. Keep the password in the protected connection field. |
SSL mode | verify-full | The default encrypts and validates the server certificate and host name. require encrypts without certificate validation; disable does not request encryption, although the server may require it. |
CA certificate (PEM) | Your private CA certificate, when required | Use this with verify-full when the server certificate chains to a private CA. Leave it empty to use the system trust store. |
Default schema / schema allowlist | dbo / dbo | The configured default schema must match the database user default. Qualify table names as schema.table. Gateway allowlist comparisons are exact even when the database collation is case-insensitive. |
Statement timeout / pool size / connection timeout | 5000 ms / 2 / 5000 ms | The statement timeout may be tightened by policy; unset follows the environment policy. Pool size defaults to two, with a supported range of one to ten. Connection timeout defaults to 5000 ms. |
Know what you expose
SQL Server tools and access boundaries
The connector determines what is possible. Endpoint modes and connection policies determine what a particular AI client may use.
| Tool | Purpose | Access behavior |
|---|---|---|
sql_list_schemas | List schemas and tables visible within the selected database and configured allowlists. Include database when the connection has several selected databases. | Read only |
sql_describe_table | Read a permitted table’s columns, types, indexes and foreign keys. Use a schema-qualified table name in the selected database. | Read only |
sql_query | Run one analyzed SELECT or WITH ... SELECT using supported built-in functions. GateRook rolls the transaction back and enforces row, response-size and timeout limits. | Read only |
sql_execute | Request one analyzed SQL change. Approval retains the selected database and exact SQL. DML impact estimates use SHOWPLAN_XML when the login has permission. | Approval by default |
Before you enable access
- SQL Server does not provide JDBC read-only transactions. GateRook analyzes reads and rolls them back, but a dedicated SELECT-only database login is the server-enforced protection; views and computed expressions still have their server-defined behavior.
- Only selected databases are available. Three- and four-part object names are refused: supply database in the call and use schema.table rather than cross-database or linked-server names.
- Read queries must be one analyzed SELECT or WITH ... SELECT with supported built-in functions. Unknown functions are refused even with approval. Stored procedures, session and transaction commands, temporary objects, sequence advancement, external rowsets and OUTPUT clauses are not supported.
- Writes require approval by default and run one analyzed statement. Keep sql_execute in Approval mode to preserve human review; explicit Bypass removes that approval step but not the remaining policies or database grants.
- DML impact estimation uses SHOWPLAN_XML without executing the change. It requires SHOWPLAN permission; without a usable plan, the approval reports that the estimate is unavailable.
- Default schema must match the database user default. With a restricted table allowlist and a non-dbo default schema, qualify table names explicitly. Gateway allowlist comparisons remain exact regardless of the server collation.
- SQL authentication is supported; Windows integrated authentication, Microsoft Entra authentication and in-app password rotation are not. Returned data may reach your chosen AI provider, so scope queries and database grants accordingly.
GateRook stores local connection secrets encrypted and resolves them for connectors. Allowed tool results are returned to your AI client and may be processed by its provider. Scope the data you expose as well as the actions you allow.
Practical answers
SQL Server connection questions
Use Test connection to isolate service access problems. Use Playground and Activity to inspect endpoint permissions and tool calls.
Can I use Windows or Microsoft Entra authentication?
No. The current connector uses SQL authentication with a SQL login and password. It connects over TCP using the configured host and port.
Can one connection expose several SQL Server databases?
Yes. Load and select the intended databases during configuration. A tool call must include database when more than one is selected. Approvals retain that database so the reviewed statement cannot silently target another database.
Why is a SELECT with my own function refused?
SQL Server has no JDBC read-only transaction to contain an unknown function. GateRook only admits analyzed SELECT reads using supported built-in functions; approval does not make an unknown function eligible.
Why is the approval’s affected-row estimate unavailable?
The estimate uses SHOWPLAN_XML and requires SHOWPLAN permission for the selected database. If GateRook cannot obtain a plan, it says the estimate is unavailable. An estimate is not a guarantee of how many rows execution will affect.
Why does Test connection report a default-schema mismatch?
Set Advanced → Default schema to the actual default for that database user, normally dbo. GateRook verifies the value when borrowing a database connection. Use qualified names such as dbo.orders rather than relying on SQL Server fallback name lookup.
Can I connect to a named instance?
Yes, when you know the instance’s TCP port. Configure the host and port directly. Windows integrated authentication and instance-name discovery are not part of this connection flow.
Keep exploring
Connect the rest of your stack
Oracle Database
Connect Oracle Database 19c and later to AI through MCP. Inspect schemas, run bounded read queries, protect credentials, and review SQL changes before execution.
PostgreSQL
Connect PostgreSQL to GateRook step by step with screenshots. Configure MCP access, run read queries, approve an UPDATE and inspect the full Activity audit trail.
MySQL
Connect MySQL to GateRook step by step with screenshots. Configure MCP access, run read queries, approve an UPDATE and follow every call in Activity.
Your infrastructure, on your terms
Bring SQL Server into the conversation.
Start with one connection. Choose what your AI can do.