SQL Server Log Shipping to a Different Domain or Workgroup

By:   |   Updated: 2012-01-05   |   Comments (32)   |   Related: 1 | 2 | 3 | 4 | 5 | > Log Shipping


Problem

Sometimes the requirement arises to provide a copy of your database to an off-site location, such as a disaster recovery datacenter. Log Shipping is one of the most popular, proven technologies used to create a copy of your databases on a remote server. The remote server can be in the same domain, a different domain, or no domain at all (workgroup). How do we configure Log Shipping to work with a secondary server that is in a different domain or workgroup than our primary server?  Check out this tip to learn more.

Solution

Log Shipping is setup and configured between a SQL Server instance running on a primary server and an instance on a secondary server. Typically the secondary server is in a remote datacenter so that in the event of a disaster at the primary datacenter, a copy of the database can be brought online in another location. You can configure multiple secondary servers in multiple locations to receive copies of the database. Other uses for Log Shipping include backing up to a secondary server in the same datacenter as a cheaper alternative for High Availability solutions such as Microsoft Windows Failover Clustering. This solution will cover the configuration of log shipping on SQL Server 2008 to successfully backup to a secondary server in a different domain or workgroup than the primary server.

Prerequisites

You should have an understanding on the basics of log shipping and if possible have an environment to test with. There are many detailed articles out there covering the setup and configuration basics. The tip Step By Step SQL Server Log Shipping or the MSDN article Log Shipping Deployment can be followed to setup your initial log shipping environment.

Service Account Configuration - Part One

The key to successfully implementing log shipping across different domains or from a domain member server to a workgroup server lies in the SQL Server Agent service account. As detailed in the Transaction Log Backup Settings dialogue box when configuring the backup jobs, the SQL Server Agent service account running on the secondary server must have read access to the folder that the log backups are located in. We will explain this more in the "Service Account Configuration - Part Two"  section later in this tip.

SQL Server Log Shipping Transaction Log Backup Settings

Backup Folder Security Permissions

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.

Create a service account in your Active Directory or as a local account on the primary server. Add that account to the folder's security permissions with change and read permissions:

SQL Server Log Shipping Directory Permissions

Also be sure to add the same account to the share permissions as well:

SQL Log Shipping Folder Permissions

You will also need to add the permissions on the log destination folder on the secondary server, as reminded during log shipping setup:

SQL Server Log Shipping Secondary Dataase Settings

Make sure the secondary instance SQL Agent service account has permissions to the local folder that it will copy the logs to:

SQL Server Log Shipping Destination Properties

Service Account Configuration - Part Two

Now that you have granted proper permissions to the log backups source folder, we need to configure the SQL Server Agent account that is running on the secondary instance. You should also configure the primary server with the service account that was added to the folder permissions above for consistency.

The question becomes, how do we configure a service account for an account in a different domain on a server that is in a completely separate domain or a workgroup? The "trick" here is to create an account in your local domain, or on the local computer in the case of a workgroup, that has the Same Name AND the Same Password as the account we granted permissions to above. If you look closely at the security configurations in the previous step you will see a sqlsvc account in SQL_LAB_DOM1 and a sqlsvc account in SQL_LAB_DOM2. Although these accounts are in two totally separate domains, since the usernames and the passwords match identically, they are able to copy files between the servers. The drawback is that you need to ensure that the passwords stay in sync.

Use the SQL Server Configuration Manager on the secondary instance to configure the SQL Server Agent to use the account that you just created:

SQL Server Configuration Manager


Change the SQL Server Agent from using Local System in the "Log on as:" configuration to the service account you are configuring:

Configure SQL Server Agent for Log Shipping


You will need to restart SQL Server Agent after changing its log on account:

Confirm restart of SQL Server Agent


SQL Server Agent running under a named service account:

Veryify SQL Server Agent Service Account

Verification

Check the destination folder on the secondary server, it should still be empty:

Destination Directory for Secondary Server in SQL Server Log Shipping


Note: Wait until there are log backups created in the primary server log backup folder or run the LSBackup job on the primary server using the same procedure that follows for the LSBackup job.

To manually run the log shipping copy and restore jobs Open SQL Server Management Studio on the secondary instance and expand SQL Server Agent to open the Job Activity Monitor:

SQL Server Agent Jobs for Log Shipping


Right click the LSCopy job and select Start Job At Step...

