Permissions to Backup to a Network Share with Ola Hallengren’s Maintenance Solution

I recently moved my SQL backups to a new file server and got tripped up granting permissions to a couple of servers. When I figured out my mistake, I realized that I had made the same mistake when I originally implemented Ola Hallengren’s Maintenance Solution a couple years ago, so I’m going to blog about it in hopes that I can save myself an hour or so next time!

The first gotcha: if you’re using separate accounts for the SQL Agent Service and the SQL Database Engine, the SQL Database Engine account needs full control of the network drive and the shared drive.  Per the documentation, BOTH accounts need full control of the network drive and share for the backup to succeed, but on SQL 2012 running on Windows 2012 R2 writing to a Windows 2012 R2 share, only the Engine account needs permission on the remote server to backup successfully.

If you grant permission to the SQL Agent Service account only, the error message that is generated in the DatabaseBackup log file isn’t very helpful: xp_create_subdir() returned error 183, ‘Cannot create a file when that file already exists.’

The second gotcha applies to SQL Server Express edition installations that are running the SQL Database Engine under the NT AUTHORITY\NetworkService account. For these installations, permissions need to be granted to {DomainName}\{ServerName}$.  So if my domain is DOMAIN1 and my server is named Server1, permission would be granted to DOMAIN1\Server1$.

Permissions to Backup to a Network Share with Ola Hallengren’s Maintenance Solution

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s