add replica access restriction for Azure SQL contained users

Dan McClain 0 Reputation points
2026-11-19T17:47:21.5233333+00:00

Please add a setting for Azure SQL database contained users that controls which replica types they can connect to:

Read-only: Allow connections only to read-only replicas.

Read-write: Allow connections only to the read-write primary.

Both: Allow connections to either replica type. This would be the default to preserve existing behavior.

Azure SQL should enforce this restriction when establishing a connection, based on the replica’s current role, including after failover.

This would let us restrict reporting users to read-only replicas and prevent them from connecting to the production primary, helping protect transactional workload performance.

Azure SQL Database
0 comments No comments

2 answers

Sort by: Most helpful
  1. Erland Sommarskog 137.7K Reputation points MVP Volunteer Moderator
    2026-11-19T18:24:12.84+00:00

    The place to go with feature requests like this one is https://feedback.azure.com/d365community/post/04fe6ee0-3b25-ec11-b6e6-000d3a4f0da0

    Was this answer helpful?

    0 comments No comments

  2. AI answer

    2026-11-19T17:48:12.8033333+00:00

    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, and Both values.
    • Both as 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=ReadOnly routes 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:

    1. Create the principal’s server login on the primary logical server.
    2. Create the corresponding database user and grant read permissions.
    3. Disable or drop that login on the primary server.
    4. Create the named replica on a different logical server in the same Azure region.
    5. Create the login only on the named-replica server, preserving the SID for SQL authentication.
    6. 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

    1. What's new in Azure SQL Database?
    2. Use read-only replicas to offload read-only query workloads
    3. Failover groups overview & best practices (Azure SQL Database)
    4. Configure isolated access for Hyperscale named replicas
    5. Hyperscale secondary replicas
    AI-generated content may be incorrect. Read our transparency notes for more information.

    Was this answer helpful?

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.