Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations wOOdy-Soft on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Backup To UNC / Mapped Drive 2

Status
Not open for further replies.
Skittle

Skittle

ISP
Sep 10, 2002
1,528
US
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'.
 
Sort by date Sort by votes
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

FAQ219-2884
FAQ181-2886
 
Upvote 0 Downvote
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.
 
Upvote 0 Downvote
OK thanks chaps.

I will change the agent account and assign permissions.




Dazed and confused.

Remember.. 'Depression is just anger without enthusiasum'.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

bethabernathy
  • Locked
  • Question
Front End Access 365 Database dsn Connection Fails when attempting to link to 2016 SQL server
Replies
1
Views
529
dhookom
dhookom
dgillz
  • Locked
  • Question
SQL View not available to Excel Power Query
Replies
1
Views
532
dgillz
dgillz
torquinian
  • Locked
  • Question
Error 0x80131904 when trying to connect to database
Replies
0
Views
537
torquinian
torquinian
intel233
  • Locked
  • Question
Backup/Restore DB from SQL issues
Replies
2
Views
307
intel233
intel233
dgillz
  • Locked
  • Question
SQL Server timeouts after updating workstations to Windows 10
Replies
0
Views
502
dgillz
dgillz

Part and Inventory Search

Sponsor

Back
Top