Run the SQL Server Transaction Log Log Shipping Job


The job should run to success - the SQL Server Agent account must have proper access to the source and destination locations:

Verify status of the SQL Server Log Shipping Job


Check the destination folder on again and it should now have Transaction Log Backup files there:

Destination folder for SQL Server Log Shipping


Next Steps
  • Congratulations you have just configured a cross-domain or workgroup log shipping solution.
  • Review the databases you manage to see if log shipping would be an attractive solution for off-site backup.
  • Further explore the capabilities of Log Shipping in your environment. Follow Step By Step SQL Server Log Shipping for detailed instructions on setup and how it functions.
  • Check out the Tip SQL Server 2008 High Availability Options to learn more about the right solution for your scenario.


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Dan Quinones Dan Quinones is an Architect/Systems Engineer with over 11 years of experience specializing in Microsoft Server and Database technologies.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Article Last Updated: 2012-01-05

Comments For This Article




Wednesday, February 10, 2021 - 11:28:38 PM - Konstantin Back To Top (88216)
You also need to add the sqlsvc account on the SQL_LAB_DOM2 server and grant sysadmin rights.

Monday, July 24, 2017 - 11:32:52 AM - Joy Back To Top (63171)
Hi Dan, this is a nice article, clean and clear. However, I am facing issues with LSBackup. following is the scenario source database server, db: mydb, sourcedb1 target database server, db: mydb, targetdb2 (source and target server are same, implementing it on same server) db.service is the windows user I have created on local machine/server. it has been granted full rights on backup folder, the db agent service is configured to run as db.service account. When the log shipping is configured using the SSMS, it creates a backup in designated folder, moves it to copy folder, and restores it to target database. But when it runs through the job, it fails. following is the error that I retrieve from job history. i double checked the permission on the backup folder, i also gave full rights to everyone, full rights on shared path - still it fails. Message 1. The job failed. The Job was invoked by Schedule 65 (LSBackupSchedule_DBSQL01\SQL2016_SIC_PR1). The last step to run was step 1 (Log shipping backup log job step.). [from main job] 2. Executed as user: .\smic.service. The step failed. [from step1] The LSAlert shows a different message like this: Message 1. The job failed. The Job was invoked by Schedule 64 (Log shipping alert job schedule.). The last step to run was step 1 (Log shipping alert job step.). [from the main job] 2. Executed as user: .\smic.service. The log shipping primary database DBSQL01\SQL2016_SIC_PR1.sourcedb1 has backup threshold of 60 minutes and has not performed a backup log operation for 140 minutes. Check agent log and logshipping monitor information. [SQLSTATE 42000] (Error 14420) The log shipping secondary database DBSQL01\SQL2016_SIC_PR1.targetdb2 has restore threshold of 45 minutes and is out of sync. No restore was performed for 140 minutes. Restored latency is 0 minutes. Check agent log and logshipping monitor information. [SQLSTATE 42000] (Error 14421). The step failed. I will appreciate any help from you find the root cause of the issue and fix it. I setup same thing on my local machine with win10+SQL16 Dev edition, and it works fine, but when i set it up on win2012+sql2016 Std edition it fails. thanks in advance Joy

Tuesday, November 8, 2016 - 11:52:18 AM - Peter Back To Top (43725)

 While your screen shot shows it, the instructions do not include the SQL Server Service also needs to be run by the same user. Until I made that change, the initial copy failed with wrong user or password. 

 

Thursday, November 5, 2015 - 2:48:11 AM - AbuElshabab Back To Top (39025)

I have two different forests with no trust between them.

when I try to apply what you said about having identical two accounts on the two forests with indentical passwords (and also make them the running accounts of the SQL Server Agents services), I still get the following error:

Executed as user: Domain2\SSAgent. The step failed.

*** Error: Logon failure: unknown user name or bad password. (mscorlib) ***

Copying log backup files. Primary Server: 'SQLSRV1', Primary Database: 'test4Butmah', Backup Source Directory: '\\SQLSRV1\Log Backup', Backup Destination Directory: '\\SQLSRV2\Log Destination'

Sunday, October 25, 2015 - 3:52:59 AM - Zolf Back To Top (38962)

Thanks Dan.Great article!!

One question, I need to set the same account for the SQL Server instance and the SQL Server Agent.correct??

