3

We have this one user who uses SSMS to connect to a 3 node 2 instance fail over cluster. He also uses SSIS, Excel, SharePoint, SSAS etc.. to connect to this database instance I'll call TEST. There are a couple hundred other users of this instance.

His domain account gets locked out about 1-2 days after we have an instance failover. This has been happening for about a year. We generally failover about once a month but sometimes not for a couple months. He gets his password reset and everything is fine until the next failover.

I'm assuming he has entered his password in somewhere and it has since expired. He changed it but never updated the spot he saved it. I can't find that spot for the life of me and the user is getting upset because I keep asking the same question.

We have verified using our syslog server that the lockout is coming from that SQL node that runs the TEST instance. It changes as the instance floats around the cluster so we are sure it is something in the TEST instance. The database engine and SSAS run as part of the TEST cluster service. SSISDB is fairly heavily used on that instance.

I checked all the agent job owners, he doesn't own any of those. I checked the SSISDB parameters list but didn't find is account in there. I'm not really sure if the constrained delegation we have setup for reporting services could trigger the lockout to come from the database server but maybe...

Any other spots I could check for saved credentials in SQL?

0

The problem with finding saved creds in SQL Server is that it's usually stored client side. You will have to capture people trying to connect to the server, but it's interesting that it only occurs You could run a extended event or a trace and capture failed logins by that user, that will tell you if it's MS SQL Server that is locking him out. Then you could view what time it occurred and capture other data.

A lot of things could be trying to log in with his AD account. Even a Excel reporting server elsewhere. It's interesting the log in attempts stop because after you change the password, it works and it only happens after fail over.

Are you sure it's in the SQL Server instance itself and maybe not something with the cluster group? Perhaps it's something like a script on a failover disk or something related with the cluster. I've had scripts do this before, or applications that store login creds. Services are often a culprit as noted, and other potential configs.

  • Our syslog server has the record from the domain controllers and can tell that the attempt came from the SQL Server. Usually it is desktops because they can't type it in properly on the login screen. I'm making some assumptions here but for something like SharePoint that uses constrained delegation, I thought a bad request for a kerberos ticket would come from the desktop. Not sure how that gets logged when locked out. Something is SQL or SSAS startup would be a likely cause, I'm just not sure where to look for those things. – Dustin Mar 25 '15 at 19:53
  • He hasn't got a scheduled task in Windows running under his credentials has he? That's the usual culprit in my experience. – David Fowler Jun 26 '17 at 10:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.