?
Solved

SQL Username continuously being disabled in SQL Server HA Cluster

Posted on 2016-11-10
13
Medium Priority
?
74 Views
Last Modified: 2016-11-30
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
Comment
Question by:Zack
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 6
13 Comments
 
LVL 51

Expert Comment

by:Vitor Montalvão
ID: 41883421
Hi Zack,

can you support your question with some screenshots so we can understand better the issue?
1
 
LVL 13

Expert Comment

by:Máté Farkas
ID: 41883900
Do you say that you endable the user/login on one node and it is disabled after a while?
0
 

Author Comment

by:Zack
ID: 41884493
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!

 
LVL 51

Expert Comment

by:Vitor Montalvão
ID: 41887471
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
 

Author Comment

by:Zack
ID: 41887482
Hi Vitor,

Please see the attached screenshots.
D001-Node.PNG
D002-Node.PNG
0
 
LVL 51

Expert Comment

by:Vitor Montalvão
ID: 41887489
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
 

Author Comment

by:Zack
ID: 41887493
Hi Vitor,

Yes that what I do and periodically gets disabled.

How should I troubleshoot?

Thank you.
0
 
LVL 51

Expert Comment

by:Vitor Montalvão
ID: 41887496
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
 

Author Comment

by:Zack
ID: 41887499
Hi Vitor,

2-3 times a week.

Thank you.
0
 
LVL 51

Expert Comment

by:Vitor Montalvão
ID: 41905330
Zack, any feedback for us?
Cheers
0
 

Author Comment

by:Zack
ID: 41906855
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
 
LVL 51

Accepted Solution

by:
Vitor Montalvão earned 2000 total points
ID: 41906878
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
 

Author Closing Comment

by:Zack
ID: 41906883
Thank you very much Vitor for this article, I am sure I will be able track the issue from here.
0

Featured Post

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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question