Azure Readable Secondary – How to: Different Access Policy to Primary?

All we need is an easy explanation of the problem, so here it is.

We have primary databases. They are in a mix of elastic pool, and standalone.

We have successfully created active geo replicated secondaries. (Our use for these is solely workload isolation.)

We want want to have different access policies. e.g A reader (by whatever principal – AAD group user etc) can only access the secondary.

This Microsoft Docs page implies it is possible to use different credentials for the secondary:

An application can access a secondary database for read-only
operations using the same or different security principals used for
accessing the primary database"

There are no further instructions. When attempting to use T/SQL grants on the secondary, an error is given that the DB is read only. This is expected behaviour, however the MS documentation quoted above suggest a different mechanism is possible.

How can we manage access at the secondary level, other than granting at the primary? Or granting excessive rights at the secondary, such as Active Directory Admin?

(We are aware of intent read only, but that does not meet our need, which is to deny certain principals access to the primary, regardless of intent)

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

The only way to have different access policies. e.g A reader (by whatever principal – AAD group user etc) can only access the secondary-

  1. Make the ‘Active Directory admin’ of the logical server hosting the secondary replica an AAD group.
  2. Make the user or group that needs to read from the secondary replica is a member of the group mentioned in step 1.
  3. As a pre-requisite you need to set the properties of secondary to read-only or all.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply