Community
Showing results for 
Search instead for 
Do you mean 
Reply

SQL cannot connect to an instance & ACTADMIN is locked out.

Bronze Super Contributor
Posts: 1,679
Country: Australia

SQL cannot connect to an instance & ACTADMIN is locked out.

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 Smiley Happy

Thirdly, how we resolved it Smiley Very Happy

 

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:

  1. Log on to the computer as a user who has administrative credentials.
  2. Click Start, click Run, type Control admintools , and then click OK.
  3. Double-click Local Security Policy.
  4. In the Local Security Settings dialog box, click Local Policies, double-click User Rights Assignment, and then double-click Backup Files and Directories.
  5. In the Backup Files and Directories Properties dialog box, click Add User or Group.
  6. In the Select User or Groups dialog box,type the user account being used for setup, and then click OK two times.
  7. Repeat the procedure for the other two policies mentioned in the Cause section
  8. On the File menu, click Exit to close the Local Security Settings dialog box.

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.

 

Ben.