Getting MOSS 2007 RTM Backup to work when your SQL Database is one you don’t control

In this post I will be somewhat generic about my machine names and account names. This is partly to keep me in form and not thinking too specifically about my special situation and partly for security reasons. I don’t believe in security through obscurity but I also don’t believe in making it extra easy for an attacker to get the first steps of the puzzle for free.

Configuration of my MOSS 2007 RTM install in development:

  • 1 Server that serves all databases to all development environments (Call it SQLDEV1).
    • I have a specific instance (Call it MOSD) in which I can put all my various MOSS databases and my domain user account (Call it DOMAIN\myuser) is a security admin and dbcreator in the Server.
    • Since I used my domain user account (DOMAIN\myuser) to give all the various configuration services and app pools and datbases an ID during initial setup/configuration of MOSS 2007, I (DOMAIN\myuser) am also dbo on all the MOSS databases in the instance.
    • Additionally, it should be noted that on SQLDEV1, the MSSQLSERVER service is running as one domain user account (Call it DOMAIN\dbuser1), and the instance (MOSD) is running as another (Call it DOMAIN\dbuser2).
  • 1 Server that has all the MOSS 2007/.NET 2.0/.NET 3.0 WFX installs (Call it MOSSDEV1).
    • On this machine, my user account (DOMAIN\myuser) is a local administrator. I also have some additional rights that allow me to log into the machine remotely with a terminal services client.
    • It was a precondition to my getting these rights that I set up the MOSS 2007 install so that all configuration aspects that required local admin access be set up with my domain account’s ID (DOMAIN\myuser). I know this isn’t ideal in some respects, but it seems to work okay in this development environment. This will be the main factor in making this blog entry not be entirely helpful in debugging YOUR configuration problem, but I hope it’ll help anyway.

With no additional special steps, I couldn’t get MOSS 2007’s Operations interface to successfully back up the Farm (via the Central Administration: http://your_server:your_port/_admin/Backup.aspx). I kept getting some progress, but each and every actual site collection failed with a failure message ending in “Operating system error 5(Access is denied.). BACKUP DATABASE is terminating abnormally.” [It should be noted that I was previously getting an error 3, which appeared to be tied to using a non-UNC path in the Backup Location field.]

The steps necessary for me to get it all working were:

  1. Create a shared folder on the system for where you want the backups to go. (i.e. \\MOSSDEV1\Farm Backups\).
  2. Add full control permissions to this share for all three accounts: The service account for MSSQLSERVER on the database server (DOMAIN\dbuser1), the service account for the database instance hosting the content/configuration databases (DOMAIN\dbuser2) and the service account running the MOSS 2007 application pool (my guess of the most likely suspect in this operation – DOMAIN\myuser).
  3. Use the share name you created in the Backup Location text box in the 2nd step of the Backup configuration in Central Administration (i.e. \\MOSSDEV1\Farm Backups\)

I had been using the Admin’s traditional UNC path (i.e. \\MOSSDEV1\d$\Farm Backups\) without explicitly sharing a folder, but that tripped me up, because the SQL Server service account IDs were not Local Admins on the MOSS 2007 box (i.e. MOSSDEV1).
Also it took a bit of digging and asking my DBAs about the service account identities for the SQL Server.

My main reference is about SQL Server Backup in the Microsoft KB #255235.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.