How to Configure SQL Server IAM Authentication

Published
Length
3 min read

Luis Felipe Dias

All posts

To set up sql server iam authentication, you map cloud identities to database users. You then assign those users to fixed database roles to control data reads and writes. Teams often start by linking directory groups to broad server logins. This approach gets engineers into the database fast. Yet broad roles often grant far more power than a daily task needs. One wide role can expose every table to accidental changes or bulk reads. How sql server iam authentication works with database roles I

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 sql server iam authentication, you map cloud identities to database users. You then assign those users to fixed database roles to control data reads and writes. Teams often start by linking directory groups to broad server logins. This approach gets engineers into the database fast. Yet broad roles often grant far more power than a daily task needs. One wide role can expose every table to accidental changes or bulk reads.

How sql server iam authentication works with database roles

In SQL Server, a database user is the identity of a login inside a database. When people need to reach many databases on an instance, teams create a login first. That login lives in the master database and maps to a SQL user. If someone only needs one or a few databases, you can use a contained user. An admin must turn on contained databases on the server and database first. Microsoft supports thirteen types of database users across its database engine.

After you map the identity, you assign permissions through fixed database roles. Every role, including the default public role, holds the basic permission to connect. You cannot change the permissions that come built into fixed database roles. To let a user read data, you add them to the data reader role. Members of that role can read all data from all user tables and views. To let a user change data, you add them to the data writer role. That role lets members add, delete, or change data in all user tables.

Admins can also block access by using fixed deny roles. The deny data reader role stops users from reading user tables, views, or metadata. The deny data writer role stops users from adding, changing, or deleting any rows. At the top level sits the database owner role. Members of the owner role can run all setup and maintenance tasks. They can even drop the entire database in SQL Server.

Why native roles leave a gap during live queries

Mapping external identities to database users solves the first part of access control. It proves who is logging in and removes shared static passwords from team laptops. You no longer need to rotate one master password when an engineer leaves the team. The directory handles the login check before the database session even starts.

Still, native database roles only check identity when the session begins. Once a user lands in the data reader role, every user table opens up. A support engineer checking one bug can query sensitive customer tables just as fast. Those tables often hold raw emails, phone numbers, card numbers, or national IDs. Native role checks do not rewrite those sensitive fields in query results. If a developer has data writer rights to fix one row, risk grows fast. A bad update query without a filter can overwrite an entire production table. Native roles decide who gets in, not what each query does in flight.

K8s API Server Authentication + Service-to-Service Authentication: architecture patterns and best practices

Continue reading? Get the full guide.

Free. No spam. Unsubscribe anytime.

Managing granular permissions at scale

Some teams try to fix this gap by building custom roles per table. They grant select or update rights on single tables instead of using fixed roles. This works for five tables, but it breaks down across hundreds of schemas. Schema changes force admins to update role grants every week. Soon, teams fall back to broad data reader and data writer roles to ship code.

Real safety requires controls that act on individual statements after the login check passes. Teams need a way to inspect queries before the database engine runs them. They also need a way to mask sensitive fields in results on the fly. That separates login identity from what happens inside a live database session.

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, inline in the TDS wire protocol. On every batch an AI agent or engineer runs against production, hoop.dev decides to pass, mask, block, or hold it for human review.

Before a batch hits the server, hoop.dev guardrails refuse disallowed actions with a native TDS error or hold risky statements for approval. On the way back, hoop.dev data masking rewrites sensitive values in supported result columns in memory, and every decision is logged. Explore the MIT-licensed project on GitHub.

Frequently asked questions

What is the difference between a SQL Server login and a database user?

A login lives in the master database and lets someone reach the server instance. A database user is the identity of that login inside a specific database. You can also enable contained database users that do not need a master login.

Can I customize the permissions inside SQL Server fixed database roles?

No, you cannot change the permissions assigned to fixed database roles in SQL Server. For example, the data reader role always grants read access to all user tables. To limit access to specific tables, you must create custom roles or check queries live.

How do I block a user from reading data without removing their login?

You can add the database user to the fixed deny data reader role. Members of this role cannot read any data or metadata from user tables and views. The user can still connect to the database, but table reads will fail.

The query runs. The emails stay hidden.