0

We have the following situation and am wondering if anyone can suggest a way of determining the cause.

One of our SQL Developers can log into, and use, the instances/databases fine and has a SQL logon with sysadmin privilages. He works from approx 8am to 5pm and has no issues.

However we get random "Insufficient Permissions for Action Attempted" for his SQL logon, at random times, with the message:

Login failed for user '(user)'. Reason: Password did not match that for the login provided. [CLIENT: xxx.xxx.xxx.xxx]

The (user) is this developer's Logon, and the CLIENT IP address is the PC he uses on a daily basis.

The only real difference between the developers is this user is using SSMS 18.0 and the others are still on older versions (17.x)

  1. The messages occur all times of the day. The developer doesn't work remotely and would definitely not be trying to log in at 3am.
  2. At one point, we asked the developer to shut down his machine at the end of the day. The alerts stopped happening for a few days but eventually returned.
  3. I've looked at running processes and there's nothing obvious as to what might be causing it.
  4. We've looked into a variety of other potential things, such as incorrectly configured SSIS connection managers.

Does anyone have any suggestions on actions that may lead to finding out the offending "thing" - would wireshark traces help or is there a better way of figuring it out? Thanks for reading.

  • Have you checked Windows event viewer on the machine with the IP listed on the error message you're getting from SQL Server? Maybe there's another log record related to that error in there. – Ronaldo Mar 11 at 12:53
  • Has the password changed recently of this user? – Learning_DBAdmin Mar 11 at 12:55
3

I would look for some kind of automated process... either a SQL Agent job, a batch file connecting to SQL, etc. There is some good chance that the login use for this process does not have the right password.

You can create an extended event (or a trace) and capture the "Error Reported". Make sure you include the "application name" in the collected field and you should see something like :

enter image description here

This should give you some good info to find the cause of your issue.

| improve this answer | |

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.