How to Configure OpenHands SQL Server Data Masking

Published
Length
3 min read

Luis Felipe Dias

All posts

You set up openhands sql server data masking by pairing strict database roles with field rewriting. This setup rewrites matched fields like emails and card numbers in query results. Many teams give coding agents shared database accounts to speed up testing. When those accounts have broad permissions, an agent can read every table or drop data. Scoping the database user is the first step toward safe agent queries. How openhands sql server data masking works with native roles SQL Server splits

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 openhands sql server data masking by pairing strict database roles with field rewriting. This setup rewrites matched fields like emails and card numbers in query results. Many teams give coding agents shared database accounts to speed up testing. When those accounts have broad permissions, an agent can read every table or drop data. Scoping the database user is the first step toward safe agent queries.

How openhands sql server data masking works with native roles

SQL Server splits access into server logins and database users. A database user is the identity of a login inside one specific database. When an agent needs access to many databases, admins create a login in master. When an agent needs just one database, admins can use a contained database user. This user has its own password and does not tie back to master. An admin must turn on contained databases on the server and database first. SQL Server supports thirteen distinct types of database users for different access needs.

After creating the user, admins assign fixed database roles to control table access. Every role, including the public role, includes basic permission to connect. The fixed datareader role lets the agent read all data from all user tables. It also lets the agent read from all user views in that database. You cannot change the permissions built into these fixed database roles. If an agent gets the datawriter role, it can add, change, or delete rows. If an agent gets the owner role, it can even drop the whole database.

Why native roles leave gaps for agent queries

Fixed database roles work at the whole database level, not the column level. A member of the datareader role reads every row and column in user tables by default. You can block all reads with the denydatareader role, which hides tables and views. You can also block writes with the denydatawriter role to stop data changes. Still, these fixed roles cannot hide specific fields while leaving other fields open.

When OpenHands runs a query to fix a bug, it reads raw table rows. If that table holds phone numbers or national IDs, the query returns them. Native login rules only check who logs in to the database server. They do not inspect individual statements or rewrite sensitive fields in flight. Teams also struggle when they manage column rules across dozens of tables by hand. When developers add new tables, someone must remember to update the masking rules. Without runtime checks, a single broad select statement pulls raw card numbers into agent logs.

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

Continue reading? Get the full guide.

Free. No spam. Unsubscribe anytime.

Planning a safe workflow for coding agents

Start by creating a dedicated database user just for your OpenHands agent. Use a contained database user if the agent only checks one database. Assign the denydatawriter role so the agent cannot alter or delete table rows. Keep the agent out of the owner role to protect your database structure.

Next, define which data types must be rewritten before results reach the agent. Focus on clear patterns such as emails, phone numbers, card numbers, and national IDs. Rewriting matched fields in results lets the agent test queries with realistic data shapes. The agent gets valid row counts and types while sensitive values stay masked. Finally, review query logs often to see which tables the agent queries most.

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 the fixed datareader role mask emails or card numbers by itself?

No, the fixed datareader role grants read access to all user tables and views. You cannot alter the permissions inside a fixed role to hide specific columns. You need extra masking rules to rewrite emails or card numbers in results.

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

Use a contained user if OpenHands only needs access to one or two databases. This keeps the user inside the target database instead of the master database. Use a login in master only when the agent must query many databases.

How do I stop OpenHands from changing data in SQL Server?

Add the agent user to the fixed datareader role and the denydatawriter role. The denydatawriter role blocks the user from adding, changing, or deleting any table data. Also make sure the user is never a member of the owner role.

The query runs. The emails stay hidden.