Solved

SQL Username continuously being disabled in SQL Server HA Cluster

Posted on 2016-11-10
13
42 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
  • 6
  • 6
13 Comments
 
LVL 46

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 12

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
 
LVL 46

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 46

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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

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 46

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 46

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 46

Accepted Solution

by:
Vitor Montalvão earned 500 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
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 SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

895 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now