June 12, 2018 at 9:37 am
Trying to solve a puzzle. I have a 2 node AG with OLTP on one and the other is used for reporting. I create a role on the OLTP server in a database with access to objects needed. Then have windows login added with access to the role. Let's say this a reporting role. of course all this security is replicated to the reporting server, which is great. However how do i stop the user from running their extract/report/sql etc only on the read only node. Denying access to OLTP will replicate to the report server. Am I missing something
June 12, 2018 at 3:22 pm
If using windows accounts - then all you need to do is add the user to the database, do not create a login. The login will be created only on the read-only replica node and therefore they can only login to the read-only server.
If using SQL accounts - it gets a bit trickier...
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
June 13, 2018 at 7:06 am
tcronin 95651 - Tuesday, June 12, 2018 9:37 AMHowever how do i stop the user from running their extract/report/sql etc only on the read only node. Denying access to OLTP will replicate to the report server. Am I missing something
To clarify, you want to prevent the users from running reports from the read-only replica?
June 13, 2018 at 7:30 am
no from the OLTP, I was thinking I could use deny on the group login on the OLTP. However if a user belongs to more than one group and that group did need access to the OLTP I believe the deny would stop the user , my recollection deny wins out
June 13, 2018 at 8:28 am
Then set up read-only routing, and require them to connect with application intent = readonly.
June 13, 2018 at 8:56 am
was going to problem is all different types of tools going to this so forcing this may be an issue
June 13, 2018 at 9:21 am
You could set up a login trigger on each node, which would prevent the reporting user from logging in if the node is not the read-only node. You can use sys.fn_hadr_is_primary_replica ( 'dbname' ) to check this.
June 13, 2018 at 9:30 am
that was plan B not a big fan I think I can live with the deny for now users not crossing over between groups
June 13, 2018 at 1:03 pm
tcronin 95651 - Wednesday, June 13, 2018 7:29 AMno from the OLTP, I was thinking I could use deny on the group login on the OLTP. However if a user belongs to more than one group and that group did need access to the OLTP I believe the deny would stop the user , my recollection deny wins out
I am not following the problem...
If you want to insure that a user cannot access the OLTP - then disable or remove the login from that server\instance. The login is at the server level only - the user is at the database level and anything related to the user at the database level will be transferred to the read-only replica, but the login will not be moved. If the login only exists on the read-only secondary then that login can *only* access the secondary and will not be able to access the primary.
If the user belongs to a group that has access to the OLTP - then they should have access based on that membership (otherwise, why have the group in the first place?). If they shouldn't have that access - then why are they included in that group? If there is some reason that they are in a group that has access - but they should not...then you can add them as a login and deny connect to that login (I am still not sure why this would occur...much easier to just remove them from the security group).
If the group does not need access to the OLTP - then remove the group login from that server\instance.
Unless you are using SQL accounts, the SID is identified in AD and will not change. You can add the user without a login to any database - and that user cannot access the database if the login has not been added or is disabled (note: you cannot disable a group).
There should be no reason to worry about a login trigger - or denying access to a specific login unless you have users assigned to security groups that should not be in those groups.
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
June 13, 2018 at 1:33 pm
Jeffrey Williams 3188 - Wednesday, June 13, 2018 1:03 PMtcronin 95651 - Wednesday, June 13, 2018 7:29 AMno from the OLTP, I was thinking I could use deny on the group login on the OLTP. However if a user belongs to more than one group and that group did need access to the OLTP I believe the deny would stop the user , my recollection deny wins outI am not following the problem...
If you want to insure that a user cannot access the OLTP - then disable or remove the login from that server\instance. The login is at the server level only - the user is at the database level and anything related to the user at the database level will be transferred to the read-only replica, but the login will not be moved. If the login only exists on the read-only secondary then that login can *only* access the secondary and will not be able to access the primary.
If the user belongs to a group that has access to the OLTP - then they should have access based on that membership (otherwise, why have the group in the first place?). If they shouldn't have that access - then why are they included in that group? If there is some reason that they are in a group that has access - but they should not...then you can add them as a login and deny connect to that login (I am still not sure why this would occur...much easier to just remove them from the security group).
If the group does not need access to the OLTP - then remove the group login from that server\instance.
Unless you are using SQL accounts, the SID is identified in AD and will not change. You can add the user without a login to any database - and that user cannot access the database if the login has not been added or is disabled (note: you cannot disable a group).
There should be no reason to worry about a login trigger - or denying access to a specific login unless you have users assigned to security groups that should not be in those groups.
The trouble is that wouldn't work in a failover situation as the servers would swap roles. Also presumably if one node went down the OP would want all queries to go through the remaining node, so the logins would need to exist on both servers for that to work.
June 13, 2018 at 1:39 pm
In perfect world you are correct, need the windows group login in initial setup to be added as user to db. This is replicated. Most of my users with myriad of tools know to connect to the RO node, however in some cases connect to pharmcorsql01. I will probably remove these logins
June 13, 2018 at 2:03 pm
Julian Watson - Wednesday, June 13, 2018 1:33 PMThe trouble is that wouldn't work in a failover situation as the servers would swap roles. Also presumably if one node went down the OP would want all queries to go through the remaining node, so the logins would need to exist on both servers for that to work.
You should not be creating or using a read-only secondary for that purpose - in fact, I don't think you can set up a read-only secondary with automatic failover. If you are setting it up that way - then any access you grant to the primary would need to be replicated to the secondary anyways and you end up having the same problem.
If you need failover - and you need/want a read-only secondary - then you should build out 3 nodes. The primary node - a synchronous secondary with automatic failover and an asynchronous secondary (read-only) with no failover.
For failover - you need to keep both (all) secondaries up to date with every change at the server level - including agent jobs, logins, maintenance plans, policies, etc...
For read-only reporting - you only need the databases. You use separate logins, agent jobs, maintenance plans, etc... on that instance.
If the goal is to offload read - then you setup the secondary with read-intent, which has a whole other level of management needed and modifications to connection strings to direct the connection to the appropriate host at the time of the request.
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
June 13, 2018 at 2:08 pm
tcronin 95651 - Wednesday, June 13, 2018 1:39 PMIn perfect world you are correct, need the windows group login in initial setup to be added as user to db. This is replicated. Most of my users with myriad of tools know to connect to the RO node, however in some cases connect to pharmcorsql01. I will probably remove these logins
Actually - you do not need the login created to add a windows user or group to a database. You can add that user directly without the login - avoiding the possibility that any members of that group could access that instance.
CREATE USER {windows group} WITH default_schema = dbo;
GO
ALTER ROLE {role} ADD Member {windows group};
GRANT EXECUTE ON schema::dbo TO {windows group};
GRANT VIEW DEFINITION ON schema::dbo TO {windows group};
The above will transfer across for that database - and the login on that instance will then immediately have those permissions in that database. If the login exists on that instance - of course.
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
June 13, 2018 at 2:13 pm
thanks only question is when replicated to the RO server isn't there a need for the login to mapped to the SID? Actually going to try this with test AD group, thanks for the reminder
June 13, 2018 at 2:19 pm
tcronin 95651 - Wednesday, June 13, 2018 2:13 PMthanks only question is when replicated to the RO server isn't there a need for the login to mapped to the SID? Actually going to try this with test AD group, thanks for the reminder
Only if using a SQL account - a domain account has a single SID across the domain.
If you are using SQL accounts then you do need to create the login, capture the SID - create the same login on the other instance with the same SID - grant permissions in the database, then disable/remove the login from the OLTP/Primary system. This insures the SID is the same across both instances and allows the login on the secondary to tie to the user in the database.
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 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply