11-01-2017 10:38 PM
I have not seen anything new since the last post here. (My posts were the two most verbose ones in this thread). I have subsequently upgraded from v18 to v 19.3, not yet to v20. For me, solving the problem came down to this, buried in my post on page 2 of this thread, dated 2016-10-23 at 07:18 pm:
"I have to change the SQL Server (ACT7) instance so that it runs under the Local Service account, not the Local System account."
I later found (also noted above in this thread) that I could also run the SQL instance under another domain admin account as well, and I ended up using a (non-default) domain admin account that I use just for running services to keep it separate from domain management functions.
So you might try either Local Service or a domain admin account and see if it solves the problem for you. The issue was with the fact that the SwiftPage developers, although they embedded the SQL instance installation within the Act setup 1) did not fully consider the implications of the choice to use the Local Service account and 2) did not present any place in the setup where I could specify a different account. At least with that choice, it would be my choice--and I would therefore have to take responsibility for its consequences, even though, in my opinion, this should have been more thoroughly testing in actual corporate-type server environments.
As it was, my backups failed for the entire server for several months until that failure got my attention, whereupon I figured all this out and manually went to the service and changed its account.
11-02-2017 02:19 AM - edited 11-02-2017 02:22 AM
Thanks for your input on this one - I'll go ahead mark the post recommending the service account switch as an Accepted Solution to help raise visibility in case anybody is having the same issues.
Incidentally, with Act! now supporting SQL Server 2016 as of v19.2 - I wonder if the same issue occurs with this version?
11-02-2017 02:32 AM
But if there is any way to pass this along to the developers, It might be useful to do two things, if possible, in the setup process:
1. Pop up a window having the recommended service account for the SQL service as the default but allowing the user to change it. I see that in many other setup applications, and it at least allows the end user (or IT support, in my case) to make a choice.
2. Somewhere in the setup documentation (or even on that popup window), include a note regarding the fact that "conflicts between VSS-based backups and Local System account for running the SQL service have been reported in some cases..." Or something like that. Or perhaps, if there is now a KB article, just a reference to it in the setup documentation. That may save some other poor bloke the trouble of having company backups fail for months before finally working down to this thread.
11-02-2017 06:16 AM - edited 11-02-2017 06:19 AM
Changing the startup to a Local Service does make the backup work properly. However, ACTDiag no longer functions once this is done. In addition the Repair and ReIndex database functions no longer work. I would not call it a solution.
The errors that are received indicate that the "sa" user can no longer login to the SQL database.
11-02-2017 08:33 AM
11-02-2017 08:45 AM
I have returned it to the Local System account. When that occurred I could no longer login to the database and had to use ACTDiag to reset the "sa" password. After I did that I was able to access the database. Also, I am able to back up now. Could it be that resetting the "sa" password also allowed the SQL Writer process to now access the database?
11-02-2017 11:40 AM
I will have to try that sometime at night when users will be unaffected. As with most workarounds, the folks that discover them (us, that is) can never be sure what else is involved, and it is perilously easy for a workaround that looks like a miracle to me to break something else that may not be evident for years. Maybe it does come down to resetting the sa password.
But...since I am just the installer & IT management for my users (not an end user myself), are you aware of anything I might break by changing the sa password? It is certainly something I am willing to try in case it can elucidate the issue further here.
11-02-2017 12:18 PM - edited 11-02-2017 12:19 PM
Our setup is as follows: Each user has a local database which synchronizes to a hosted "master database". The local database can easily be recreated or restored from backup.
Everything I have tried since returning the SQL back to "Local System" account seems to work with the exception of the Outlook interface. However, that could be due to the Windows 10 Creator Update (Fall). The Outlook interface was working before that.
I have tried data base repair, data base reindexing, data base synchronization, data base lock and unlock, and data base backup.
IMHO, I would take a wait and see attitude if this database is on a server where the users access the SQL over your intranet.
11-03-2017 02:19 AM
Changing the SA password is completely harmless if done the right way.
When Act! is installed the SA password is randomly generated and securely stored in the program - the 'sa' or server admin account is what Act! uses to log in to the SQL server when making changes that require full access to the schema/permissions.
If the SA password if changed from ActDiag, it is re-randomized and once again stored in the program.
Issues will occur if the SA password is changed directly in the SQL Server - as Act! will not have this new password stored. This can sometimes happen due to outside circumstances and lead to the situation rgwagner described where Act is unable to log in to the SA account - which can be fixed by resetting the SA password from ActDiag.
Without knowing the specific details of why VSS backups fail under the Local System account, I can't really speak to the nature of the issue - but it has to be said - I wouldn't expect changing the SA password to affect the situation.