Solved

SQL Username continuously being disabled in SQL Server HA Cluster

Posted on 2016-11-10
13
47 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 47

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
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.

 
LVL 47

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 47

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 47

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 47

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 47

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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Suggested Solutions

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.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

773 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