Granting backup user access to Act database

The short question: how do I determine which ActiveDirectory or local users have access to the Act database so I can configure one to use for the SQL VSS Writer service so I can actually back up my server now that Act is installed?

The long story: I installed Act Premium a couple of months back. I was admittedly not monitoring my VSS-based backups closely enough and just now discovered that my backup on this server has been failing since the exact day I installed Act and, as you can see below, the failure is directly traceable to Act's SQL server implementation. In fact, this server does not just host Act but also stores our users' home folders--for 50 users. So the backup failure produced by the faulty default SQL server configuration in Act setup was not limited to the Act database; it prevented all backup of the entire mission-critical server!

The backup failure reason was given as this: The VSS writer 'SqlServerWriter' failed during the 'DoSnapshotSet' phase with error WriterErrorNonRetryable. After some serious digging to determine the meaning of this, including the usual many false leads, it finally became clear that this error occurs when the SQL VSS Writer service does not have access to a database, so I began looking for something that could be explained by some faulty SQL server setup assumption on the part of the Act developers. Sure enough, I found it: the SQL Server VSS Writer was automatically configured to log on as the Local System account. I inferred, based on the backup system error message, that this does not have access to the Act database. Sure enough, my backup worked as soon as I changed this service to log on as my domain admin account. Although I now have a temporary workable solution, there are two big issues with this:

This is not an good configuration, since it leaves a one-off service on one the servers in our ActiveDirectory domain running under the default domain admin account. Good policy dictates using a separate AD or local account for running services. So I want to change this to another account, but I need to know how to ensure that other account has access to the Act database or grant it. Given a choice, I would probably use the new local admin account I configured when I changed from Act Premium to Act Premium (web access) last week. But it seems I would have to just try this to see if that user has access to the Act database. Still, some guidance here would be much appreciated!

Why in the world was none of this in the Act setup documentation? This is my second major Act-induced head-beating-against-wall event for me this week, and it enforces the impression I get that the entire Act product implementation is focused on fast and simple setup at the expense of flexibility and reliability down the road. That approach might work for two-bit implementations in small peer-to-peer networks that pay little attention to shared resource management, system security, or solid backups. But it is not so good in an enterprise environment.

For that matter, reading up on manual SQL server installation in the Act KB explicitly tells me to configure SQL Server using the Local System account: http://kb.act.com/app/answers/detail/a_id/38099. But without instructions as to how to grant the Local System access to the Act database(s) this is incorrect in an environment that requires VSS-based backups. If I were a SQL expert, I might have seen this coming despite the lack of related documentation, but I am not, so I was largely at the mercy of the Act developers here.

Even had I suspected a need for non-default manual configuration in or following the SQL setup process, the guide would have provided problematic guidance. It is entirely possible that I was given the option of choosing a logon account for the SQL services at initial installation, but without some clue from the Act developers that there are considerations beyond getting their product up and running as quickly as possible, I would have made exactly the same choice: Local System account. In the end, although I was able to eventually get it running, where was the "Read this first if you are installing this in an environment where you do system backups"? It seems that either the Local System account should have automatically been granted access to the Act database or that there should have been some indication of 1) why one might want to use something besides Local System and 2) how to do that (i.e. ensure that the selected user has access to the database).

So as far as I can tell, I find Act to be a superior CRM system that once again disappoints me when it falls far short of the mark when it comes to fitting into serious Information Systems infrastructures. Once I installed Act, my sales staff were happily off and running with their new CRM system. But in the meantime, my backup for an entire mission-critical server began failing.

And, to be entirely fair, it is not as though a correct configuration is that far away; it is apparently just ignored in the setup instructions in favor of a quick setup. That is, I think that an enhanced installation guide would solve this problem, assuming that the Act developers are even aware of the issues that should be addressed.

Re: Granting backup user access to Act database

Do you have a link for that? It did not work for me when I just switched the SQL Server VSS Writer service to log on as the Local Service account; that is, VSS backup fails with the same error that ultimately leads me back to the inescapable conclusion that the Local Service account also does not have access to the Act database any more than the Local System account does.

I do have to correct something from my initial post: I found that the domain admin did not, in fact, work. I had forgotten to restart the SQL Server VSS Writer service when I changed the log on to be that domain admin account. So the backup software was simply skipping the SQL backup. Once I restarted the service, backup was right back to failing based on lack of access to the Act database. And I find this doubly odd because I can configure an ODBC DSN to the Act DB using Window NT Authentication and get in to read and write data; that is, an account that apparently has full access to the Act data does not have access to the database when it comes to managing the VSS backup of the database.

