January 5, 2016 at 7:59 am
Kind of an odd-ball request but it thrown on my plate...
For the most part, our development staff very limited access to our production database... But they do have access. I've been asked to find a way to limit that access to the secondary copy of our AG.
I'm aware that we can use "ApplicationIntent=ReadOnly" when connecting and developers are "encouraged to use it... That said humans tend to be forgetful, lazy or a combination of the two (myself included)...
Are any of you aware of a way to limit, by either user or role, access to the secondary copy only?
Failing that, is there a way to default the connection to use ApplicationIntent=ReadOnly, so that the user would have to actively choose the primary copy?
Thanks in advance,
Jason
January 5, 2016 at 10:24 am
January 5, 2016 at 10:41 am
MadAdmin (1/5/2016)
Login trigger on live server A.No login trigger on liver server B
Do rollback if Login name = 'ThisHardenedDeveloper'
Thanks MadAdmin,
Correct me if wrong (a very real possibility), but that would simply refuse the connection to the active primary, not redirect them to the secondary.
That's a definite possibility if there's no option to redirect the login automatically.
January 5, 2016 at 11:36 am
I'm not aware of any way to force connections to the read-only replica for particular logins.
When I've needed logins to have permissions solely on the read-only secondary, I've just created the login on the primary and secondary, mapped it to the appropriate users/roles in the DB on the primary (since the secondary is read-only), and then disabled the login on the primary.
Cheers!
January 5, 2016 at 12:46 pm
Jacob Wilkins (1/5/2016)
I'm not aware of any way to force connections to the read-only replica for particular logins.When I've needed logins to have permissions solely on the read-only secondary, I've just created the login on the primary and secondary, mapped it to the appropriate users/roles in the DB on the primary (since the secondary is read-only), and then disabled the login on the primary.
Cheers!
Yes, just disable login on primary.
Was on autpilot with my wayward suggestion.
Something I did in the past cos the devs were connecting to live with a sql login used by the application.
Not relevant in this case.
And worse, when the availability group fails over then they will have access only to the primary.
January 6, 2016 at 12:15 pm
Thank you both. 🙂
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply