Azure Active Directory as Your Identity Foundation

Shannon Lowder

Shannon Lowder

— 3 min read

One of the decisions that bit the most Azure early adopters in 2011 was identity. They moved their database to the cloud and kept their authentication on-prem — SQL logins, Windows accounts tied to an Active Directory that wasn't connected to Azure. The result was a split identity model that was annoying to manage, difficult to audit, and impossible to explain to a security auditor. Azure Active Directory existed to solve this problem, and in 2011, most people weren't using it.

What SQL Server Authentication Looks Like in the Cloud

On-prem SQL Server gives you two authentication modes. Windows Authentication uses the machine's domain membership — your AD credentials flow through and SQL Server trusts them. SQL Authentication is a SQL login with a username and password stored in the SQL Server instance itself. Both are manageable when you own the infrastructure. In the cloud, neither maps cleanly to the same mental model.

SQL Authentication in SQL Azure works — you create a login at the server level and a user at the database level, same as on-prem. But now your credentials are a username and password floating in a managed service, without the domain-joined machine context you're used to. Password rotation, lockout policies, audit logging — these all become things you have to manage explicitly rather than inheriting from your corporate AD.

Windows Authentication in SQL Azure required Azure AD integration to work properly, and in early 2011, that integration was still maturing. Many shops defaulted to SQL Authentication because it was simpler to set up. This was a mistake they paid for later.

What Azure Active Directory Provides

Azure AD is a cloud-hosted identity provider. At its simplest, it's a directory of users and groups — the same concept as on-prem Active Directory, but running as a service. In 2011, you could:

  • Sync your on-prem AD to Azure AD using Directory Sync (the predecessor to Azure AD Connect), giving your existing users cloud identities without requiring them to remember separate passwords
  • Create cloud-only Azure AD users for external partners, contractors, or services that didn't have on-prem AD accounts
  • Use Azure AD as the identity provider for Azure management operations — who can create VMs, who can modify network rules, who can access the subscription

The IAM story on the Azure subscription side was particularly important. Without Azure AD, access to the Azure management portal was controlled by Microsoft Account (personal) logins — not a tenable security model for a business. Azure AD gave you organizational accounts with centralized management, so you could add and remove access when someone joined or left the team without relying on them to delete their own Microsoft Account access.

The RBAC Model

Azure Role-Based Access Control let you assign specific permissions to Azure AD users and groups at the subscription, resource group, or resource level. The built-in roles covered the common cases:

  • Owner — full access including the ability to assign access to others
  • Contributor — can create and manage resources but can't grant access
  • Reader — read-only access to resources

For a team with a DBA, a developer, and a manager, this translated directly: DBA gets Contributor on the SQL Server resources, developer gets Contributor on the app service resources, manager gets Reader on everything. No shared credentials, no "I'll just give everyone Owner for now."

The Recommendation I Started Making in 2011

Set up Azure AD first, before anything else. Even if it felt like extra work at the beginning, having a clean identity foundation made every subsequent access control decision easier. Adding an Azure AD user to a SQL Azure database as a contained user instead of a SQL login gave you single sign-on with your corporate credentials, centralized password policy enforcement, and an audit trail that tied database access back to a specific human identity — not a shared service account.

-- Create a contained Azure AD user in SQL Azure
CREATE USER [user@yourdomain.com] FROM EXTERNAL PROVIDER;
ALTER ROLE db_datareader ADD MEMBER [user@yourdomain.com];
ALTER ROLE db_datawriter ADD MEMBER [user@yourdomain.com];

This pattern — Azure AD user as the identity, database role as the permission boundary — is still the right answer for SQL database access in Azure. The tooling has improved dramatically since 2011, but the model hasn't changed. Get identity right at the start and you won't be retrofitting it later when someone asks you to demonstrate who has access to what.

If you're setting up an Azure environment now and haven't thought through the identity model yet, start there. It affects every other access control decision you'll make. As always, I'm here to help.

Read more