1

I'm running SQL Server 2008 and want to setup an agent job to backup all databases. The machine running SQL Server is part of a Windows domain. The destination for the backup files is on another machine, which is not in the domain, so I need different credentials to access it.

I've created a new domain account (SqlBackup), added it as a new SQL account (granted it "backup database") and use that to run the agent job. I logged in locally as that user to map the target network share to a drive (providing it with the necessary credentials).

The script I'm using to backup all databases just calls "backup database" (in a cursor loop across all databases) and writes the backup file to the mapped drive. The problem is: When the SQL agent is running the job as the SqlBackup user, the drive is not mapped.

What did I miss?

  • Update: I got it working. The SQL script calls xp_cmdshell 'net use \\machine\share password /user:username /persistent:yes' to give access to that share with the given credentials. Then I can write the backup file to that share. (Of course, I first had to enable xp_cmdshell, set up the proxy account for that, grant execute to my sql account and add the domain account to the admin group to allow login from the script). –  Jun 30 '09 at 14:12

3 Answers3

2

If your job is executing command line commands, put it in a .bat file to be executed.

Then, within the .bat file, before your command that does the real work, use the net use command (NET USE drive: share_name /USER:domain/user_id password) to map the drive from within the SQL job. You can provide any user id and password you need. Just remember to unmap the drive before exiting the bat file (NET USE drive: /delete) or the next time it runs it will generate an errorlevel that SQL will interpret as a problem. When you execute the .bat file from job step, "call" the .bat file (CALL MY_BAT.BAT) or SQL can jump to the next step before the .bat step has completed.

HopelessN00b
  • 53,795
  • 33
  • 135
  • 209
LMillsap
  • 21
  • 2
1

IIRC, drive mapping is only going to work with interactive login - and I believe your backup runs as a service. Will the SQL backup task allow you to add credentials for the target drive using a UNC path instead of mapping a drive?

Kara Marfia
  • 7,892
  • 5
  • 33
  • 57
0

Services cannot use mapped drives, and as far as I know, there are no ways to pass credentials using the UNC path. If the non-domain server can't be added to the domain, my only suggestion would be to grant write permissions to that share to "Everyone."

An alternative might be to have the backups dumped to their local server drives and then create a "collector" batch job on the non-domain server to pull the backup files to their final destination.

Ed Leighton-Dick
  • 1,094
  • 1
  • 7
  • 12
  • As it turns out, you can use NET USE to pass the credentials to access a share (see the comment to my original post). We would have written the backups to the local drive, but that would have required too much space. –  Jun 30 '09 at 14:15