Force user to read replica Azure SQL Database where Read Scale-Out has been Enabled and user only has readonly access,
Is there any way to force a user to read from a read only replica where that user has only been granted readonly access to a database? It seems that we're relying on any external application to be a good citizen and provide the ApplicationIntent in the connection string.
Just thinking, for others landing here... it's not perfect, but perhaps it's possible to prevent AD logins into the primary and over to the replica using SQL Server-level firewall rules? Create rules that only allow these IPs to write into primary?
Sebastiano Trusso commented
Hi, I am working for a big ecommerce initiative in collaboration with SAP and we have the same request for this improvement.
It is very important to consider the ApplicationIntent also from an Authorization perspective to allow DB administrators to grant a specific user to use only ApplicationIntent=ReadOnly. In this case that user is not allowed to access the RW primary DB having the possibility to brake the performances.
Thanks and regards, Sebastiano
Adding my vote as well. We have users in other departments developing reports. Sometimes during the course of that development they try to Select * against very large tables, that kills our performance. Would like to force specific users to RO.
Troy Witthoeft commented
Also adding my vote. We'd like a way to protect our RW database and have our AD users only use the RO replica.
Granted this was a long time ago but I have the same question. I had someone run an intrusive read query against the RW when they should have ran it against the RO, and that caused some serious issues for a bit as it slowed down the RW enough to affect critical insert/update/delete processes. While we usually do configure the users SSMS correctly to ensure they are hitting the RO, in this case it was not setup properly, but the user does only have read access to the database.