Permissions for shipping SQL Server 2005 transaction logs

Question

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:

Answer 1

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?

Answer 2

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.

Answer 3

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.