none
Log shipping to remote server not working

    Question

  • Hi all,

    This is my scenario:

    Primary server (A) --> Secondary server (B)

    - Both servers running SQL 2008 R2 Standard
    - Both servers have a network shared folder but:
              "A" can access \\directory on "B" but "B" can't access \\directory on "A" (Ports are closed for security reasons)
    - Enabling LogShipping on Primary has no problem

    The problem appears when enabling in "A" the LS to deliver "B":

    Cannot open backup device '\\"A"\ls\DataBase.bak'. Operating system error 53(The network path was not found.) RESTORE FILELIST is terminating abnormally. (Microsoft SQL Server, Error: 3201)

    So, how come the OS can't find a network path of its own?, if I type on windows explorer \\"A"\ls\ I can perfectly see the .bak file.

    I have been thinking that SQL in "A" sends a request to "B" to open the .bak file but from "B", but I just don't know

    Any ideas?


    G.Waters




    Monday, October 09, 2017 7:43 PM

All replies

  • Try including physical file system name instead of share:

    \\server_A\E:\LogShipping\Database.bak

    Make sure Server B SQL Agent Service account has been granted necessary permissions on file system of Server A.

    HTH,


    Phil Streiff, MCDBA, MCITP, MCSA

    • Edited by philfactor Monday, October 09, 2017 8:10 PM
    Monday, October 09, 2017 7:59 PM
  • It should be this:

    \\server_A\E$\LogShipping\Database.bak

    But if you are using the log shipping wizard it should have prompted you to create a share.

    Monday, October 09, 2017 8:09 PM
    Moderator
  • Thanks Hilary, but it didn't work.

    If I search \\B\ls\file.bak  from A is ok, I can see the .bak file

    I just tried to do a simple backup of the database using \\B\ls\bakcup.bak and the same error shows up.

    I created a windows users for each server with the same password, the I assigned this account to log on the SQL Agent, now the error has changed:

    Backup failed for Server 'A'.  (Microsoft.SqlServer.SmoExtended)
    System.Data.SqlClient.SqlError: Cannot open backup device '\\B\ls'. Operating system error 5(Access is denied.). (Microsoft.SqlServer.Smo)


    So I think I am a little closer than before, but what am I missing?


    G.Waters

    Monday, October 09, 2017 8:45 PM
  • I changed log on account to the SQL service in both servers, they were with Local system account, now they are with an account I created with same logon, password and groups (administrators and SQL groups) and now it is working!!!

    Now I have the doubt if this is secure and if it is an accepted practice. Can you let me know?


    G.Waters


    Monday, October 09, 2017 9:19 PM
  • Are you executing the backup job interactively? If so, it executes under the security context of the account you are logged in with. You need to make sure that your account has been granted FULL control (or at least read/write/execute/change) privilege on Server_A\ls file system.

    HTH,


    Phil Streiff, MCDBA, MCITP, MCSA

    Monday, October 09, 2017 9:19 PM
  • Local System account may work, however, it is not preferred from a security standpoint. That account has maximum server level privilege and could introduce security vulnerability.

    Instead, use default virtual SQL servcie startup accounts or a Windows domain account that has required privilege on both Publisher and Subscriber server, for best security.

    HTH,


    Phil Streiff, MCDBA, MCITP, MCSA

    • Edited by philfactor Tuesday, October 10, 2017 9:54 PM
    Monday, October 09, 2017 9:22 PM
  • Hi There,

    When you configure for log shipping there will be agent jobs created on primary and secondary. These jobs run under security context of the Agent service account.

    so the agent service account on primary should have write access to the backup detonation folder on secondary.

    The Agent service account on secondary must have read permissions to restore the backup.

    So, how come the OS can't find a network path of its own?, if I type on windows explorer \\"A"\ls\ I can perfectly see the .bak file.

    your log in account must have permissions to access the folder. If you run the agent service with your login account you will not get this problem. your account must have higher permissions. so you need to get another domain account with enough permissions to configure your log shipping.

    good luck

    Kumar

    Monday, October 09, 2017 9:46 PM
  • Hi George Waters,

     

    The process of log shipping is divided into the following three steps:

     

    1. Primary instance runs the backup job to back up the transaction log on the primary database and then sent it to the backup share folder.
    2. Secondary server copy the backup file from the share folder.
    3. Secondary server runs restoring with these backup files.

     

    In your scenario, it looks like that you set '\\"A"\ls\ as the shared folder, because "B" can't access \\directory on "A" (Ports are closed for security reasons), it will cause this problem.

     

    You can set a share folder on the Server B, \\B\ls\, please make sure that you have read/change permissions on the folder for the account under which SQL Server services are starting.

     

    Best Regards,

    Teige

     


    MSDN Community Support<br/> Please remember to click &quot;Mark as Answer&quot; the responses that resolved your issue, and to click &quot;Unmark as Answer&quot; if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact <a href="mailto:MSDNFSF@microsoft.com">MSDNFSF@microsoft.com</a>.

    Tuesday, October 10, 2017 7:26 AM
  • Thanks for the LS process explanation Teige,

    This is what I did:

    I shared a folder in "B" and the primary server "A" generates the transaction logs directly into that directory (\\B\ls), so there's no need to "B" to communicate with "A" to get the files since they are already in "B".

    I don't know if I am making a mess but database on "B" is now being updated ok, my problem is that the .trn files are not being deleted.

    Any ideas?


    G.Waters

    Tuesday, October 10, 2017 9:09 AM
  • Hi George Waters,

     

    There is a job deleting these .trn files which are out of date, the default value of it is three days, you can set it in the transaction log backup settings. These logs allow you doing point in time recovery to a specific datetime, if you delete it, you will lose the ability to recover to specific points.

     

    Actually, the backup of transaction log is a part of the feature in log shipping, it is a high availability solution which backup the transaction log to a remote storage. I suggest you keeping the default setting and keeping these log backup for a specific time.

     

    Best Regards,

    Teige

     



    MSDN Community Support<br/> Please remember to click &quot;Mark as Answer&quot; the responses that resolved your issue, and to click &quot;Unmark as Answer&quot; if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact <a href="mailto:MSDNFSF@microsoft.com">MSDNFSF@microsoft.com</a>.

    Thursday, October 12, 2017 7:59 AM