Unable to restore from network share - Server 2012/SQL 2012

Comments

• Options

  toad2898 Posts: 41

  September 24, 2014 4:02AM

  I'm trying to use Server 2012 / SQL2012 but can't even backup to a network share let alone do a restore. It's a similar issue though, I know the account that runs the SQL agent job has read/write on the share but the backup job fails.
  
  I seem to be jumping through a lot of hoops to try to get this software to work on this combination tbh

  0 · Share on Twitter
• Options

  jhboricua Posts: 41

  September 26, 2014 3:23PM

  Crap, you're right. Since it was a new instance I had not setup the backup jobs yet because I first was trying to restore the database for the application we're trying to migrate. But I just looked at the job we setup for backing up the system databases, which also writes to the same network share and it is also failing.

  0 · Share on Twitter
• Options

  petey Redgate › Posts: 2,358 New member

  September 26, 2014 9:33PM

  It is the SQL Backup Agent service startup account that requires read/write access to your network shares, not the SQL Server Agent service startup account. The SQL Server Agent service startup account just needs to have rights to run the SQL Backup extended stored procedure, and rights to backup/restore the databases in question. Pls see http://www.sqlbackuprestore.com/settinguprights.htm

  Peter Yeoh
  SQL Backup Consultant Developer
  Associate, Yohz Software
  Beyond compression - SQL Backup goodies under the hood, updated for version 8

  0 · Share on Twitter
• Options

  jhboricua Posts: 41

  October 2, 2014 12:40PM

  So I got this working now. When examining the logs I saw the following on the failed backup jobs:
  
  "SQL Backup could not access [HKEY_LOCAL_MACHINESOFTWARERed GateSQL BackupBackupSettingsGlobal(LOCAL)] running as user [svcRedGate]."
  
  So I had to give the service account that runs the SQL Backup Agent account {svcRedGate] permissions to the registry keys at [HKEY_LOCAL_MACHINESOFTWARERed GateSQL BackupBackupSettingsGlobal(LOCAL)].
  
  Then I tried to run a backup again and it failed with:
  
  "Warning 402: Failed to open local data store: Access to the database file is not allowed. [File name=D:Red GateSQL BackupData(local)data.sdf]"
  
  Apparently the service account didn't have permissions set here either, to Regdate's OWN FILES!!! Again, I gave the service account that runs the SQL Backup Agent service permissions to the Red Gate directories.
  
  After doing this, backups were successful. I can now also browse the network share for restores, which pretty much confirms to me this was NEVER a share permission issue but missing permissions on RedGate's end. I then asked the support technician I was working with why were this permissions missing in the first place? During the install I was asked both for the path where I wanted the tool installed and also under what account I wanted the Backup Agent service to run and I provided that info. So, why didn't the installer grant the account the permissions it NEEDED for the tool to work in the first place? This is the response I received:
  

  ---

  In regards to this situation, I definitely understand your frustration, and how challenging it was for you to properly set up the permissions to get the software running correctly.
  But ultimately, the way our tool handles permissions is a design decision. The majority of our customers prefer to have that granularity and control to configure permissions within their environment, for the sake of security (even though it is laborious). In a "locked down" environment where there are intentionally multiple layers of permissions for security reasons, it would be concerning if a 3rd party tool can easily and automatically bypass these various layers.
  
  At the end of the day, it is going to be hard for us to meet everyone's needs. As you can see from the forum posts, there are definitely users who feel the same way as you (regarding this particular issue), but we would suspect that there would be an even larger and more critical backlash if we were to have "an automated permission handling".

  ---

  
  I personally find the explanation to be absurd and, in my opinion, I see this as a design flaw, pure and simple. There's nothing about this that speaks security when the tool functionality is BROKEN unless I manually do the steps above.
  
  Back when we were doing SQL backups via Commvault, I didn’t have to manually give the account that runs the Commvault service permissions to the Commvault’s directory so it could read its own files or to the Commvault registry keys so it could access its own settings. Commvault handled that itself when it was installed. All I had to make sure was that the service account had permissions to the final destination of the backup files.
  
  When doing SQL backups natively via SQL, I don’t have to give the SQL Agent service account permissions to the directory where the SQL binaries are nor I have to give it permissions to the SQL registry keys. All those are handled by the installer when I specified the service accounts and paths. All I have to make sure, again, is that the service account has permissions to the final destination of the backup files.
  
  The ONLY time I've had to mess with an application in this manner has been because the application was either poorly designed or it's an ancient app that was not designed with modern Windows systems in mind. Which in both cases mean they didn't follow MS design best practices for applications, especially in regards to security. These are the apps that want to run under elevated rights (think local admin credentials) in order to work. So, in order mitigate risk and not have them run with elevated rights, we end up having to mess with manually assigning permissions to regkeys and directories/files. In my mind, there's no excuse for a modern application such as RedGate Backup Pro to require this level of meddling.
  
  /end rant

  0 · Share on Twitter