How to Configure Goose SQL Server Data Masking

Published
Length
3 min read

Luis Felipe Dias

All posts

To set up goose sql server data masking, you pair restricted database roles with masking rules. This setup rewrites sensitive fields like emails and card numbers when the agent runs queries. Many teams start by giving their AI agent a shared login with broad read rights. That wide access exposes raw phone numbers and national IDs to every prompt and tool call. When an agent holds too many rights, one bad query can pull entire customer tables. How native goose sql server data masking and roles

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 goose sql server data masking, you pair restricted database roles with masking rules. This setup rewrites sensitive fields like emails and card numbers when the agent runs queries. Many teams start by giving their AI agent a shared login with broad read rights. That wide access exposes raw phone numbers and national IDs to every prompt and tool call. When an agent holds too many rights, one bad query can pull entire customer tables.

How native goose sql server data masking and roles work

SQL Server separates server logins from database users to control who can log in. A database user is the identity of a login when it connects to a database. If a person or agent needs many databases, admins create a login in master first. For one or a few databases, admins can use a contained database user with a password. An admin must enable contained databases on the server and database before using this option. In total, SQL Server supports thirteen types of database users for different login needs.

Once the user exists, admins assign fixed database roles to control read and write actions. You cannot change the permissions built into these fixed database roles. Every role, including the default public role, includes basic rights to connect to the database. Members of the datareader role can read all data from all user tables and views. Members of the datawriter role can add, delete, or change data in all user tables. To block access, admins use deny roles that stop all reads or writes on tables. Avoid the owner role for agents, because it can run maintenance and drop the database.

After setting up the user and role, admins apply column masking rules inside the database. These rules tell the database to hide sensitive columns for users who lack unmask rights. When the Goose agent runs a query, the database checks the user role and masking rules. If a column holds phone numbers or card numbers, the rule rewrites those matched fields.

What native database roles solve and what they miss

Native logins and fixed roles do a good job of controlling who enters the database. Pairing the datareader role with a deny write role stops an agent from changing data. Native column rules also help hide known columns when a standard user runs a select query.

Still, native database setups still leave major operational gaps for teams running AI agents. The standard datareader role grants read access to every user table and view at once. Admins must maintain column rules on every single table across every database by hand. If a developer adds a new table with emails, the agent reads it right away. Also, the Goose agent still needs a database password stored on the local machine. Native roles decide who logs in, not what individual queries do in flight. They do not inspect unstructured text fields where national IDs or card numbers might hide.

Data Masking (Static) + Kubernetes API Server Access: architecture patterns and best practices

Continue reading? Get the full guide.

Free. No spam. Unsubscribe anytime.

Managing credentials and query checks for AI agents

When engineers run local agents like Goose, storing database keys in plain text creates risk. Every machine running the agent holds a credential that can read from your production tables. If you use contained users or master logins, rotating those passwords across teams takes work. You also lack a clear view of which prompt triggered a specific database query.

Teams need a way to inspect query results in flight and rewrite matched fields dynamically. Instead of maintaining rules on every column, runtime checks scan the actual data returning to Goose. When a query returns emails, phone numbers, or card numbers, the check rewrites those fields. This approach works even when sensitive values appear inside free-form text columns or joined views.

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

Should I give the Goose agent the owner role in SQL Server?

No, never give an AI agent the database owner role in your setup. That role allows full maintenance activities and can even drop the entire database. Use a restricted read role and block write actions instead.

How do I limit Goose to a single SQL Server database?

You can create a contained database user that is not tied to a master login. An admin must first enable contained databases on both the server and the target database. This keeps the user identity inside that specific database only.

What types of data should be masked when Goose runs queries?

You should mask structured personal identifiers before the query results reach the agent context window. Common fields to rewrite include emails, phone numbers, credit card numbers, and national IDs. Rewriting these matched fields in results keeps raw values out of model prompts and logs.

The query runs. The emails stay hidden.