SQL Availability Groups, when move database aplication stop working

Question

SQL Availability Groups, when move database aplication stop working

RAN55 176

Hello,

We have a cluster SQL 2017 always on, node1 and node2. We have a database for lansweeper aplication. I put the password for the user with this SQL script:

https://www.lansweeper.com/knowledgebase/login-failed-for-user-lansweeperuser/

When we execute failover, database moves correctly but aplication fails with login error. If i set the password with the same script in the node2, works again.

But if we move again to node1, fail again.

Thanks,
Regards

7 answers

Your answer

Answer 1

RAN55 176

At the moment, i solved my problem with this script

https://www.sqlshack.com/synchronize-logins-between-availability-replicas-in-sql-server-always-on-availability-group/

Now i will try a powershell dbatools taks

Thanks to all for help :)

Sivert Solem 16 Reputation points

2022-08-15T13:04:01.48+00:00
Yes, that would help.
I noticed that the lansweeper script recreates the SQL user, not only resets the password.

With SQL Server authenticated users, SQL Server itself is responsible for setting the SID of the user.
The SID is what connects a login with the database user.

When you recreated your user locally, you reset the SID and the connection to the database.
In the future, you can verify and locally fix the SID mismatch with the following commands in the database:

-- This command will list database users where there's no login with corresponding SID: EXEC sp_change_users_login 'Report' -- If you already have a login id and password for this user, this command will ensure Server Login and Database User have same SID. I am unsure if it changes login or user SID: EXEC sp_change_users_login 'Auto_Fix', 'user' -- If you want to create a new login id and password for this user, fix it by doing: EXEC sp_change_users_login 'Auto_Fix', 'user', 'login', 'password'

sp_help_revlogin, as suggested by sqlshack, is recommended to create SQL Authenticated Logins on replicas in Always On clusters.
As for using dbatools, I believe the copy-dbalogin cmdlet is the one that you want for Always-On clusters.
RAN55 176 Reputation points

2022-08-16T13:14:06.96+00:00

Yes, i wil try with that command, thx

Answer 2

Hi @RAN55 ,

Welcome to Microsoft Q&A!
Passwords are stored in the master database. The password change succeeds on the primary but fails on the secondary because the password in the master on the current node is not reset. Unless the database is a contained database, server logins are also stored in the master database. Contained databases take care of themselves as contained users can be "logins" and are "contained" within the database, thus automatically included.

Best regards,
Seeya

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

RAN55 176

Thanks for the info.

How can i solve my problem ? i need to be able to move database without service interruption.

Seeya Xi-MSFT 16,586 Reputation points

2022-08-10T08:47:48.48+00:00

Hi,

You can reset the password on the both node. Good luck!

Best regards,
Seeya
RAN55 176 Reputation points

2022-08-10T09:17:30.117+00:00

I said it in my post:

If i set the password with the same script in the node2, works again. But if we move again to node1, fail again.
Seeya Xi-MSFT 16,586 Reputation points

2022-08-10T09:32:32.673+00:00

From your initial posting, you failover from node1 to node2 and then getting the error. You made it work by resetting the password on node2. Then failover returns to node1 and fails again.
In order to better solve your problem, I just confirmed with you if you have reset the password on node1.
Also I would like to ask is there any error message? If there is, you can also post it.
RAN55 176 Reputation points

2022-08-10T10:07:00.84+00:00

If a have to set the password of every user of every database, everytime i move a database... this cluster is a nightmare instead of a solution

This is what i needed

https://www.sqlshack.com/synchronize-logins-between-availability-replicas-in-sql-server-always-on-availability-group/

Thanks again,

Answer 4

Following your conversation early today...

As for SQL Server availability groups, you always have to standalone SQL Server which are using both the same feature for HA and can "share" (not necessarily all) databases and somehow connected... they are still standalone with their user and permission management as you also can host other databases that are not part of the AG.

So you have to make sure that all users that are planning to use the AG databases exist on both/all nodes of your AG... so you have to establish a process to synchronize them.
If you are doing this manually day by day or creating an automation for this... that is your decision
I am using a PowerShell dbatools mechanism for that that runs every x minutes/hours

https://docs.dbatools.io/Copy-DbaLogin

Answer 5

Tom Phillips 17,771

Please see:
https://techcommunity.microsoft.com/t5/core-infrastructure-and-security/sync-sql-logins-and-jobs/ba-p/371393

Share via

SQL Availability Groups, when move database aplication stop working

7 answers

Your answer