How to Configure Haystack SQL Server Connect

Published
Length
3 min read

Luis Felipe Dias

All posts

You set up a haystack sql server connect workflow by linking your pipeline to a database user. You give that user a strict, read-only database role so the pipeline only reads data. Many teams start by using a shared admin login to test their AI pipeline fast. That broad role lets generated queries alter tables or read sensitive rows across the whole database. How haystack sql server connect works with database users A database user is the identity of a login when it enters a database. SQL Ser

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 haystack sql server connect workflow by linking your pipeline to a database user. You give that user a strict, read-only database role so the pipeline only reads data. Many teams start by using a shared admin login to test their AI pipeline fast. That broad role lets generated queries alter tables or read sensitive rows across the whole database.

How haystack sql server connect works with database users

A database user is the identity of a login when it enters a database. SQL Server supports thirteen different types of database users for your setup. If your Haystack app reads from many databases on one instance, use a login in master. You then map a SQL user to that login inside each target database.

If your pipeline only needs one or a few databases, you have another option. You can create a contained database user with its own password. This user is not tied to a login in the master database. An admin must first enable contained databases on the server and on the target database.

Once you create the user, you must assign a role with the right permissions. Every role, including the public role, has permission to enter the database. Still, you cannot change the permissions assigned to fixed database roles in SQL Server. You add a user to a fixed role by altering the role to add the member. In Azure Synapse, you run a stored procedure to add the role member instead.

Choosing fixed roles for your pipeline

For a Haystack query pipeline, you should avoid the database owner role at all costs. Members of the owner role can run all maintenance tasks and even drop the database. You should also avoid the data writer role unless your app must change records. That role lets the user add, delete, or change data in all user tables.

Instead, teams place the Haystack user in the fixed data reader role. Members of this role can read all data from all user tables and views. You can pair this with the deny data writer role for extra safety. That role makes sure the user cannot add, modify, or delete any table data. If you want to block all reads, the deny data reader role blocks tables and views. It also blocks the user from reading metadata about those tables and views.

Kubernetes API Server Access + End-to-End Encryption: architecture patterns and best practices

Continue reading? Get the full guide.

Free. No spam. Unsubscribe anytime.

What native roles solve and what they miss

Native SQL Server roles decide which user can log in and read tables. Putting your Haystack user in the data reader role stops accidental writes and table drops. It is a strong first step for any team building a text-to-SQL app.

Still, the fixed data reader role applies to every single user table and view. You cannot edit a fixed role to hide specific columns from a curious model. If a table holds emails, phone numbers, card numbers, or national IDs, the query reads them. Native roles check who logs in, not what each query does in flight. They do not rewrite matched fields in results before the data reaches your model. Teams also have to store the database password on the host running the Haystack app.

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

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

Use a standard login in master if your pipeline reads from many databases on one server. Use a contained user with a password if the app only needs one or a few databases.

Can the fixed data reader role block my pipeline from reading specific columns?

No, members of the fixed data reader role can read all user tables and views. You cannot change the permissions assigned to fixed database roles in SQL Server.

How do I stop a Haystack user from changing data in SQL Server?

Do not place the user in the data writer role or the database owner role. You can also add the user to the deny data writer role to block all changes.

The query runs. The emails stay hidden.