File Activation Failure

This post describes errors in a SQL instance after changing the windows account running SQL Server.

Environment – a Windows 2008 server running two SQL Server 2008 R2, SP2.instances. One instance is the default instance, the other a named instance.

Using SQL Server Configuration Manager, I changed the SQL Server service account for both the default instance and the named instance to run under a local account (.\ServiceAccount).

The change succeeded on the default instance, but on the named instance the databases were unavailable. This was not immediately obvious, but only became clear after re-starting the server.

To prove that the issue was related to the database service account change, I changed the database account back (to localsystem). When I re-started SQL Server, the databases were available.

The SQL Server error log held the following error messages:

File activation failure. The physical file name “L:\Log\Accounts_log.ldf” may be incorrect.

A file activation error occurred. The physical file name ‘L:\Log\Accounts_log.ldf’ may be incorrect. Diagnose and correct additional errors, and retry the operation.

Error: 5105, Severity: 16, State: 1.

Unable to open the physical file “L:\Log\Accounts_log.ldf”. Operating system error 5: “5(failed to retrieve text for this error. Reason: 15105)”.

Error: 5120, Severity: 16, State: 101.

Resolution

I granted the new account (.\ServiceAccount) Full Control to both the log and the data directories.

The fact that changing the service account worked on the default instance but failed on the named instance suggests – in my view – a bug with the SQL Server Configuration Manager.