1

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.

closed as off-topic by Shanky, RolandoMySQLDBA, Michael Green, Andriy M, Max Vernon Jun 2 '16 at 15:34

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – Shanky, RolandoMySQLDBA, Michael Green, Andriy M, Max Vernon
If this question can be reworded to fit the rules in the help center, please edit the question.

1

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.

  • how if i change the 'log on' sql server agent services, to be user for login my windows server? is it good? – Yoga Jun 2 '16 at 3:54
  • I would suggest you create a User ID specifically for this and explicitly grant it just the rights it needs to perform the tasks that the jobs do. Using your own Windows login for this role will cause problems: you will end up with rights on production systems you should not have; if you ever leave the company your login will be disabled and all the jobs will stop working! – Michael Green Jun 2 '16 at 6:08

Not the answer you're looking for? Browse other questions tagged or ask your own question.