Monday, August 31, 2015 - 11:08:27 AM - Seth Delconte Back To Top (38573)

It seems that this article skips over the most challenging and important part: how to set up pass-through authentication between untrusted domains.  Can we get a response to Scott's question below?  I'm having the same exact issue:

 

"Hi Dan,

Great article, but I'm also struggling a with the cross domain authentication. I've set up a local SQLSVC account on both the source and destination servers and granted both folder and share permissions to that account.  Same PWs.  The new service account now runs both the SQL Server and SQL Server Agent services (looks like you did the same in your example).  Both services have been restarted on both servers. 

I don't have a problem creating the initial .bak or subsequent .trn files on the source server, but the "Copy" job on the destination server fails.  I get the following log entries:

Copying log backup files. Primary Server: 'SourceServerName', Primary Database: 'LS_Source', Backup Source Directory: '\\SourceServerName\LogShipping', Backup Destination Directory: '\\DestServerName\LogShippingDestination'

*** Error: The network path was not found.
(mscorlib) ***

Are there certain ports that need to be opened perhaps? Or is it possibly a scope issue?  The accounts I created are local to the servers.  Would that make a difference?

Any ideas you have would be helpful.

Thanks,

Scott"

Thursday, January 8, 2015 - 9:15:32 AM - Ioana Loredana Back To Top (35868)

I have another issue: what if between the principal server and second server cannot be established a common network share? How do we transfer the log backups?

Thank you!

 

Thursday, October 9, 2014 - 1:05:56 PM - Joshua McClintock Back To Top (34910)

Here's some additional information about where it fails:

 

TITLE: Microsoft SQL Server Management Studio

------------------------------

 

SQL Server Management Studio restore database 'Product_Database'

 

------------------------------

ADDITIONAL INFORMATION:

 

An exception occurred while executing a Transact-SQL statement or batch. (SqlManagerUI)

 

------------------------------

 

Cannot open backup device '\\xxx-yyy-sql01\LogShippingSrc\Product_Database.bak'. Operating system error 1326(Logon failure: unknown user name or bad password.).

RESTORE FILELIST is terminating abnormally. (Microsoft SQL Server, Error: 3201)

 

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1600&EvtSrc=MSSQLServer&EvtID=3201&LinkId=20476

 

------------------------------

BUTTONS:

 

OK

------------------------------

 

Thursday, October 9, 2014 - 12:58:19 PM - Joshua McClintock Back To Top (34909)

1. When SQL Studio attempts to run the 2nd step which is 'Restoring backup to secondary database' it fails saying it can't connect to the network share on the primary.  What I see in the Event Viewer security logs is the secondary machine trying to authenticate to the primary using it's machine account even though I set the SQL Agent to run as the 'sqlsvc' account.  It seems the Wizard tries to initiate a connection from the secondary not using the SQL Agent?

 

2. I know the 'sqlsvc' account works, because I dumped the 'script' from the log shipping setup and ran the 2nd half portion on the secondary to get it to generate the jobs.  The copy job works and copies the log files, the restore job fails however.

 

Any tips on fixing issue 1?

Monday, August 4, 2014 - 7:06:01 PM - Aaron Back To Top (33998)

HI,

Regarding "Same Name + Same Password configuration" Does this only apply to SQL Agents talking to each other? I have a need to centralize backups (regular backups) to a domain share. I have a couple of servers that are on a different domain than the backup share.

These two servers must have local domain accounts for various reasons and cannot authenticate to the backup share. Will creating Same Name + Same Password on the domain backup share allow me to successfully authenticate and create a backups on the share?

Does that make sense? Thanks in advance, Aaron

Tuesday, July 1, 2014 - 11:54:43 AM - Priyesh Back To Top (32497)

Hi....

I have one issue with scheduling jobs in sql.

I am using SQL 2008 R2 in WIN 2008R2 server.

I want to execute an application using an sql job.

I've created a job, scheduled it but it never executes the application.

sometimes it shows as  -started, succeeded. but it never invokes the application.

sometimes, it starts the job and after hanging, shows still executing.

I am doing that as the system administrator.

I've every right in the folder where I kept the exe.

Please help me by sending a solution.

ll the other job works, like executing an sql query, but not application.

 

Thanks in advance,

Priyesh.P.G

 

Sunday, August 18, 2013 - 4:23:14 AM - venkatesh Back To Top (26366)

