An Azure relational database service.
Azure SQL Database does not currently document a per-contained-user setting that restricts connections to read-only replicas, the read-write primary, or both; submit this as an Azure SQL feature request, and use Hyperscale named-replica access isolation when that service-tier-specific design is applicable.
Submit the feature request
Post the proposal through the Azure SQL Database feedback channel linked from the release documentation ^1^.
Include these requirements explicitly:
- A database-principal property with
ReadOnly,ReadWrite, andBothvalues. -
Bothas the compatibility-preserving default. - Enforcement during authentication rather than relying solely on the client connection string.
- Evaluation against the replica’s current role after every failover.
- Coverage for contained SQL users and contained Microsoft Entra users.
- Defined behavior for connection pooling and sessions established before a role change.
- A distinct login error when the principal is not permitted on the target replica.
Current behavior is routing, not per-user authorization
For standard Azure SQL read scale-out, the client chooses its routing intent:
-
ApplicationIntent=ReadOnlyroutes the connection to a read-only replica. -
ApplicationIntent=ReadWrite, or omitting the setting, routes it to the read-write replica. - Replica role can be checked with
DATABASEPROPERTYEX^2^.
Server=tcp:<server>.database.windows.net;
Database=<database>;
ApplicationIntent=ReadOnly;
User ID=<user>;
Password=<password>;
Encrypt=True;
SELECT DATABASEPROPERTYEX(DB_NAME(), 'Updateability');
The query returns READ_ONLY on a read-only replica. This can validate routing, but it does not prevent that same user from supplying ApplicationIntent=ReadWrite in another connection.
For failover groups, read-only workloads can use the read-only listener:
Server=<failover-group>.secondary.database.windows.net;
Database=<database>;
ApplicationIntent=ReadOnly;
That listener automatically targets the geo-secondary ^3^. It is still an endpoint and connection-routing mechanism, not a contained-user restriction.
Supported isolation for Hyperscale named replicas
If the database uses Azure SQL Database Hyperscale named replicas, Microsoft documents a design that grants a principal access to a named replica without granting access to the primary or other named replicas ^4^.
The documented design is:
- Create the principal’s server login on the primary logical server.
- Create the corresponding database user and grant read permissions.
- Disable or drop that login on the primary server.
- Create the named replica on a different logical server in the same Azure region.
- Create the login only on the named-replica server, preserving the SID for SQL authentication.
- Direct the reporting application to the named replica’s server endpoint.
The resulting principal can authenticate to the named replica but not the primary. This provides both access and compute isolation, but it is not the proposed role-aware property:
- It applies specifically to Hyperscale named replicas.
- It depends on separate logical-server login configuration.
- Applications must use the named-replica endpoint.
- It does not automatically follow whichever replica becomes primary after failover.
Do not treat ApplicationIntent=ReadOnly alone as a security boundary. In Hyperscale, if no secondary replica exists, a read-intent connection can be routed to the primary and default to read-write behavior ^5^.
References