Windows/Entra/SQL Authentication Concepts, Logins vs Users, Contained Databases, and Identity

Learning outcomes

ServiceHub is ready to separate support engineers, API identities, deployment automation, and database administrators. The team’s first mistake would be to treat every security name as the same thing. In SQL Server, authentication answers “who are you?” while authorization answers “what can that identity do?” A server login and a database user usually participate in different halves of that model. Microsoft Entra identities and contained database users add more deployment-specific choices, not a reason to blur the boundary.

The practical goal is a stable identity model that survives restore, failover, migration, connection pooling, password rotation, and service-account change without granting broad privileges merely because a mapping broke.

1. Start with the two-stage identity model

A traditional SQL Server connection first authenticates a server-level principal. The Database Engine then maps that login to a user inside the target database. The login has a security identifier (SID); the mapped database user stores the corresponding SID. Database permissions are evaluated against the database user and its role memberships, while instance-scoped permissions are evaluated against the server principal.

SELECT SERVERPROPERTY('ProductVersion') AS product_version,       SERVERPROPERTY('ProductUpdateLevel') AS update_level,       SERVERPROPERTY('Edition') AS edition,       SERVERPROPERTY('EngineEdition') AS engine_edition,       SERVERPROPERTY('IsIntegratedSecurityOnly') AS windows_auth_only;GOSELECT name,value_in_useFROM sys.configurationsWHERE name IN  (N'contained database authentication',   N'column encryption enclave type',   N'clr enabled',N'xp_cmdshell');GOSELECT DB_NAME() AS database_name,       ORIGINAL_LOGIN() AS original_login,       SUSER_SNAME() AS execution_login,       USER_NAME() AS database_user;GO

USE master;GOIF DB_ID(N'ServiceHubSecurityLab') IS NULL  CREATE DATABASE ServiceHubSecurityLab;GOALTER DATABASE ServiceHubSecurityLab SET COMPATIBILITY_LEVEL = 170;GOUSE ServiceHubSecurityLab;GOIF SCHEMA_ID(N'ops') IS NULL EXEC(N'CREATE SCHEMA ops AUTHORIZATION dbo;');IF SCHEMA_ID(N'api') IS NULL EXEC(N'CREATE SCHEMA api AUTHORIZATION dbo;');IF SCHEMA_ID(N'sec') IS NULL EXEC(N'CREATE SCHEMA sec AUTHORIZATION dbo;');IF SCHEMA_ID(N'lab14') IS NULL EXEC(N'CREATE SCHEMA lab14 AUTHORIZATION dbo;');GOIF OBJECT_ID(N'ops.WorkOrderSecure',N'U') IS NULLBEGIN  CREATE TABLE ops.WorkOrderSecure  (    work_order_id bigint IDENTITY(14001,1) NOT NULL      CONSTRAINT PK_ops_WorkOrderSecure PRIMARY KEY,    tenant_code char(3) NOT NULL,    customer_code varchar(16) NOT NULL,    customer_name nvarchar(100) NOT NULL,    customer_email varchar(200) NOT NULL,    status varchar(16) NOT NULL,    priority tinyint NOT NULL,    amount decimal(12,2) NOT NULL,    opened_at datetime2(0) NOT NULL,    notes nvarchar(400) NULL,    CONSTRAINT CK_ops_WorkOrderSecure_tenant      CHECK (tenant_code IN ('N01','W02','E03')),    CONSTRAINT CK_ops_WorkOrderSecure_status      CHECK (status IN ('OPEN','ASSIGNED','CLOSED','ESCALATED')),    CONSTRAINT CK_ops_WorkOrderSecure_priority CHECK (priority BETWEEN 1 AND 5)  );  INSERT ops.WorkOrderSecure    (tenant_code,customer_code,customer_name,customer_email,status,priority,amount,opened_at,notes)  VALUES    ('N01','CUST-1401',N'Mina Rahimi','mina@example.test','OPEN',2,240.00,'2026-08-01T09:00:00',N'North queue'),    ('N01','CUST-1402',N'Owen Brooks','owen@example.test','ASSIGNED',3,480.00,'2026-08-01T10:15:00',N'North dispatch'),    ('W02','CUST-1403',N'Sara Chen','sara@example.test','ESCALATED',5,860.00,'2026-08-01T11:30:00',N'West escalation'),    ('W02','CUST-1404',N'Ali Reza','ali@example.test','OPEN',1,120.00,'2026-08-02T08:20:00',N'West queue'),    ('E03','CUST-1405',N'Nora Bell','nora@example.test','CLOSED',4,735.00,'2026-08-02T09:40:00',N'East closed'),    ('E03','CUST-1406',N'David Kim','david@example.test','OPEN',2,315.00,'2026-08-02T12:05:00',N'East queue');  CREATE INDEX IX_ops_WorkOrderSecure_tenant_status    ON ops.WorkOrderSecure(tenant_code,status,opened_at)    INCLUDE(customer_code,priority,amount);END;GO