hi dan,i have  small doubt regarding backup folder in primary server you have granted change and read permission of the primary server domain1 sqlsvc  account ,but secondary server agent acount of domain2 sqlsvc account you haven't given any permission(read)

of that backup share folder how the copy job will work? how it will read and copy into destination folder.

thnks for your reply in advance

 

 

 

 

 

 

 

Friday, March 1, 2013 - 9:51:37 AM - Vinay Back To Top (22505)

You are GREAT... BIG THANK YOU....!!!! This was perfactly helpful.

Friday, November 2, 2012 - 12:54:45 PM - mahesh Back To Top (20196)

 

Can we cofigure the log shipping in different domains?

Monday, September 17, 2012 - 3:14:29 PM - Scott Back To Top (19538)

Thanks Dan.

2 issues that I don't understand. 1) In the dialog box titled "Secondary Database Settings", what account do I specify
when I hit the 'Connect' button?

And 2) The SQL Server Agent on the secondary instance is configured to use the sqlsvc account. Doesn't the sqlsvc account need
to be added as a user of the database in order for it to restore a backup? (I am currently failing when the Restore step fires.)

Friday, September 14, 2012 - 7:36:03 PM - Dan Quinones Back To Top (19515)

Hi Scott,

You should be able to log on as the svc account on the primary server and open a connection to the share on the target.  Ensure that both NTFS and Share permissions are in place.  One other thing to verify is the sql agent is running under the same account as well, that is a common oversight.

 

The server name is going to be prepended regardless, it does not matter as long as the accounts are named the same and have same password between primary and secondary.  You can also enter .\sqlsvc which is the same for local computer\sqlsvc

Friday, September 14, 2012 - 11:54:55 AM - Scott Back To Top (19513)

I am unable to complete the 'copy' step.  

The job failed. The Job was invoked by User DESKTOPSQLSRV\Administrator. The last step to run was step 1 (Log shipping copy job step.).
Executed as user: DESKTOPSQLSRV\sqlsvc. The step failed."

Are there any methods to manually verify the folder permissions between the 2 servers?  Is there a way to not have the server name prepended on the user name?  I'd like it to execute as user sqlsvc rather than  DESKTOPSQLSRV\sqlsvc.

 

Wednesday, August 29, 2012 - 5:19:11 PM - Dan Quinones Back To Top (19288)

Hi Anil,

 There are numerous high availability options available based upon your requirements and budget.  In addition to Log Shipping you may want to look into SQL Clustering (HA) and Database Mirroring (DR) in SQL 2008 and AlwaysOn in 2012. Here is a good tip to look at -

 SQL Server High Availability Options 

Wednesday, August 29, 2012 - 10:40:53 AM - Anil.T Back To Top (19278)

I want to know the best high availability option for a medium sized companies.

Someone please clarify my doubt.

Thanks,

Anil.T

 

Sunday, August 26, 2012 - 2:42:00 AM - Perry whittle Back To Top (19228)

@scott yes, if you have a firewall between the 2 endpoints you will need Erin netbios ports opened. Double check the share and NTFS permissions you have granted too.

 

@praveen you would be best to move this to the forums. You will also need to restructure your questions to ensure you are provided with fully detailed answers.

 

@manjula you need to do the same as Praveen

Saturday, August 25, 2012 - 11:19:13 AM - Manjula Back To Top (19225)

what happen when changed the Sql Agent Loging? Does it make any affect for runnig other sql services?(ex. replication,maintaince plans)

Wednesday, July 11, 2012 - 1:07:08 AM - praveen Back To Top (18433)

Hi i have some questions in Logshipping could you please clarify,

1.If the Secondary database full wat the action has to take

2.If Failover the logship wat are the pre-requisites and post requasites has to take.

3.How to Root the users and logins to secondary server.

 

Tuesday, June 26, 2012 - 7:00:47 PM - Scott Back To Top (18209)

Hi Dan,

Great article, but I'm also struggling a with the cross domain authentication. I've set up a local SQLSVC account on both the source and destination servers and granted both folder and share permissions to that account.  Same PWs.  The new service account now runs both the SQL Server and SQL Server Agent services (looks like you did the same in your example).  Both services have been restarted on both servers. 

I don't have a problem creating the initial .bak or subsequent .trn files on the source server, but the "Copy" job on the destination server fails.  I get the following log entries:

