June 25, 2014 at 12:54 am
SQL server backup issue
When backing up databases on SQLSever2012 to a backup server I am receiving the following error:
failed with the following error: "xp_create_subdir() returned error 5, 'Access is denied.'"
." failed with the following error: "Cannot open backup device
I was able to backup databases to the same location on both SQLServer2008 and 2005 without error so I don't know why I can't backup to the same location on SQLServer2012 as they're setup the same.
When connecting to the server and opening Windows Explorer I'm able navigate to the backup location by entering the network location e.g \\backupdatabase\backup and I can view the folder and create files in this location but when trying to backup to this location I still receive "Access denied" as above.
The account used to login to the server has sysadmin permission and full access to the backup servers folders
The account used to login to The "SQL Server Agent" within services is running under a domain account and has sysadmin permission and full access to the backup folder.
If both the SQLServerAgent account and the account used to login to the server have sysadmin permissions and full permissions to the backup folders then why would access be denied? Please let me know if im missing another step.
June 25, 2014 at 4:04 am
I'd double check the security for the account that's running SQL Server. The error you're getting is a security error. It doesn't sound like that account has the correct privileges.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 25, 2014 at 6:26 am
The SQL Service is running under a Windows NT account and therefore doesn't have access to the backup location.
My understanding is that backup jobs run under the SQLAgentService login though and not the SQLService login so as long as the SQLAgent account is a domain account and has access to the backup folder it should be working.
Is this correct? Or do backups in fact run under the SQLService login and that login will need permissions to the backup folder and not the SQLAgent account?
If so I will need to change the SQLService login to a domain account so that I can provide it access to the backup folder that's on another server which i was hesitant on doing unless required as I'm trying to avoid restarting the server.
June 25, 2014 at 6:38 am
Nope, the Agent doesn't do the backups, the SQL Server executable and service does. That needs the permissions.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply