Backup To UNC / Mapped Drive 2

[Skittle]

Bit of an old chestnut I know but I can't solve this one.

I can issue a BACKUP command from the query window to the local C drive and it backs up the database.
I cannot backup to a mapped network drive.

The SQL Server Agent account is 'LocalSystem' which I assume will not have the authorities to write to a mapped drive.
However I am running the backup from a query window in SQL Server Management Studio from an account that is windows authenticated sysadmin.
The windows account can access the mapped drive in my windows session and copy files with windows explorer but for some reason my
SQL Server Management Studio will not do it.

I figured I need to run the backup command as a T-SQL step in an SQL agent job - with a 'Run AS' value of a proxy account.
I have set up a credential and a proxy to the same domain windows account that can manipulate data on the mapped drive.
However the T-SQL step won't let me select the proxy account to run the step!
At first I though I had not set the credential and proxy up properly but I can select it on steps for an SSIS package, just not a Y-SQL step.
I don't understand why.

So my questions are:-
Why Can't I run the backup command from a query window to a UNC location even when I have the windows authorities to the mapped drive location?
Why do T-SQL steps not allow you to select a 'Run As' proxy?
Is there anything else I can do to achieve a backup to a mapped drive from inside SQL Server without changing the Agent Service account?







Dazed and confused.

Remember.. 'Depression is just anger without enthusiasum'.

 

[fredericofonseca]

correct thing is to give the sql server agent user permissions to the network drive you need to write the backups to.

if your DBA allows cmdshell(which I would on a very restrictive way) you may be able to do it through a cmd file - but this will require a few other things and a good DBA will not allow it.

others may reply to you on the other questions

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[yelworcm]

The reason you can not do the backup is that while domain\user is issuing the command to SQL Server, it is localsystem that is trying to carry out that command. Think of it in terms of office delegation. You may have rights to the server room, but the file clerk does not. If you tell the file clerk to do something in the server room, he will not be able to comply. The same thing is happening here.

 

[Skittle]

OK thanks chaps.

I will change the agent account and assign permissions.




Dazed and confused.

Remember.. 'Depression is just anger without enthusiasum'.