Copying log backup files. Primary Server: 'SourceServerName', Primary Database: 'LS_Source', Backup Source Directory: '\\SourceServerName\LogShipping', Backup Destination Directory: '\\DestServerName\LogShippingDestination'

*** Error: The network path was not found.
(mscorlib) ***

Are there certain ports that need to be opened perhaps? Or is it possibly a scope issue?  The accounts I created are local to the servers.  Would that make a difference?

Any ideas you have would be helpful.

Thanks,

Scott

Thursday, May 10, 2012 - 9:35:30 AM - sam Back To Top (17399)

Hi,

I want log shipping steps on table lable not whole database lable.If some chenges are done on table of primary serever then it reflects on secondry server .

Regars,

sam

Wednesday, March 28, 2012 - 11:55:41 AM - Dan Quinones Back To Top (16677)

Hi Roz,

The initialize database tab within the Secondary Database Settings wizard allows you to let Log Shipping create the initial backup ("Yes, generate a full backup...") or select an existing backup you have stored somewhere.  You can also choose "No, the secondary database is initialized" and restore your own backup on the target server if you desire.  

If the permissions are setup as described in the article, Log Shipping should be able to copy the initial backup over to SQL.  You could also use the account you created with the proper permissions on the remote server to copy a backup over manually.

Hope that helps clear up how to setup the initial backup on the remote server.

Monday, March 26, 2012 - 5:26:48 PM - Roz Back To Top (16620)

Good article; however there's no mention of how to get the initial backup from Server A to Server B across domains.  I'm trying to setup log shipping across domains and am running into problems getting the initial backup created to start.

Sunday, January 15, 2012 - 8:58:53 PM - Dan Quinones Back To Top (15657)

hi Eric,

no, any additional users would need to have the same ntfs and share pemissions defined to access the log shipping source and destination locations.  additionally the same username and password would be required in both domains/servers, something you would not have access to setup without administrative authority in both locations.  

thanks for your question. 

Saturday, January 14, 2012 - 9:31:02 AM - Eric Back To Top (15652)

Would any un-authorized 3rd, 4th domain user can gain access the same way (same local username pswd)? How is it prevented?

Friday, January 13, 2012 - 5:08:25 AM - Perry Whittle Back To Top (15638)

Hi

Jason is referring to passthrough authentication. Each machine (server, workstation), except domain controllers, retains its own local SAM. You create local users at each endpoint with the same password to gain unchallenged authentication between nodes. My article on this very same topic is being published at the end of Jan on SSC which details this

Regards

Perry

Thursday, January 12, 2012 - 10:25:24 PM - Dan Quinones Back To Top (15635)

Hi Jason,

Not sure what the question is exactly, but with the text you paste I will explain further on the Same Name + Same Password configuration to make Log Shipping work across different domain or workgroup servers.

Since the servers are not in the same domain in this solution we cannot leverage a common account from Active Directory that would be known to both SQL servers participating in the Log Shipping configuration.  To workaround that obstacle and allow Log Shipping to still be used an account needs to be created on both the primary and secondary servers.  When the log shipping job copies logs over it merely maps a share with the user credentials it was configured with.  The prefix domain does not matter, what counts is that the name "LogShipAcct" would be the same on both servers and the password would have to be the same too or else it would not authenticate and would fail.

A shortcoming of this strategy is that the accounts need to be setup and maintained in two separate locations rather than a centralized directory server.  This includes coordinated regular password resets or setting the accounts to not expire, a security no-no in many eyes.

Hope that helps explain further. 

Thursday, January 12, 2012 - 6:14:01 PM - Jason Back To Top (15632)

"trick" here is to create an account in your local domain, or on the local computer in the case of a workgroup, that has the Same Name AND the Same Password as the account we granted permissions to above. If you look closely at the security configurations in the previous step you will see a sqlsvc account in SQL_LAB_DOM1 and a sqlsvc account in SQL_LAB_DOM2. Although these accounts are in two totally separate domains, since the usernames and the passwords match identically, they are able to copy files between the servers

?????

Thursday, January 5, 2012 - 11:45:43 AM - Jay Back To Top (15525)

Great article!  I see now how useful Log Shipping could be and how it can offer a less expensive alternative to high-availability options.  Very well written and easy to understand. I look forward to more articles from this author. 














get free sql tips
agree to terms