Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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

share|improve this question
add comment

3 Answers

up vote 3 down vote accepted

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?

share|improve this answer
    
Worked perfectly. Thank you. –  The Matt May 28 '09 at 6:01
    
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
show 1 more comment

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.

share|improve this answer
    
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
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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