Sql-server – Using a third location for log shipping

log-shippingpermissionssql server

I have a problem with log shipping in SQL Server 2012.

I have tried to configure log shipping between server A and server B. The log destination is on my PC (shared folder). This scenario is working successfully.

Now the problem is when I try to make the logshipping between two servers. I make a shared folder from SYNOLOGY and INFORTREND. Even when I have changed the permissions, it fails to be executed. The log history of the failure shows:

The last step to run was step 1 (Log shipping backup log job step).
Executed as user: NT Service\SQLSERVERAGENT.
The step failed.
Cannot open backup device '\…\TESTLAGI_20160601082323.trn'.
Operating system error 86 (The specified network password is not correct.)
BACKUP LOG is terminating abnormally.

I don't know what's wrong with it. Is it because the SQL Server log shipping doesn't support a third place to put logs for log shipping?

I have added permission for EVERYONE, and given it full control. It should be working, because when I use a shared folder on my PC, it works great, but when I use storage machine, it fails.

Best Answer

The program which performs log shipping runs under a specific UserID. Assuming you are using SQL Server Agent to perform log shipping on a schedule, log shipping will happen using SQL Server Agent's credentials (but see below for proxy accounts). You can find this by starting SQL Server Configuration, highlighting SQL Server Services and right-clicking on SQL Server Agent. The account listed there must have write permission on NAS device. Importantly, the source Agent and the destination device must share a source of authentication. If they are on different network domains, say, you may have to get your LAN administrators involved to establish the trust between the two devices.

Proxies: it is possible to establish additional credentials which SQL Agent will use to run particular jobs. See here for information. If your site is using proxies to run log shipping then it is the proxy account which will need access to the NAS.

To test, I would suggest you create a new job with a single step that copies a small text file from the DB server to the NAS. Use the same credentials for this as you do for log shipping. Run this and adjust permissions until it works. Then duplicate those permission for the actual log shipping job and re-test.