I also tried configuring the service to run with a domain user account whose username & password matches an active name/password combination in Act. But then, although the service starts correctly, running vssadmin list writers does not show the SQLServerWriter as it does when the service is properly configured with a local or domain admin account, presumably due to some rights-related issue--and this causes the backup process to seem to succeed only because it does not attempt the SQL-specific backup. So then I tried making that user (very temporarily) a member of the Domain Admins group. Then the service would start and SQLServerWriter show up under vssadmin list writers; however, the backup still failed due to SQL VSS issues; that is to say, no access to the database.

So all I can do until I get a solution is to stop the SQL Server VSS Writer service. This prevents the backups from failing completely, so at least, Act is no longer preventing backup of the rest of the data on this mission-critical server; however, I would guess that it also limits the (Act) SQL DB backup to being a simple file backup rather than a SQL database that needs to be backed up in a consistent state.

Now, to be fair, this is way over the line into SQL Server management. I suspect that it may be possible, by installing the SQL Management Studio, to get in there and grant explicit rights to some user or group; however, I am trying to keep my SQL Express here as plain as possible, since Act is but one very tiny drop in the larger bucket of a 50-computer organization that has several servers, many server-side applications, and thousands of shared files. Act should not have to dominate the entire process just to get it installed and prevent it from hosing my backups.

So, the fact that these are really SQL issues does nothing to reduce the culpability of the Act developers. With no documentation, not so much as a "don't expect our product to work with your backup system" warning, Act purports to function perfectly with a simple installation in an environment where this is simply not the case, and I am forced to conclude once again that the Act developers are either 1) ignorant of the situation or 2) aware of the situation but have intentionally ignored it. If the former, shame on them for not testing deployment in what is a very common scenario. If the latter, though, then they may just assume that everyone can be convinced to do manual backups through the Act UI or, even worse, invoke the Act Scheduler to do backups. These are both terrible ideas on managed networks where backups are automated at the server level so that users are free to go about their daily work without having to babysit IT systems or being tempted by a vendor to interfering with will-thought-out system processes. So I cannot tell if I should interpret the situation as "Huh? What's a managed system?" or "In your face, buddy. Act configuration and management is supposed to be done by sales staff, not by information systems managers."

And yes, I know I could call Act tech support; however, the nature of my technical questions is that so far, they have struck out completely on even understanding the complexity of my questions, much less providing a relevant answer, so I have found myself very much on my own but for the good graces of you folks here on the forum.

Re: Granting backup user access to Act database

I just looked and couldn't find it. If they didn't have access to the database no one would be able use Act! so it must be something else they don't have access to. I haven't run into the issue that you are experiencing but I just realized that the clients that I have that are running VSS aren't running Act!. I'm sure I'll run into the issue over the next year or so. We're putting in a lot more backup systems thanks to ransomware issues.

Re: Granting backup user access to Act database

Agreed. I just do not understand enough to see why a user that has full read/write access in the DB via ODBC does not also have access to back up the database via VSS

It is just somewhat frustrating to have yet another, "Just click here, and life will be good" installation package (i.e. Act Premium 18) that, in fact, does not fit well into larger environments without serious amounts of extra research and work. Things like this generally includes a phase where all the end users are imperiously looking at me with arched eyebrows asking why I couldn't figure it out when all they had to do at their last job was to stick in the disk and click OK. After all, the Act developers wrote it, so it must work just like they said.

I am posing the same question up to my backup software vendor's site (Dell's RapidRecovery), although I understand it is even more generic than that, being somehow related to the SQL Server VSS Writer service itself. I will post my finding if I get any. In the meantime, I just had to disable the SQL-specific VSS service. This will at least let my backups function for the many files and other functions of that server. But I am pretty sure that the resulting purely file-based backup of the Act SQL DB (i.e. non-SQL-aware backup) could result in an unrecoverable DB in the event of a crash.

And I hope that someone in Act dev that cares is monitoring this thread as well...I am relatively certain that any attempt to explain something of this complexity to Act's overseas support would be a dismal failure.

Re: Granting backup user access to Act database

Yes. Shadow Copies is enabled for all drives on all my servers. In the case of malware encryption or user-deleted files, retrieval from Shadow Copies is generally a lot faster than getting them from backup, so I really have my backups running for disaster recovery. Shadow Copies kind of idiot-proofs the system to some degree.

Re: Granting backup user access to Act database

If it really is an access issue you could give whatever user (ie Local System) explicit access to the Act! database folder and everything below it. That might solve your problem and wouldn't represent much of a security threat.

Re: Granting backup user access to Act database

The folder rights are not the ones in play here. I already tried configuring the SQL Server VSS Writer service with my default domain admin account, and that has full rights to everything. And that was just one of maybe 15 different configurations I tried today. I even tried creating an Act user account to match a domain admin account (same username & password) and using that for the service. All to no avail. At least five hours on this task today.