SQL2005 Backup to network fileserver failing (operating system error 5)

Question

SQL2005 Backup to network fileserver failing (operating system error 5)

Viewing 10 posts - 16 through 24 (of 24 total)

You must be logged in to reply to this topic. Login to reply

george sibbald SSC Guru Points: 104210 More actions · Answer 1

yes, they can be changed independently, BUT, when a database backup command is run it is run by the database engine NOT SQLAgent, I think this maybe why you get access denied, local system will have no network rights. This also explains why you get access denied running from a query window.

I think you are going to need to get SQL restarted and try it again.

---------------------------------------------------------------------

Danb7183 SSCrazy Points: 2098 More actions · Answer 2

george sibbald-364359 (9/23/2009)
yes, they can be changed independently, BUT, when a database backup command is run it is run by the database engine NOT SQLAgent, I think this maybe why you get access denied, local system will have no network rights. This also explains why you get access denied running from a query window.
I think you are going to need to get SQL restarted and try it again.

Ok thanks for explaining this George, but I already understand this. I understand that the database engine executes the queries run through SSMS, I'm not doubting this. What I don't understand is why the SQL agent job is failing. I know and understand that the local system will have no network right's and this will explain why the access denied error is appearing when I am attempting to backup the file to another server through SSMS. This doesn't solve my original problem of the SQL Server Agent with a Domain Account (with modify permissions on the destination share) having the 'Access Denied' error when backing up via a scheduled maintenance plan...

Not sure what to do now.

Thanks anyhow

george sibbald SSC Guru Points: 104210 More actions · Answer 3

I believe because the SQLagent kicks off the job BUT the backup command itself is run by the database engine therefore the SQL service account needs access.

Only a bounce of SQL will tell........................:-)

---------------------------------------------------------------------

Danb7183 SSCrazy Points: 2098 More actions · Answer 4

george sibbald-364359 (9/23/2009)
I believe because the SQLagent kicks off the job BUT the backup command itself is run by the database engine therefore the SQL service account needs access.
Only a bounce of SQL will tell........................:-)

Ok, i've traced the database in question and have seen that the job is logging in as the Windows Domain Account and is executing the SQL under the login name of this account (which is what I was hoping)

I'm certain I didn't need to bounce the SQL Server Service on the Development server to get the SQL Agent to successfully write to the folder in question on the file server. I could well be wrong, its just unfortunate that the next available window to bounce the Production SQL Server service isn't til sunday *Sigh*

I'm just thinking of backing up the backup files to the local drive and using an SSIS package to copy the files now!! :S

Thanks all for your help.

george sibbald SSC Guru Points: 104210 More actions · Answer 5

let us know how it goes.

george

---------------------------------------------------------------------

ja_hilton SSC Rookie Points: 49 More actions · Answer 6

As mentioned earlier, when the backup is written it is the SQL Server service that is writing it, not the agent. The agent will log into SQL with the domain account and issue the backup command under that account. However, it is actually the SQL Server service that executes (writes) the backup. Thus the SQL Server service (not the agent) must use a domain account that has access to the file share.

You have to restart SQL Server to get it to use the domain security token associated with the new service account you assigned.

george sibbald SSC Guru Points: 104210 More actions · Answer 7

Danb, did the bounce at the weekend resolve the problem.?

---------------------------------------------------------------------

Danb7183 SSCrazy Points: 2098 More actions · Answer 8

Thanks to you both.

George, I was not able to restart the SQL Server service the previous weekend. The aim is to do it this weekend. In the meantime I have a Maintenance plan backing up to the local server drives and an SSIS package copying the files, cumbersome I know but I was left with little alternative...:(

Dan

philcart SSC-Forever Points: 47794 More actions · Answer 9

I may have missed it in the earlier replies, but have you also checked the NTFS permissions as well as the share permissions.

--------------------
Colt 45 - the original point and click interface

Danb7183 SSCrazy Points: 2098 More actions · Answer 10

philcart (10/1/2009)
I may have missed it in the earlier replies, but have you also checked the NTFS permissions as well as the share permissions.

Sure have....have run into the issue of the two sections of permissions with sharing & NTFS permissions before....

Thanks for the thought though!