USE ServiceHubSecurityLab;GOSELECT name,type_desc,is_disabled,default_database_name,sidFROM sys.server_principalsWHERE type IN ('S','U','G','E','X')ORDER BY type_desc,name;GOSELECT name,type_desc,authentication_type_desc,default_schema_name,sidFROM sys.database_principalsWHERE principal_id > 4ORDER BY type_desc,name;GOSELECT dp.name AS database_user,       dp.type_desc AS user_type,       sp.name AS mapped_login,       sp.type_desc AS login_type,       CASE WHEN sp.sid IS NULL THEN 0 ELSE 1 END AS has_server_mappingFROM sys.database_principals AS dpLEFT JOIN sys.server_principals AS sp ON sp.sid=dp.sidWHERE dp.authentication_type=1ORDER BY dp.name;GO

The final query is evidence about login-based users. It does not classify contained users, certificate users, asymmetric-key users, or users without logins as “broken.” Security investigations must interpret authentication_type and principal type before treating a missing server SID as an error.

2. Windows, SQL, and Microsoft Entra authentication solve different identity problems

SQL Server 2025 also strengthens new SQL-authentication password storage by using PBKDF with SHA-512 and 100,000 iterations. That improves resistance to offline password guessing, but it does not make reused passwords, embedded secrets, excessive login rights, or unencrypted network connections safe.

SELECT CASE SERVERPROPERTY('IsIntegratedSecurityOnly')         WHEN 1 THEN 'Windows authentication only'         WHEN 0 THEN 'Mixed mode: Windows + SQL authentication'       END AS instance_authentication_mode;GO-- Observation only. Changing authentication mode is an instance-wide-- administrative decision and is intentionally outside this lab.

3. Reproduce an orphaned user safely, then repair the SID mapping

An orphaned user is a login-based database user whose stored SID no longer matches a server login. This commonly appears after a restore to another instance or after someone drops and recreates a SQL login. Recreating a login with the same name does not recreate the same SID automatically.

USE master;GOIF SUSER_ID(N'lab14_identity_login') IS NOT NULL DROP LOGIN lab14_identity_login;DECLARE @pw nvarchar(128)=N'Lab!9aA-'+REPLACE(CONVERT(nvarchar(36),NEWID()),N'-',N'');EXEC(N'CREATE LOGIN lab14_identity_login WITH PASSWORD=' + NCHAR(39) + @pw + NCHAR(39) +     N', CHECK_POLICY=ON, CHECK_EXPIRATION=OFF;');GOUSE ServiceHubSecurityLab;GOIF USER_ID(N'lab14_identity_user') IS NOT NULL DROP USER lab14_identity_user;CREATE USER lab14_identity_user FOR LOGIN lab14_identity_login;SELECT dp.name AS user_name,sp.name AS login_name,       CONVERT(varchar(170),dp.sid,1) AS user_sid,       CONVERT(varchar(170),sp.sid,1) AS login_sidFROM sys.database_principals AS dpJOIN sys.server_principals AS sp ON sp.sid=dp.sidWHERE dp.name=N'lab14_identity_user';GOUSE master;DROP LOGIN lab14_identity_login; -- user remains in the databaseGOUSE ServiceHubSecurityLab;SELECT dp.name AS orphan_candidate,sp.name AS mapped_loginFROM sys.database_principals AS dpLEFT JOIN sys.server_principals AS sp ON sp.sid=dp.sidWHERE dp.name=N'lab14_identity_user';GOUSE master;DECLARE @pw2 nvarchar(128)=N'Lab!9aA-'+REPLACE(CONVERT(nvarchar(36),NEWID()),N'-',N'');EXEC(N'CREATE LOGIN lab14_identity_login WITH PASSWORD=' + NCHAR(39) + @pw2 + NCHAR(39) +     N', CHECK_POLICY=ON, CHECK_EXPIRATION=OFF;');GOUSE ServiceHubSecurityLab;ALTER USER lab14_identity_user WITH LOGIN = lab14_identity_login;GOSELECT dp.name,sp.name AS repaired_login,       CASE WHEN dp.sid=sp.sid THEN 1 ELSE 0 END AS sid_matchesFROM sys.database_principals AS dpJOIN sys.server_principals AS sp ON sp.name=N'lab14_identity_login'WHERE dp.name=N'lab14_identity_user';GODROP USER lab14_identity_user;USE master;DROP LOGIN lab14_identity_login;GO

