Issue w Log Shipping between different domains.

Question

Issue w Log Shipping between different domains.

chrisrdba 471

Greetings. I'm using this link to try and get LS set up between servers in different domains.

https://www.mssqltips.com/sqlservertip/2562/sql-server-log-shipping-to-a-different-domain-or-workgroup/

As noted in the instructions, I have:

Created an account in each domain that has the same username and password for the SQL Server Agent Account.
Created a folder/ share in both the source and destination, and granted the SQL Server Agent account permissions.
Specified the correct folder on the source and destination.

However, I keep getting the following error. Note that I've tried using the actual folder instead of the share name, and also tried not using the FQDN. Nothing changes except the verbiage in the error (same issue though).

Any ideas?

Microsoft (R) SQL Server Log Shipping Agent
[Assembly Version = 13.0.0.0, File Version = 13.0.5026.0 ((SQL16_PCU_Main).180318-0835)]
Copyright (c) 2016 Microsoft. All rights reserved.

2020-09-11 15:45:00.54 ----- START OF TRANSACTION LOG COPY -----
2020-09-11 15:45:00.62 Starting transaction log copy. Secondary ID: '6dfd8002-3e61-4daf-b8e9-ad9fb381dd83'
2020-09-11 15:45:00.62 Retrieving copy settings. Secondary ID: '6dfd8002-3e61-4daf-b8e9-ad9fb381dd83'
2020-09-11 15:45:00.63 Retrieved copy settings. Primary Server: 'sourceServer', Primary Database: 'dbaLS', Backup Source Directory: '\sourceServer.mydomain', Backup Destination Directory: '\destServer.otherDomain\dbaLS', Last Copied File: '<None>'
2020-09-11 15:45:00.65 Copying log backup files. Primary Server: 'sourceServer', Primary Database: 'dbaLS', Backup Source Directory: '\sourceServer.mydomain', Backup Destination Directory: '\destServer.otherDomain\dbaLS'
2020-09-11 15:45:01.30 * Error: The user name or password is incorrect.
(mscorlib) *
2020-09-11 15:45:01.30 ----- END OF TRANSACTION LOG COPY -----

Exit Status: 1 (Error)

Edit -- I forgot to mention that this part doesn't make much sense to me.

The first step is to configure permissions on the log backup folder so that the SQL Server Agent account on the secondary instance has access to the logs. The agent on the secondary instance will copy the logs from the folder that was designated as your log backup folder during the Transaction Log Backup Settings configuration.

If the Agent from the Secondary was able to access a folder on the Primary, this whole workaround wouldn't be needed.

Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2020-09-11T21:50:58.947+00:00

The part that does make sense to you, make sense to me. It is SQL Server that accesses the log file to apply it, not SQL Server Agent. So if the service account for SQL Server does not have access the source disk for the logs, it will not be able to read them.

As for the error message, have you tried that sharing works with your own accounts? (Set up in the same way, that is, same name and password)?
chrisrdba 471 Reputation points

2020-09-14T18:17:22.237+00:00

I've tried adding Everyone, it didn't work.

I also tried having sql server use the same account as the agent, that didn't either.
Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2020-09-14T19:42:25.07+00:00

I repeat: "As for the error message, have you tried that sharing works with your own accounts? (Set up in the same way, that is, same name and password)?"

Get this working outside SQL Server first, so you get that part out of the equation.
chrisrdba 471 Reputation points

2020-09-15T14:30:53.32+00:00

By "your own accounts" not sure what you mean?

On box 1, I can access the share on box 2 using the box2Domain/ serviceAccount, and well as my personal account for that domain.

On box 2, I can access the share on box 1 using the box1Domain/ serviceAccount, and well as my personal account for that domain.
Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2020-09-15T21:16:28.603+00:00

If it works for your own account, this means that sharing is possible at all. I take it that you have the same account name and passwords on both domains?

You say "On box 1, I can access the share on box 2 using the box2Domain/ serviceAccount," How do you specify Box2Domain\ServiceAccount? Can you login with that account on the other domain?

Did you try logging in on box 1 as the local service account and try to access the remote share?
chrisrdba 471 Reputation points

2020-09-15T22:39:43.247+00:00

I do that by mapping a drive in windows explorer, and specifying the appropriate domain/service account.

So on box 1 I may a drive to the log shipping share on box 2, and connect w the domain2/service account.

I can't log on to our boxes as a service account.
chrisrdba 471 Reputation points

2020-09-15T22:41:43.107+00:00

I just looked in the source event viewer and noticed the attached errors24964-lserrors.txt
Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2020-09-16T17:54:31.117+00:00

So the Agent job needs to make the same manoeuvre. Which of course is a tad difficult for an automated job.

