none
Cannot Connect to Secondary Server Share When Setting Up Log Shipping RRS feed

  • Question

  • I go through the process of setting it up, but when I run it for the firs time, I get this error:

    TITLE: Microsoft SQL Server Management Studio
    ------------------------------

    SQL Server Management Studio could not backup database 'AM_VEGAS'

    ------------------------------
    ADDITIONAL INFORMATION:

    Backup failed for Server 'PULIZ05\PULIZ'.  (Microsoft.SqlServer.SmoExtended)

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=12.0.4522.0+((SQL14_SP1_QFE-CU).170628-1636)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Backup+Server&LinkId=20476

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

    System.Data.SqlClient.SqlError: Cannot open backup device '\\puliz05bk\lsbackup\AM_VEGAS.bak'. Operating system error 5(Access is denied.). (Microsoft.SqlServer.Smo)

    HOWEVER, I can connect to the share just fine in Windows Explorer on the primary server just fine.

    Wednesday, August 30, 2017 3:40 PM

All replies

  • It sounds like the Sql Server Agent account needs read/write access to the share.  Can you open the access and retry your backup?
    Wednesday, August 30, 2017 4:02 PM
  • Where do I do that?
    Wednesday, August 30, 2017 4:16 PM
  • Connect to the backup share via windows explorer from the database server after determining the account running the sql agent, at the top level, if windows, go to properties of the folder>security tab.  Then, add the account and then give it the permissions and have them propagate to all subfolders.
    Wednesday, August 30, 2017 4:28 PM
  • Thanks for the help.  The SQL agent is using the sa account to run.   How do I add that account to a folder share on the secondary server?
    Wednesday, August 30, 2017 4:41 PM
  • You would need to login to the secondary, launch Windows Explorer, Connect to the share (via \\sharename) and at the top level of the share, apply the permissions there. 
    Wednesday, August 30, 2017 4:51 PM
  • I have it set to everyone, read/write.

    Wednesday, August 30, 2017 4:59 PM
  • Thanks for the help.  The SQL agent is using the sa account to run.   How do I add that account to a folder share on the secondary server?

    Do you mean the job?  The Windows level SQL Server Agent Service cannot be running under a SQL Login, unless I missed a really odd memo.    Go to Services.msc on the machine where the database lives (PULIZ05? PULIZ05bk?) and check the SQL Server Agent service for the account.


    22 years of database experience, most with SQL Server. Please 'Mark as answered' those posts that helped you.


    • Edited by KevinBHill Wednesday, August 30, 2017 5:03 PM
    Wednesday, August 30, 2017 5:02 PM
  • It's set to use this account.

    Wednesday, August 30, 2017 5:15 PM
  • try service account --PULIZ05\PULIZ

    Mssql installation on Centos

    Wednesday, August 30, 2017 5:30 PM
  • You will need a service account that has permissions to read/write on the share \\puliz05bk\lsbackup, which is on a different machine it appears...

    22 years of database experience, most with SQL Server. Please 'Mark as answered' those posts that helped you.

    Wednesday, August 30, 2017 5:36 PM
  • When trying to add the sa account to the shared folder, it states that it cannot find it?
    Wednesday, August 30, 2017 6:44 PM
  • A little more history might help.  The primary server is a 2008 R2 server running SQL2014.  The secondary is running Win10 Home with SQL2014.  Hopefully that helps.
    Wednesday, August 30, 2017 7:59 PM
  • Hi Scottdog129,

    Are both machine domain-joined? If so, run SQL Server agent under a domain account and grant read/write permission to the domain account on the shared folder should resolve the issue.

    If not, consider using Windows pass-through authentication, that should also resolve the issue. In this case, you need to create Windows accounts on both machine, with same username/password. Then you’ll need to run SQL Agent under that account and grant read/write permission to the account on the shared folder. Notice that this is not as secure as domain account(but better than allowing everyone to have read/write access) but it’s good enough for small office.

    If you have any other questions, please let me know.

    Regards,
    Lin

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" 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 MSDNFSF@microsoft.com.

    Thursday, August 31, 2017 7:48 AM
    Moderator
  • Please run SQL with service account, Add that service account in SQL as SQL Admin and add in file share security with full access.
    Thursday, August 31, 2017 9:09 AM
  • When trying to add the sa account to the shared folder, it states that it cannot find it?
    sa is an account created inside a SQL Server instance...it cannot be given permissions outside of SQL Server, including file share access.  This HAS to be done via a Windows account...domain, local or system.

    22 years of database experience, most with SQL Server. Please 'Mark as answered' those posts that helped you.

    Thursday, August 31, 2017 11:43 AM
  • Okay, I've set up a user on the Server2008 and the same user on the Win10 machine.  Still not working.  I set the user as 3833DVR and the server isn't finding the user on the other computer when I try to set security settings on the share.

    Thursday, August 31, 2017 10:34 PM
  • Any help would be greatly appreciated.  Thank you.
    Wednesday, September 6, 2017 10:23 PM
  • I have the same problem, did you find a solution?

    G.Waters

    Tuesday, June 19, 2018 8:19 PM