Use ALTER USER ... WITH LOGIN for modern remapping. The old sp_change_users_login procedure is deprecated. In production, also reconcile the login’s intended permissions, default database, password/service identity ownership, and automation that will reproduce it during disaster recovery.

4. Containment trades instance dependence for delegated database identity control

A partially contained database can authenticate users at the database boundary without a corresponding login in master. That improves portability, but it also changes who can create authenticating identities. When contained authentication is enabled, database principals with sufficiently broad user-management permissions can grant database access without a server administrator creating a login first.

SELECT name,value,value_in_useFROM sys.configurationsWHERE name=N'contained database authentication';GOSELECT name,containment_descFROM sys.databasesWHERE name=N'ServiceHubSecurityLab';GO-- Deliberately not executed by the mandatory lab:-- EXEC sys.sp_configure 'contained database authentication',1;-- RECONFIGURE;-- ALTER DATABASE ServiceHubSecurityLab SET CONTAINMENT=PARTIAL;-- CREATE USER app_contained WITH PASSWORD='<unique secret supplied at deployment>';

Contained users with passwords must connect with the database named as the initial catalog so SQL Server knows which database authenticates them. Avoid naming collisions between contained users and server logins. If you adopt containment, include it in restore/migration runbooks and permission reviews rather than treating it as a purely developer-facing convenience.

5. Service identities are lifecycle objects, not anonymous connection strings

Application pools, scheduled jobs, Windows services, containers and CI/CD automation should each have identities whose ownership, rotation and permissions are explicit. Prefer group/service-principal/managed-identity patterns supported by the deployment over shared human credentials. Connection pooling does not erase identity: the physical connection has authenticated state, session context, SET options and execution context that must be reset correctly before reuse.

6. Production judgment

Production judgment. Record the exact SQL Server build, edition, host platform, authentication mode, identity provider, TLS requirements, connection driver and HA topology. For Microsoft Entra, record the SQL Server integration path and external dependencies. For contained users, record the instance containment setting and who is authorized to create users. For SQL authentication, treat passwords as deploy-time secrets and monitor login latency if a workload opens new connections excessively rather than using pooling.

Hands-on verification checklist

• Confirm the engine build and authentication mode without changing server configuration.
• Identify at least one server principal and its mapped database user by SID.
• If authorized, reproduce an orphaned user and repair it with ALTER USER ... WITH LOGIN.
• Record whether contained authentication is enabled and whether the lab database is contained.
• Document which authentication option your ServiceHub application would use and why.

Authoritative references

• Create a login — server principals, authentication model and SQL Server 2025 password hashing
• Create a database user — database-user models and containment
• Troubleshoot orphaned users — SID mapping and ALTER USER repair
• Contained databases — containment boundary and portability
• Contained database authentication — instance configuration and security implications
• Microsoft Entra authentication for SQL Server — SQL Server 2022+ Entra capabilities and topology boundaries
• SQL Server security best practices — identity and least-privilege guidance
• SQL Server 2025 build versions — current servicing baseline