How to Configure LangChain SQL Server Prevent Delete

Published
Length
3 min read

Luis Felipe Dias

All posts

You set up a langchain sql server prevent delete rule with database roles. Give the agent a SQL user that only has read rights. An AI agent turns plain text into SQL queries and runs them. Many teams start by giving the agent a broad database owner role. That shortcut lets a bad prompt wipe out rows or whole tables. How to set up langchain sql server prevent delete Start by creating a dedicated login and database user for your AI app. In SQL Server, a database user represents the login inside

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 a langchain sql server prevent delete rule with database roles. Give the agent a SQL user that only has read rights. An AI agent turns plain text into SQL queries and runs them. Many teams start by giving the agent a broad database owner role. That shortcut lets a bad prompt wipe out rows or whole tables.

How to set up langchain sql server prevent delete

Start by creating a dedicated login and database user for your AI app. In SQL Server, a database user represents the login inside one database. If the agent needs many databases, create a login in master first. If the agent needs just one database, use a contained database user. An admin must turn on contained databases on the server and database first.

Next, assign the right fixed database roles to that new user. Fixed database roles have set rights that you cannot change. Add your agent user to the data reader role. Members of this role can read all data from all user tables. They can also read from all user views in the database.

Never put an AI agent in the database owner role. That role can run all maintenance tasks and even drop the database. Do not add the agent to the data writer role either. The data writer role can add, change, or delete data in tables. To be safe, add the user to the deny data writer role. Members of that deny role cannot add, change, or delete any data. Even if another role grants write rights, the deny role blocks deletes.

Why prompts alone cannot stop destructive queries

Some builders try to stop deletes by writing rules in the system prompt. They tell the model to only write select statements. Prompt rules are helpful, but they are not a real security boundary. An agent decides which tools to call based on user input. A clever user prompt or a model mistake can ignore prompt text. If the database user has delete rights, the server will run the delete.

Database roles are much stronger because the server enforces them every time. When the agent sends a delete command, SQL Server checks the user role. If the user is in the deny data writer role, the query fails. The table stays safe even when the model writes a bad command.

SQL Injection Prevention + Kubernetes API Server Access: architecture patterns and best practices

Continue reading? Get the full guide.

Free. No spam. Unsubscribe anytime.

What native database roles solve and what they miss

Native SQL Server roles solve the big problem of unauthorized writes and deletes. They control who can log in and which tables they can change. A read-only user with deny write rules cannot drop tables or delete rows. This is the foundation for safe AI database tools.

Still, static database roles leave an operational gap during live sessions. The data reader role lets the agent read all rows in all user tables by default. It does not check what individual queries do in flight. The agent can still read sensitive columns like emails, phone numbers, or card numbers. Those raw values go straight into the model prompt and chat logs. Also, what if an agent needs to update one status column without deleting rows? Fixed roles are all or nothing across user tables. And static passwords stored in app settings can leak or get shared. Native roles decide who logs in, not how each statement behaves at runtime.

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

Can system prompts stop a LangChain agent from deleting SQL Server data?

No, system prompts are only text instructions and are not a hard block. A model error or bad input can still make the agent send a delete query. You must enforce read-only rules at the database or query layer.

Which SQL Server role blocks an AI agent from deleting rows?

Assign the agent user to the fixed data reader role for read rights. Then add the user to the fixed deny data writer role. This blocks the user from adding, changing, or deleting data in user tables.

How do I let an agent read tables without exposing sensitive data?

Fixed database roles let the user read all columns in all user tables by default. To protect private fields, use dynamic data masking on the query results. Masking rewrites matched fields like emails, phone numbers, and card numbers in results.

The query runs. The emails stay hidden.