How to Configure LlamaIndex SQL Server Read Only Access

Published
Length
3 min read

Luis Felipe Dias

All posts

To set up llamaindex sql server read only access, give your agent a restricted database user. Then point your LlamaIndex database wrapper at that user and name the exact tables to read. Many teams start by reusing an admin login or an owner role during testing. That shortcut lets a language model change rows, drop tables, or read every table in the database. How llamaindex sql server read only access works natively LlamaIndex connects to relational databases through a light wrapper around SQL

Your coding agents run with your access.

Claude Code reaches prod with a developer's credentials, and approval prompts get clicked through. hoop.dev checks every query, masks sensitive data, and holds risky changes for a human.

To set up llamaindex sql server read only access, give your agent a restricted database user. Then point your LlamaIndex database wrapper at that user and name the exact tables to read. Many teams start by reusing an admin login or an owner role during testing. That shortcut lets a language model change rows, drop tables, or read every table in the database.

How llamaindex sql server read only access works natively

LlamaIndex connects to relational databases through a light wrapper around SQLAlchemy. Its natural language query engine turns user questions into SQL queries and runs them against your database. The official docs warn that running arbitrary generated SQL is a real security risk. They advise teams to use restricted roles, read-only databases, and sandboxing.

You can scope what the framework sees by passing a list of allowed tables to the query engine. If you leave that list blank, the engine pulls schema context from every table in the database. That extra schema context wastes tokens and shows the model tables it should not query. Even with a table list in Python, you must lock down the database user itself.

In SQL Server, a database user is the identity of a login inside a target database. When an app needs one or a few databases, admins can create a contained database user. A contained user has its own password and does not need a login in the master database. Admins must enable contained databases on the server and the target database first.

For read access, admins often add the user to the fixed database reader role. Members of that role can read all data from all user tables and views. Admins can also add thefixed deny data writer role to block inserts, updates, and deletes. You cannot change the permissions inside fixed database roles, so table-level rules need custom roles instead.

What native roles solve and what they leave open

A dedicated SQL Server user with read-only permissions stops the worst write mistakes. If a prompt causes the agent to build a delete or drop statement, the database rejects it. Giving the app its own user also separates agent traffic from human logins in native server logs. This basic setup is a vital first step for any text-to-SQL project.

Still, the native fixed reader role still lets the user read every user table and view. If a prompt injection bypasses the Python table list, the database will still run the query. The database only checks who logged in and whether that role can read the table. It does not inspect what individual queries do in flight or what columns come back.

Auditor Read-Only Access + Kubernetes API Server Access: architecture patterns and best practices

Continue reading? Get the full guide.

Free. No spam. Unsubscribe anytime.

Customer tables often hold sensitive fields like emails, phone numbers, card numbers, or national IDs. A valid read query from the agent will pull those raw values straight into the model prompt. Static database credentials also sit in app settings or secret stores where scripts can read them. Native roles cannot mask result fields on the fly or pause risky queries for human review.

Why runtime query controls matter for text-to-SQL agents

AI agents do not behave like fixed backend apps with predictable query patterns. Every user question leads to a brand new SQL statement written by a language model. A query might join five large tables and lock up server memory for minutes. Static database roles cannot tell a safe lookup apart from a broad data dump.

Teams running text-to-SQL in production need controls that act on each statement and result set. When a query returns emails or card numbers, data masking rewrites matched fields in results. That way, the agent still answers the question without pulling raw personal data into the prompt. Teams also gain a clear audit log of every generated query tied to the caller identity.

Check SQL Server batches and results on the wire

SQL Server logins and roles decide who connects. They do not check what each TDS batch does or mask sensitive values in returned rows. With hoop.dev, you run an open-source proxy, a sidecar for your SQL Server. It reads plain batches and parameterized queries on the TDS wire while passing Windows login through untouched.

Before a batch hits the server, hoop.dev guardrails check the statement against your rules. Disallowed actions get a native TDS error with your message. On the way back, hoop.dev data masking rewrites sensitive values in supported result columns that match your rules. Every query and decision is logged. Explore the MIT-licensed project on GitHub.

Frequently asked questions

Does the LlamaIndex table list stop the agent from querying other tables?

No, the table list only limits which table schemas LlamaIndex puts into the model prompt. If a user tricks the model into naming another table, SQL Server will run that query unless database permissions block it.

Is the SQL Server fixed reader role safe enough for sensitive databases?

The fixed reader role blocks writes, but it allows reading all user tables and views in the database. To hide specific tables or columns, you must create a custom role and grant select permissions on safe objects only.

How can we keep emails and card numbers out of LlamaIndex prompts?

Database roles alone return full cell values whenever a user has select permission on a column. Using runtime data masking rewrites matched fields like emails, phone numbers, and card numbers in query results before they reach the model.

The query runs. The emails stay hidden.