02-28-2012 10:23 PM
Firstly, this is a description of the issue I faced with a client today,
Secondy, kudos to Deepthi at Sage Business Australia for her support whilst resolving this, she should be paid more
Thirdly, how we resolved it
Yesterday afternoon, three ACT! user (site of 53 ACT! users) couldn't access the database, everyone else could.
This morning, no-one could access ACT! via their installed ACT!, but they could access via ACT! for Web.
While we tested, we restarted the SQL Server (ACT7) service, after this, no-one could access ACT! via ACT! for Web.
Opening their production database or the ACT2012Demo database gave a message "SQL cannot connect to an instance".
Trying to restore a backup of their database (restore as, to a new filename in a different folder) errored, it gave the message "Login failed for user 'ACTADMIN' because the account is currently locked out.
Doing the usual fixes of SecurityCmdLnApp, or ActDiag | Server | Server Security | Reset SA PWD failed.
Uninstalling and reinstalling SQL also failed.
The breakthrough came by looking in the SQL Error log (C:\Program Files (x86)\Microsoft SQL Server\MSSQL10_50.ACT7\MSSQL\Log\ERRORLOG) where it gave us the information that there was an "Error: 18456, State: 5".
These posts here shed some light on this error:
Key here is that the error message dialog box itself will always show the "State: 1" regardless of what error state it actually is. You have to open the SQL error log to see what it's actual error state is.
State 5 means it's an invalid userid.
Which didn't make sense initially, as we're logged in as Domain Admin user with full rights.
This Microsoft KB, whilst talking about installation, is the one that gave us the fix: http://support.microsoft.com/kb/2000257
Which was to:
The other two polices are "Debug Programs" & "Manage auditing and security log".
After doing this, reset the file permissions of the the .ADF/.ALF/[dbname]-database files folder and re-attached the db.
Fired up first go.
It just took us six and a half hours to arrive at this point.
What caused it, dunno, but it appears that something had changed some windows security settings/permissions and that is what triggered it all.
Hope this helps the next person that encounters similar.