How to Configure Haystack SQL Server Approval

Published
Length
3 min read

Luis Felipe Dias

All posts

You set up haystack sql server approval by adding a human check before queries run. You also restrict the database user to fixed read roles. Teams often hook AI pipelines straight to a database using one shared login. When that login has broad rights, a bad prompt can change or drop tables. Even a read role lets the app pull every row from every user table by default. Setting up strict user roles and query checks stops bad queries before they run. How haystack sql server approval works with d

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 haystack sql server approval by adding a human check before queries run. You also restrict the database user to fixed read roles.

Teams often hook AI pipelines straight to a database using one shared login. When that login has broad rights, a bad prompt can change or drop tables. Even a read role lets the app pull every row from every user table by default. Setting up strict user roles and query checks stops bad queries before they run.

How haystack sql server approval works with database roles

A safe setup starts inside the database itself. In SQL Server, a database user represents the identity of a login upon entry. If the app needs many databases, admins create a SQL user with a master login. If it needs one or a few databases, a contained database user works well. The admin must first turn on contained databases on the server and database. SQL Server supports 13 types of database users in total.

Once the user exists, you assign it to a fixed database role. You cannot change the permissions built into these fixed roles. Every role, including the public role, comes with basic connect rights. To block data changes, admins put the pipeline user in the data reader role. Members of this role can read all data from all user tables and views. Admins can also add the deny data writer role to block edits. That role stops the user from adding, changing, or deleting any table data. Avoid the owner role, which can run maintenance tasks and drop the database.

On the app side, Haystack builds queries from user prompts using components in a pipeline. To add an approval step, developers pause the pipeline before the database component runs. The app sends the drafted SQL text to a reviewer in a chat tool. The human checks the statement and clicks approve or reject. Only approved queries move to the next step and hit the database.

What native roles solve and what they miss

Database roles control who logs in and which broad actions they can take. A read role stops an AI agent from dropping tables or changing rows. If a prompt tricks the model into writing a delete statement, the database blocks it.

Still, fixed roles do not check what individual queries do in flight. A user in the data reader role can read every user table and view. If a table holds emails, phone numbers, card numbers, or national IDs, queries return them. You can use the deny data reader role to block table reads and metadata. Yet that blocks the whole database unless you manage table grants by hand.

Approval Chains & Escalation + Kubernetes API Server Access: architecture patterns and best practices

Continue reading? Get the full guide.

Free. No spam. Unsubscribe anytime.

App-side approval steps also have limits when teams scale up. Developers must build and maintain custom pause logic inside every single pipeline. If another team deploys a new script, they might skip the check by mistake. Also, static credentials still sit in app settings on the host machine. Anyone who reads those settings can log in directly and bypass the app check. And once a human approves a read query, the raw results still flow back. Masking is needed so a rule rewrites matched fields in results before the model reads them.

Why query checks belong outside the app code

Relying on app code for query approval mixes safety rules with pipeline logic. Each time you update the pipeline framework, you must test the approval flow again. You also lack one central log that links the reviewer to the exact database run.

Moving approval and data masking to the database path fixes this gap. Every query from any pipeline hits the same check before reaching SQL Server. Reviewers can approve sensitive queries in one place, and masking rules strip out card numbers. The database stays safe even when teams build new AI workflows fast.

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 I change the permissions of the SQL Server data reader role?

No, SQL Server does not let you change permissions on fixed database roles. To limit table reads further, you must grant custom rights or use deny roles.

Should I use a master login or a contained user for Haystack?

Use a contained user if the pipeline only needs one or a few databases. An admin must enable contained databases on the server and database first.

Does an approval step stop the pipeline from pulling sensitive columns?

Approval alone only lets a human allow or block the full SQL query. To hide emails or phone numbers in allowed queries, you need data masking in flight.

The query runs. The emails stay hidden.