Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 83
  • Last Modified:

SQL Username continuously being disabled in SQL Server HA Cluster

Heyas,

One particular username is being disabled on the SQL HA cluster, the odd thing is it's only occurring on the Secondary 'Passive Node' of the cluster.

I have checked the SQL jobs on the Secondary Node and nothing from what I can tell is disabling this particular user.

Any ideas on how to investigate/troubleshoot this?

Thank you.
0
Zack
Asked:
Zack
  • 6
  • 6
1 Solution
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Hi Zack,

can you support your question with some screenshots so we can understand better the issue?
1
 
Máté FarkasDatabase Developer and AdministratorCommented:
Do you say that you endable the user/login on one node and it is disabled after a while?
0
 
ZackGeneral IT Goto GuyAuthor Commented:
Hi Mate,

"Do you say that you enable the user/login on one node and it is disabled after a while?"

Yes I am saying this.

Vitor what screenshots would you like me to provide?

Thank you.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Vitor what screenshots would you like me to provide?

A screenshot of the situation: "One particular username is being disabled on the SQL HA cluster, the odd thing is it's only occurring on the Secondary 'Passive Node' of the cluster."
0
 
ZackGeneral IT Goto GuyAuthor Commented:
Hi Vitor,

Please see the attached screenshots.
D001-Node.PNG
D002-Node.PNG
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
They aren't the same SQL Server instance so that's nothing to do with the failover process.
You need to enable manually the login in the instance D002.
0
 
ZackGeneral IT Goto GuyAuthor Commented:
Hi Vitor,

Yes that what I do and periodically gets disabled.

How should I troubleshoot?

Thank you.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Just enable now and next time that it happens again you just return to this question so we will try to solve this.
Btw, how often this occurs?
0
 
ZackGeneral IT Goto GuyAuthor Commented:
Hi Vitor,

2-3 times a week.

Thank you.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Zack, any feedback for us?
Cheers
0
 
ZackGeneral IT Goto GuyAuthor Commented:
Heyas,

It has occurred a couple of times last week, but I had to re-enable it for the user.  Now it's disabled and the user is away today :)

Your troubleshooting suggestions, please.

Thank you.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Zack, best thing to do is to track any login change with a trigger on the server side.
In this article you'll find the script for that. Copy and paste it to your SQL Server instance, change the email address to yours and run it. Now every time a change occurs on a login you'll receive an email with the necessary information.
0
 
ZackGeneral IT Goto GuyAuthor Commented:
Thank you very much Vitor for this article, I am sure I will be able track the issue from here.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 6
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now