SQL Server 2012 cannot use file server for log shipping

Question

I have a problem with log shipping in SQL Server 2012.
I have two servers for log shipping, let's say server A and server B.

For the first experiment, when I use a shared folder from my computer based on Windows 10, it works great.

But when I try to use a shared folder from my file server such as synologi or infortrend, it failed, even after I added permissions for SQL Server Agent, my administrator on each server, my user on each server, even if I added full control permission for everyone.

Do you have any advice for my problem? Or must SQL Server 2012 log shipping use a file server which is based on a Windows OS?

When I look in the job history, I see:

Error, Can't open backup device

I assume that this backup job from log shipping can't be done because the device (file server) cannot be accessed by SQL Server.

I am very sure that my servers, and my file server, use the same domain and the same segment, that's why I doubt that log shipping from SQL Server 2012 really works with a file server.

If I manual backup using the GUI, it won't show us the directory mapped in My Computer. But if I use a backup script, it would fail because there is no file backup in my file server.

Trying a BACKUP DATABASE command to the share, I get the following error message:

Msg 3201, Level 16, State 1, Line 1
Cannot open backup device '\\[fileserver IP]\coba\TESTLAGICOBA.bak'.
Operating system error 86(The specified network password is not correct.).
Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.

Answer 1

Make sure your log shipping primary instance's SQL Server and SQL Server Agent services are configured using a domain account and has write access on the specified backup share. If these are in place it should work fine.

Note: The SQL Server Agent can be configured to use a local account as well. The primary issue is that whatever account is used must have read/write access on folder where backup is happening.

To check it manually, you can try to take a manual backup on the share specified to see if that works and make sure write access is in place. I am assuming that your SQL Server and SQL Server Agent accounts are configured to use the same domain account.

Run the below commands. Just change the DBName and FileserverPath as you have in your environment:

BACKUP DATABASE [DBName]
TO DISK = N'\\[FileserverPath]\DBName_FULLBackup.bak' 
WITH INIT;
GO
EXECUTE sys.xp_cmdshell 'dir \\[FileserverPath]\';

If this gives an error, create a folder on your fileserver, share it and grant write permissions to the SQL Server Agent account, then try again.

Answer 2

the solution is

1. change logon account in SQLSERVERAGENT services
2. change logon account in SQLSERVER services

both of them to be domain user, and give full permission to this domain user.