How to Configure LangChain SQL Server Read Only Access
You set up langchain sql server read only access by creating a restricted database user. Then you assign that user to a read-only role and pass its credentials to your agent. Teams often start by reusing a broad admin login to build prototypes fast. When an AI agent uses an admin account, one bad prompt can delete tables. A database owner role can even drop the whole database in SQL Server. Scoping the agent down to read-only rights stops those write actions at the database level. How to set u
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.
You set up langchain sql server read only access by creating a restricted database user. Then you assign that user to a read-only role and pass its credentials to your agent. Teams often start by reusing a broad admin login to build prototypes fast. When an AI agent uses an admin account, one bad prompt can delete tables. A database owner role can even drop the whole database in SQL Server. Scoping the agent down to read-only rights stops those write actions at the database level.
How to set up langchain sql server read only access
First, you need to create an identity for the AI agent inside SQL Server. A database user is the identity of a login when it connects to a database. SQL Server supports 13 types of database users for different access needs. If your agent reads from many databases on one instance, use a standard SQL login. That login lives in the master database and maps to a user in each database.
If the agent only needs one or a few databases, use a contained database user. A contained user has a password and does not tie to a master login. An admin must first turn on contained databases on the server and the target database. Every role in the database, including the public role, has basic connect rights by default.
Next, you assign the new database user to the built-in data reader role. Members of this fixed role can read all data from all user tables and views. You can also add the user to the deny data writer role for extra safety. Members of the deny data writer role cannot add, change, or delete any table data. SQL Server does not let you change the permissions assigned to fixed database roles. Once the role is set, you give these user details to your LangChain database tool. The agent translates natural language questions into SQL queries, runs them, and returns the answers.
What native read-only roles solve and what they miss
Native database roles do a great job of blocking direct data changes. If the language model writes an update or drop statement, SQL Server rejects it. This protects your tables from accidental edits or destructive commands generated by the model.
Still, fixed database roles are all-or-nothing across the whole database. The built-in reader role lets the agent read every user table and view. You cannot alter a fixed role to hide specific columns or sensitive tables. You can add a deny data reader role, which blocks all user tables and views. Yet managing table-by-table grants by hand takes a lot of time as schemas change.
Auditor Read-Only Access + Kubernetes API Server Access: architecture patterns and best practices
Continue reading? Get the full guide.
Free. No spam. Unsubscribe anytime.
Native roles also only check who logs in, not what a query does in flight. A read-only user can still pull sensitive emails, phone numbers, card numbers, or national IDs. When the database returns those rows, the raw values go straight into the model prompt. Also, native roles cannot stop an agent from running slow, heavy select queries. A poorly written join from an AI agent can lock resources and slow down production. Finally, sharing a static database password across agent apps makes it hard to track individual requests.
How to scope table access for AI agents
To reduce risk inside SQL Server, avoid the broad reader role for sensitive databases. Instead, create a custom database role and grant select rights only on safe views. You can build views that leave out columns with phone numbers or card numbers. Then you link the agent user only to that custom role.
You should also limit which tables the LangChain agent knows about in its tool settings. Giving the agent a short list of included tables keeps its prompts smaller and safer. Still, app-level table lists are not a security wall on their own. Always back them up with strict SQL Server permissions and runtime query controls.
Add runtime guardrails beside SQL Server
Role grants and network rules are needed setup for SQL Server. Still, an allowed user or agent tool can send a bad delete or read raw customer fields over a valid session. In practice, hoop.dev runs beside the database as a sidecar for your SQL Server and checks TDS traffic without changing how clients log in.
With hoop.dev SQL Server controls, rules refuse banned statements before they run and can hold risky queries for human review. Matched sensitive values in supported result columns are masked before they reach the client. Furthermore, hoop.dev holds SOC 2 Type II and generates evidence for HIPAA, PCI DSS, SOX, and GDPR audits. See the source on GitHub.
Frequently asked questions
Can I edit the built-in data reader role to hide one table?
No, SQL Server does not let you change permissions on fixed database roles. To read only specific tables, create a custom role and grant select rights per table.
Should I use a master login or a contained user for my agent?
Use a contained database user if the agent only reads from one or two databases. Use a login in the master database if the agent needs many databases on one instance.
Does a read-only database user stop the agent from seeing sensitive columns?
No, a standard read-only role can read all columns in all user tables and views by default. To hide emails or card numbers, you need custom views or runtime data masking that rewrites matched fields in results.
Related guides
In this post