SQL Always On Availability Groups + permissions
Hello,
We are using RDM with SQL server (SQL 2016 Enterprise) AlwaysOn Availablity Group as the backend. RDM is properly connecting to the shared address of the availability group. Seems like there's a problem with the user logins and permissions: When adding a new user in RDM and assigning permissions the user account is correctly created in the database, but not under Security | Logins of the secondary SQL node, making the user unable to login when the SQL database switches over to the secondary node.
RDM "Fix SQL Login" button seems to fix the problem when ran after the failover, but as it requires manual work after failover and cannot be done beforehand it's not really a proper solution.
Any ideas?
All Comments (2)
Hello,
It is not something that is client side, but rather a requirement that is brought on by the chosen replication technology. We felt it was better to not try to handle that using RDM.
As per
https://blogs.msdn.microsoft.com/sqlcat/2012/04/23/always-on-availability-group-connection-to-readable-secondary-fails-when-login-sids-are-different-or-missing/
They recommend two solutions, one being a simple script that you schedule using the SQL Agent.
Best regards,
Maurice
Hmm ok, thanks. I will check which one of the solutions is most suitable for our use.
The scenario is a bit different, it's not readable secondary but primary after failover, but the result is effectively the same.