But I asked this before: do the service accounts have the same name and passwords in the two environments? To my knowing this is the only way you can access remote resources in a workgroup or between domains without trust.

(You never said whether the other domain trusts the source domain, but I take it that it does not, because in that case you would have the problem.
chrisrdba 471 Reputation points

2020-09-16T19:10:45.21+00:00

Yes same name and password. Figured it out -- will post below. Thanks for all your help!

4 answers

Your answer

Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2020-09-11T21:50:58.947+00:00

The part that does make sense to you, make sense to me. It is SQL Server that accesses the log file to apply it, not SQL Server Agent. So if the service account for SQL Server does not have access the source disk for the logs, it will not be able to read them.

As for the error message, have you tried that sharing works with your own accounts? (Set up in the same way, that is, same name and password)?
chrisrdba 471 Reputation points

2020-09-14T18:17:22.237+00:00

I've tried adding Everyone, it didn't work.

I also tried having sql server use the same account as the agent, that didn't either.
Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2020-09-14T19:42:25.07+00:00

I repeat: "As for the error message, have you tried that sharing works with your own accounts? (Set up in the same way, that is, same name and password)?"

Get this working outside SQL Server first, so you get that part out of the equation.
chrisrdba 471 Reputation points

2020-09-15T14:30:53.32+00:00

By "your own accounts" not sure what you mean?

On box 1, I can access the share on box 2 using the box2Domain/ serviceAccount, and well as my personal account for that domain.

On box 2, I can access the share on box 1 using the box1Domain/ serviceAccount, and well as my personal account for that domain.
Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2020-09-15T21:16:28.603+00:00

If it works for your own account, this means that sharing is possible at all. I take it that you have the same account name and passwords on both domains?

You say "On box 1, I can access the share on box 2 using the box2Domain/ serviceAccount," How do you specify Box2Domain\ServiceAccount? Can you login with that account on the other domain?

Did you try logging in on box 1 as the local service account and try to access the remote share?
chrisrdba 471 Reputation points

2020-09-15T22:39:43.247+00:00

I do that by mapping a drive in windows explorer, and specifying the appropriate domain/service account.

So on box 1 I may a drive to the log shipping share on box 2, and connect w the domain2/service account.

I can't log on to our boxes as a service account.
chrisrdba 471 Reputation points

2020-09-15T22:41:43.107+00:00

I just looked in the source event viewer and noticed the attached errors24964-lserrors.txt
Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2020-09-16T17:54:31.117+00:00

So the Agent job needs to make the same manoeuvre. Which of course is a tad difficult for an automated job.

But I asked this before: do the service accounts have the same name and passwords in the two environments? To my knowing this is the only way you can access remote resources in a workgroup or between domains without trust.

(You never said whether the other domain trusts the source domain, but I take it that it does not, because in that case you would have the problem.
chrisrdba 471 Reputation points

2020-09-16T19:10:45.21+00:00

Yes same name and password. Figured it out -- will post below. Thanks for all your help!

Answer 1

m 4,276

Hi @chrisrdba ,

Try to test with grant permission to everyone:

BR,
Mia

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

chrisrdba 471 Reputation points

2020-09-14T18:16:50.557+00:00

I've tried adding Everyone, it didn't work.

I also tried having sql server use the same account as the agent, that didn't either.
m 4,276 Reputation points

2020-09-15T08:52:57.5+00:00

Hi @chrisrdba ,

Yes. You are right. I have tested it on my side. The same issue.
So I doubt whether that can be achieved.

BR,
Mia

Answer 2

Peter Babic 21

Under which account are running your services for SQL agent service?

chrisrdba 471 Reputation points

2020-09-15T14:31:59.377+00:00

An account w the same name and password on each box -- different domains of course.

Answer 3

Guoxiong 8,206

Make sure that you changed the SQL Server Agent account in the "Log on as:" configuration to the service account you created: Box1Domain/ServiceAccount on box 1 and Box2Domain/ServiceAccount on box 2 and then restarted the SQL Server Agent service to make the changes.

chrisrdba 471 Reputation points

2020-09-15T22:18:58.753+00:00

Got that, thanks

Answer 4

chrisrdba 471

I got this working.

I needed to RDP to the destination server w the account that the Agent is running under, and map a drive back to the source, specifying to connect as the account that the Agent there is using (yes, same name and pwd between them).

I know this was suggested above but I didn't have the ability to do this until today. If another response should be marked as the answer that's fine w me.

Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2020-09-16T20:36:18.143+00:00

Great to hear that you got it working!

(And I think you should accept your own post as answer. In difference to the MSDN forums, it is only the person who asked the question who can mark a post as the answer.)
m 4,276 Reputation points

2020-09-18T03:47:07.837+00:00

You can accept your own reply as answer.

Share via

Issue w Log Shipping between different domains.

4 answers

Your answer