How to Configure Azure SQL IAM Authentication

Published
Length
3 min read

Luis Felipe Dias

All posts

You set up azure sql iam authentication by linking directory identities to database users. Then you assign those database users to specific roles inside your target database. Many teams start by replacing shared passwords with cloud directory accounts and groups. That change stops password sharing across developers, analysts, and background apps. Still, teams often attach broad fixed roles to these new identities to move fast. Broad roles let any logged-in user read or change every table in the

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 azure sql iam authentication by linking directory identities to database users. Then you assign those database users to specific roles inside your target database. Many teams start by replacing shared passwords with cloud directory accounts and groups. That change stops password sharing across developers, analysts, and background apps. Still, teams often attach broad fixed roles to these new identities to move fast. Broad roles let any logged-in user read or change every table in the database.

How azure sql iam authentication works with database users

In SQL Server and cloud SQL engines, a database user represents a login identity. When a person or app connects to a database, the engine checks this user. For people who need many databases on an instance, a login in master is common. For access to one or a few databases, a contained database user is an option. A contained user lives inside the target database and does not need a master login. An admin must enable contained databases on the server and database for password-based contained users. Microsoft supports 13 types of database users across its SQL engine family.

Once you create the user, you must grant permissions so the person can run queries. All database roles, including the default public role, hold the permission to connect. Beyond connecting, admins usually assign users to fixed database roles to manage table permissions. You cannot change the permissions that belong to these built-in fixed database roles. Instead, you add the database user as a member of the right fixed role.

Choosing fixed roles and avoiding broad permissions

Each fixed role grants a wide set of rights across the whole database. Members of the data reader role can read all data from all user tables. They can also read data from every view in that database. Members of the data writer role can add, delete, or change data in all user tables. If you need to block access, deny roles exist for both reading and writing. The deny reader role stops a user from reading any user tables, views, or metadata. The deny writer role stops a user from adding, changing, or deleting any table data.

At the top sits the database owner role. Members of this owner role can run all setup and maintenance tasks. They can even drop the whole database. Placing a cloud identity into the owner role gives that person total control. Even the standard data reader role exposes every column in every user table by default.

Service-to-Service Authentication + AWS IAM Policies: architecture patterns and best practices

Continue reading? Get the full guide.

Free. No spam. Unsubscribe anytime.

What native identity setup solves and what it misses

Linking cloud identities to database users solves a major login problem for engineering teams. You no longer pass static database passwords around in chat tools or local files. When someone leaves the team, you disable their central cloud account right away. You also know which person or service opened a connection to the database.

Still, native identity checks only decide who can open a session at login time. Once the session starts, fixed roles apply to whole tables rather than individual queries. A user in the data reader role can pull raw emails, phone numbers, and card numbers. A user in the data writer role can accidentally update or delete thousands of rows. Native roles also cannot require a teammate to approve a risky write command before it runs. To control individual statements and mask sensitive fields in results, teams need runtime controls.

Govern SQL Server queries and responses in flight

Table grants decide which SQL Server objects a caller may touch. They do not check query intent or rewrite sensitive values in results across every client and agent. By design, hoop.dev sits inline in the TDS wire protocol next to SQL Server as a sidecar for your SQL Server. On every production batch from an engineer or AI agent, hoop.dev decides to pass, mask, block, or hold it for approval.

With hoop.dev guardrails and hoop.dev data masking, banned statements are refused or held for review, matched sensitive fields in supported result columns are masked in memory, and each decision is logged. Check out the code on GitHub.

Frequently asked questions

What is the difference between a master login and a contained database user?

A master login lives at the server level and works across many databases on an instance. A contained database user stays inside one database and does not need a login in master.

Can I customize the permissions inside a fixed database role?

No, the SQL engine locks the permissions assigned to built-in fixed database roles. If you need custom rules, you must create your own user-defined database role instead.

Does the data reader role hide sensitive columns like national IDs or emails?

No, the fixed data reader role lets members read all data across all user tables and views. To rewrite sensitive fields like emails or card numbers in results, you need dynamic data masking.

The query runs. The emails stay hidden.