?
Solved

SQL Username continuously being disabled in SQL Server HA Cluster

Posted on 2016-11-10
13
Medium Priority
?
81 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 52

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
Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

 
LVL 52

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 52

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 52

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 52

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 52

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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

649 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