Unable to backup your SQL server to anything but the local machine???

Recently we  had a situation where a customer was only permitted to backup to a folder on the local hard drive of their SQL server..  If the job was written to another backup device/location, the job would generate a BA1505 error (Cannot open the specified destination directory from the server – it may not exist or the SQL server may not have the correct authorization).

After a little brainstorming,  we changed  the user account the SQL service was running to a domain administrator account instead of the local system account. Then it was time to test out the backup again (with fingers and toes crossed).
The result: The SQL server was successfully backed up to removable drives and other locations!

The explanation:
The trick with SQL is to remember that the “Use SQL authentication or Use Backup User Identity” option is simply to get access to *run* an SQL backup,  it does not give any privileges for the SQL server to *write* the backup anywhere.

The privilege for writing comes from the user account that is running the SQL service. By default this will be the computers Local System account (or similar).This account has high privileges on the local computer, with zero privilege on other computers,  therefore the UNC shares on remote computers will need to be writable by everyone *AND* the remote computer must have “simple sharing” *disabled*.  This is why we temporarily create a share on the BackupAssist machine with writeable-by-everyone privileges (if the SQL server is remote and the destination is a non-UNC path).

If the SQL service user account is changed to a domain account (not necessarily the backup user, but makes the most sense) then the UNC share needs to be writable by that account. Note that running the SQL service as a domain administrator is a security risk, as it  could permit any hacker that can connect to the SQL server the ability to  access domain admin privileges on the network.  Also, database applications running on the SQL server (eg. a website using the SQL server as datastore) may be affected if the SQL service user changes; e.g. a domain administrator contains a greater amount of permissions overall, however a local system account is given more specific permissions when running application services on a particular machine.

This is why we recommend setting up a share on the SQL server, that is *readable* by the Backup User Identity, and backing up to that share using a UNC path. This avoids changing the SQL server account and the potentially risks, and avoids permanently having a share writable by everyone. Another BA job can easily pick up the .bak files from that share, or the post-backup script could do the same.

For more information on the SQL add-on available with BackupAssist, please take the time to look over our white paper located at http://www.backupassist.com/downloads/whitepapers/SQL_WP.pdf.

Hopefully this information helps out when having permission issues such as we were!