Cross Domain Log Shipping issue

Question

Cross Domain Log Shipping issue

gloeffler

SSC-Addicted

Points: 484
More actions
July 10, 2019 at 1:13 pm

#3661570
Hello all,
I know there are many blog posts similar to this, and I have read them all and as far as I can tell things are setup correctly, but I still get invalid username or password when kicking off the copy job. My setup and procedure is below:
- Server A hosts source database in Domain A
- Server B and C are in a cluster in Domain B. I am log shipping to both of these servers to prep for eventual migration and adding into an Availability Group.
- SQL Server agent accounts are created in AD on both domains as this:
  - Server A: A\sqlagent
  - Server B and C: B\sqlagent
  - all 3 servers have the same exact username and password with the exception of the domains being different before each one
- Log backups are placed locally on Server A, shared out with full permissions given to the A\sqlagent account.
As far as I am aware, that is all that is needed from a SQL perspective to get cross-domain LS working, however whenever the copy job runs, it fails for "invalid username and password". We have a VPN tunnel between the 2 domains, however no Trust relationship exists. I am able to path out to the backup share on Server A from Server B and C using the admin login to manually pull the backups over if I want, so I know the tunnel is properly working.
There is mention of using a local network account when doing cross domain log shipping instead of a domain login, but I am not sure how to create and use that. Is that created as a different type of account in Active Directory? Is this something that might be caused by network passthrough security? unfortunately I am not finding any helpful information on setting up network passthrough security online.
If anyone has any info about what might be causing this issue, it would be most appreciated.
- This topic was modified 5 years, 4 months ago by gloeffler.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Sue_H SSC Guru Points: 90860 More actions · Answer 1

It's the secondaries agent account that need access to the logs, share where the logs are located. If it's shared out to A and A is the host, publisher then it's the primary that has been given permissions.

Sue

gloeffler SSC-Addicted Points: 484 More actions · Answer 2

Sue,

Correct, ServerB and C use B\sqlagent account for SQL Server Agent, but there is no way to add B\sqlagent to the permissions on the share in serverA, since the B domain doesn't exist on that domain. when i tried to do that, it comes up with a name not found error and won't accept it. That is why I was wondering if there was a different type of account that needs to be running the sql server agent account so that the domain part of the username wouldn't interfere. It was my assumption that windows takes care of properly changing the domain name when it does the cross domain authentication. All that SQL would need to do is to have the same username and password, and the domain name part would work out under the covers. permissions issue

Sue_H SSC Guru Points: 90860 More actions · Answer 3

It should work if the accounts are exactly the same. Windows doesn't change the domain names - it just looks at the user name and passwords. The other alternatives would be to try using local accounts for the pass through authentication. Or move the files over to the other servers using FTP and just write your own processes since the log shipping process is pretty basic. I've usually found it pretty easy to use FTP and just write the log shipping jobs - it's just copying files and then restore with no recovery.

Sue

gloeffler SSC-Addicted Points: 484 More actions · Answer 4

So, this is now fixed, although I am not 100% sure what did it. I think it was just creating a local account with the same username and password as the domain account running SQL Server Agent. Below are the changes I made, so one of them fixed it 🙂 have to test more to see which one it was...

1.) changed the SQL Server agent account on Server B (not C) to a local account - .\sqlagent kept the same password. (don't think this is what did it since Server C was still able to pull the log backups from the share, even with the domain account running the sql agent on server C.

2.) Added a local user to Server A - .\sqlagent kept the same password, but did NOT use this account to run SQL Server Agent. That is still running under A\sqlagent. I have a feeling this is what did it.

3.) I could not add the new local account to any of the folder shares involved in log shipping, Windows could not distinguish between the domain and local account. i didn't remove the domain account from shares either.

4.) I did a fresh restore of the test DB, although i don't know how this would have made any impact on this, but it was something i did, so maybe.

Thanks for your help with this. I took your advice and used powershell to move the files as a backup while I worked this out and it worked until i got the normal log shipping to work.

Sue_H SSC Guru Points: 90860 More actions · Answer 5

Thanks for posting back - glad it's all working for you now. I would guess it was adding the local accounts that made the difference. Some things are likely different in the configuration of the two domains, AD which made it a bit more fun (challenging) 🙂

Sue