January 31, 2018 at 10:24 am
Evening All,
Is it possible to use a Logon Trigger to prevent a user logging on or to eject them once logged on.
I have a 4-replica AlwaysOn Avaiability group. I want some users only to have access to the read only nodes. I understand that Application Intent will route them to the correct node but they cannot be trusted not to forget to add this attribute to their connection string. As was the case this morning.
I don't want to revoke their rights to the principal replica as that will cause problems when the AG fails over.
So what I thought would be a Logon Trigger will check if the AG is owned by that node and if so kick off the user.
They will be accessing via a Listener Name. They use Active Directory logins.
Any thoughts?
Cheers
Alex
February 1, 2018 at 8:32 am
If i understand correctly, what you are trying to do is.
You have secondary replicas and you want your application users to log in the secondary (read-only) replica via the listener.
The only solution i can think of is add another application server that redirect them to the secondary (read-only) replica and grant the selected users the permissions to use that application.
I don't think its possible to do what you are trying to achieve. i think the only way to redirect the user with the main application is either: modify the connection string to look for the secondary replica, since the only way to connect to that secondary replica through the listener is using the WSFC and making a manual failover to that secondary.
I'm fairly new at AG and i think that the only way to use a secondary replica with the listener is using a manual failover (correct me if im wrong, would be a really nice information).
February 1, 2018 at 9:36 am
alex.sqldba - Wednesday, January 31, 2018 10:24 AMEvening All,
Is it possible to use a Logon Trigger to prevent a user logging on or to eject them once logged on.I have a 4-replica AlwaysOn Avaiability group. I want some users only to have access to the read only nodes. I understand that Application Intent will route them to the correct node but they cannot be trusted not to forget to add this attribute to their connection string. As was the case this morning.
I don't want to revoke their rights to the principal replica as that will cause problems when the AG fails over.
So what I thought would be a Logon Trigger will check if the AG is owned by that node and if so kick off the user.
They will be accessing via a Listener Name. They use Active Directory logins.
Any thoughts?
Cheers
Alex
you want to prevent users from initiating read queries to replicas in the primary role, is that correct?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
February 1, 2018 at 11:46 am
Alejandro, I don't think that is what I mean.
Perry, yes this is what I intend.
Certain users 'forget' to append ApplicationIntent to their connection string. This results in them connecting to the primary. The queries they perform are pretty heavy and cause locking and IO problems.
I don't want to revoke their access to the primary, in case at some point it becomes the instance they are meant to be connecting. Through planned or unplanned availability grou role changes.
So the solution I could think of was to compare their login name to a list of known offenders within a Logon Trigger.
But now I am saying this out loud, I can't see how it would know what Listener it connected to.
Damn.
Anyway, that's my problem.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply