2

Original symptom: User sa locked out, too many bad attempts.

Problem was sudden (no issues before), and pervasive (couple of fails in one day).
As this was a new roll out of SQL backend to Sage300, Crystal Reports and Misys inventory package, we could reboot to recover.
(We have since confirmed a Windows Login will allow us to unlock account. No more reboot.)
After seeing the recommendation on how to "disable enforcement policy", we halted the lockouts.
(And yes, there was a windows update, and SQL "uses the policy of the underlying OS" - did Windoze something change?)

Ahh! But here the mystery deepens!
Our log indicates a login failure for user sa at each Crystal/Sage report generated. 

Login failed for user 'sa'. Password did not match that for the login provided. [CLIENT: <localhost> -or- 192.168.x.x of machine]    
Error: 18456 Severity: 14 State: 8

The report IS generated and delivered to the user. No indication of an issue/error.
We tried to intentionally enter a bad password; exact same error in the logs, BUT the user gets a failure dialog box.
Please note: with the exception of occasional fat-fingered logins, we had no login failures in the logs up until this started. The verbose message is present (duplicated) in both the WindowsNT:Application and the SQL server logs.

So the problem is still there! But the user doesn't see it. But it is in the logs.
WHY? I expect this will come to bite me later, if I don't address today.
Since this is new roll-out, and end-user expects some teething pain, I have some leeway to try things, now.... Maybe not so much, down the line, if/when this blooms into a real issue.
Any suggestions??

Improve this question
6
  • 10
    Stop using the sa account for user processes. This is a terrible idea for so many reasons. It also sounds like you are possibly using it for multiple, disparate processes which can make troubleshooting things like this a pain. Create a new account for Crystal, then for other processes that use sa as well until you eventually stop using the sa account entirely. At that point, you'll likely identify the process in question causing you problems.
    – John Eisbrener
    Commented May 9, 2019 at 16:30
  • Yes, I was about to say the same thing. Using the "sa" account for user processes is really a bad idea. You probably want an account with much less privileges for user account.
    – Danielle Paquette-Harvey
    Commented May 9, 2019 at 17:26
  • John, Danielle - Agreed, the 'default username' is not the preferred way to go. Unfortunately, not my call. (Software mfg contractor/consultant; hundreds of hours & thousands of $ - then, they turn over the keys.) On the bright side, this is a dedicated server, one process (inventory), no other users or tasks in this database or machine. So (for the moment) I am willing to allow 'sa' as the user. Doesn't address the errors in the logs.
    – Beekeeper
    Commented May 9, 2019 at 18:25
  • 1
    OK, Tony - Thanks. I appreciate your desire to not work here. And with the arrogant attack you are throwing at me, I am not sure I would want you to work here. I do not dispute that using the default-user/god-privilege-user is bad. And, yes there is the potential for bad actors to take advantage. As a one-man IT department, in a sub-100 user environment; I need the car to run, THEN I can get to air-bags and seat belts. If it don't run - then safety has no value. As it stands, NOBODY; good, or bad, is getting in.
    – Beekeeper
    Commented May 9, 2019 at 19:39
  • 1
    Thus I made a request to the community. Is this kind of response what 'the community' considers 'help'? I have followed the rules of the site and provided information as best I can. I am in the process of creating a new user, as suggested by John & Danielle. I can do without the insults, thank-you. I will report what I can determine with a non-admin user, meanwhile does anyone have any insights, not insults?
    – Beekeeper
    Commented May 9, 2019 at 19:39

1 Answer 1

Reset to default
3

Alright, I have a resolution maybe, and an explanation for the log entries.
Following the advise of @John-Eisbrener & @Danielle-Paquette-Harvey (did I do the attribution right?) In particular John's advise of a different user for Crystal I learned the following.

Issue: Log entries that say that the user (sa originally) has entered bad credentials.
Discovery: When a Crystal Report 'template' is opened, there is no log entry.
When the report is 'executed', a dialog box is presented with the users name pre-populated.
Behind the scenes, Crystal apparently sends a request to the OBDC connector with the username that the report database was connected to, without a password, which (of course) fails.
This is the source of the non-fatal, 'credential failure', log entry.
[This was reported (tangentially) here: I am getting a failed login message in the SQL Server error logs however, nothing actually failed? four years ago!]
Note that if a report has five 'template pages', for say an end-of-month report; five attempts rapid fire, and can cause a user lockout unless 'enforcement policy' is loosened! Thus my first problem.
At this point the user enters the (presumably) correct credentials and the report queries the database and populates the report.
If the user has an open instance (say Sage300, with credentials); then the report is generated without a credential dialog box or an failed password in the logs.

So, I will call the mystery log entries 'benign', and close that issue. I will (of course) take the advise for further lock-down of my systems. I may be small, but that does not absolve me of 'best practices'. (Read: Bullseye Breach -G.Scott)
Thanks again to the community. -30-

Improve this answer
1
  • Definitely feel free to pick your answer as the solution
    – clifton_h
    Commented May 11, 2019 at 17:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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