up vote 1 down vote favorite
share [g+] share [fb]

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?

link|improve this question
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). – user11177 Jun 30 '09 at 14:12
feedback

2 Answers

up vote 1 down vote accepted

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?

link|improve this answer
feedback
up vote 0 down vote

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.

link|improve this answer
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. – user11177 Jun 30 '09 at 14:15
feedback

Your Answer

 
or
required, but never shown
discard