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.
ZackGeneral IT Goto GuyAsked:
Who is Participating?
 
Vitor MontalvãoConnect With a Mentor MSSQL 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
 
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
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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
 
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
 
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
All Courses

From novice to tech pro — start learning today.