I'm trying to ship transaction logs from Server A to Server B. I keep getting permission denied errors when the agent tries to run because it isn't using the right credentials to connect to the backup folder share.

  • Neither server is in a domain (and that's not an option).
  • I've setup a shared folder on Server B in which to place the files, but Server A needs access to write to it. (Or vice-versa, Server B needs to read from a share that Server A provides.)
  • I can't grant permission on Server B's share to Server A's SQL Agent because the servers are separated.

Is there a way I can specify the login credentials for Server A to use when connecting to Server B?

I'm referring to the first textbox on this screen:

alt text


i think if you make the login credentials have the same username & password on each server it might work. Or have you already tried this?

  • this is a bit dated but does the machine name need to be the same as well? – Nai Mar 3 '10 at 10:26
  • 1
    err.. no. machine names that are the same will cause problems, dont do that – Nick Kavadias Mar 3 '10 at 11:47
  • okay, I followed the steps exactly but still couldn't get it to work? Do I need to make the accounts a member of SQL Server Agent Group on the primary server as well? So just to make sure I getting this right. 1) create same account/pass for both backup and primary server 2) set both sql server and sql server agent services to run from these newly created accounts on both the backup and primary server 3) enable file sharing permissions on the backup folder on the primary server thats it right? – Nai Mar 3 '10 at 14:09
  • 1
    yes, sounds right. Make sure that the backup folder on standby has enough NTFS and share permissions assigned to the service account. What's your error? maybe post it as a new sf question? – Nick Kavadias Mar 3 '10 at 14:42
  • yeah probably thats a good idea. Thanks for the assist mate. – Nai Mar 3 '10 at 14:45

You'll need to create an account on both servers with the same name and the same password. You'll need to configure the SQL Server and SQL Agent to run under these logins on which ever server is going to be pushing/pulling the files to the other machine.

  • Thanks man, that worked. I've got to give the answer above credit since he replied back first. Sorry :( – The Matt May 28 '09 at 6:01
  • Thanks everybody, you saved my day today. Your next PanGalactic Gargle Blaster is on me :-) – ila May 10 '10 at 15:51

Set up a local user account on the source system.

Set up a local user account on the target system with the exact same username and password.

Set the source system's MSSQLSERVER service (or the appropriate instance of that service) service account to use the newly created local account.

Create a share on the target system and give the newly created account read/write permissions to the folder.

The target server does not necessarily need to run with that service account, it's optional.

As an alternative, you can also do the following. It requires a cleartext password in a job, however, so it's not recommended. In your backup script, do this:

exec sp_configure 'xp_cmdshell', 1 reconfigure

exec 'xp_cmdshell ''net use \targetserver\path /user:shareusername /password:shareuserpassword'''

exec sp_configure 'xp_cmdshell', 0 reconfigure

Substitute in a user/password that you created on the target server. It should be an account that only has access to that share, and nothing else, not an administrator or anything, to minimize the security impact of a cleartext password.

Theoretically you could do this as a system startup SP and it should work. I'd probably stick it into the backup job.

This also assumes that you're leaving xp_cmdshell turned off, which is the default, correct, and high security option. If you're leaving it turned on all the time, you can remove the sp_configure and reconfigure statements.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.