January 13, 2017 at 2:51 pm
We have a large OLTP database with approximately 700 user/web sessions connected to it. We have this database running transactional replication to a "reporting" server that we grant access to individuals to query against the data there. We are currently testing out migration to SQL 2016 and using Always On/ Availability Group to replace the transactional replication setup - which has always been fragile.
We are facing a challenge in how to manage end-user access to the AO/AG secondary replicas. According to what we've been told by Microsoft, we are going to have to manage end-user access to the read-only replicas by using Windows AD Groups. We create and permission the AD Group on the primary database since all security has to flow from the primary to the secondary instances. We can then add a new user to the AD group and they then can gain immediate access.
On the primary server, we have set the Deny permission on that AD Group's Status page. On the secondary instances, the setting is Grant to permit it to connect to the database engine.
All of this seems to work okay. We can add/drop a user from the AD Group and they can get their read access on the secondary replica. However, any user in that AD Group cannot then be added with an individual security entry on the primary and gain access there. Because they are present in the AD Group, the user isn't allowed to connect to the DB engine on the primary instance.
So this begs the question of the general DBA population utilizing AO/AG: How is everyone managing their user access in this environment? In our case, we want our primary (read/write) node to be accessible to the application utilizing appropriately permissioned service accounts. We want to get all Ad-Hoc query activity running against a read-only replica. Thanks for sharing your thoughts.
January 14, 2017 at 12:59 am
How are your users accessing the database- through an application? I think it would be easier for you to set up read-only routing for those users, and use "application intent = read only" in your connection string. That way users will always be routed to the secondary replica, regardless of which one it is. You'll need to use a listener device.
January 16, 2017 at 9:20 am
Beatrix Kiddo - Saturday, January 14, 2017 12:59 AMHow are your users accessing the database- through an application? I think it would be easier for you to set up read-only routing for those users, and use "application intent = read only" in your connection string. That way users will always be routed to the secondary replica, regardless of which one it is. You'll need to use a listener device.
Yes Beatrix, for the most part, all standard users are gaining access to the primary via the listener which connects using a production service account. We have a fairly large "production support" group of folks that have need to run their own ad-hoc queries to create data-fixes. Those are the folks we need to be a bit more "dynamic" about granting read-only access to a secondary replica. We have discovered that one might be able to set Read-Intent on the SSMS "connection properties -> additional connect properties" window, but that seems like it requires providing a lot of additional guidance to the users who are of differing experience levels.
January 16, 2017 at 10:43 am
disabling the group login seems a little over the top if im honest.
Setup read only routing and a listener and ensure that the connections are configured for read intent, you could also set the replica role to ReadWrite connections instead of All. Using this read intent connections would be disallowed to the primary.
With Read only routing your goal is to redirect the majority of read intent requests to readable secondaries.
You're always going to have some read connections that could be primary bound
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
January 16, 2017 at 12:29 pm
Perry Whittle - Monday, January 16, 2017 10:43 AMdisabling the group login seems a little over the top if im honest.
Setup read only routing and a listener and ensure that the connections are configured for read intent, you could also set the replica role to ReadWrite connections instead of All. Using this read intent connections would be disallowed to the primary.
With Read only routing your goal is to redirect the majority of read intent requests to readable secondaries.
You're always going to have some read connections that could be primary bound
I would not recommend using read-intent setup unless you really want normal application reads to be performed on the replica. If your only usage of the replica is to provide for ad-hoc reporting then just don't create the login on the primary node. Since this is a windows group then you can create the windows group as a user in the specified database without creating the login - the user will be transferred to the replica database and be accessible to the login on the replica from the same windows group.
Any users that need direct access to production but not necessarily the same access will require more work. You need to create the individual (or group) login - then deny permissions in each database that is granted by the read only windows group. It might be better to assign these users separate AD accounts that they would use specifically for accessing the production system and making changes. This would require the users to either login to windows with their admin account - or launch SSMS with the admin account. Either way it prevents them from 'accidentally' accessing production and making changes.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply