Transaction log shipping not working, insufficient rights?

Thijs1979

Hi all!

I'm trying all day to get transaction log shipping (SQL Server 2005) to work, but without success so far. I hope you can help me! Here's the situation:

I have an SQL Server 2005 in Domain A. I want to ship the transaction logs of a Nav 4.03 database to a SQL Server in Domain B. There is no trust between the domains.

On both servers I created a local (not domain!) account called "SQLAgentAc" with same password which I use on both servers to run the SQL Server Agent service. Therefore I added this account to the group called SQLServer2005SQLAgentUser$SERVERA$MSSQLSERVER (and SQLServer2005SQLAgentUser$SERVERB$MSSQLSERVER)

On both servers there is a shared folder to which "SQLAgentAc" has read+write access to. When I am on the server in domain A, I can access the shared folder on server B by using the "SQLAgentAc" username and password. I can create new files in the folder, so far so good.

Now I turn on Transaction Log Shipping for my database on server A. I tell the server to put the backup file in \MSSQL\TRANSLOGS and copy it to \\SERVERB\TRANSLOGS.

Backing up the file is going OK, the BAK file appears in \MSSQL\TRANSLOGS on server A, but when it wants to copy the file to server B the error appears:

SQL Server Management Studio restore database 'DATABASENAME'
An exception occured while executing a Transact-SQL statement or batch (SqlManagerUI)
Cannot open backup device '\\SERVERA\TRANSLOGS\DATABASENAME.BAK' Operating system error 5(error not found). RESTORE FILELIST is terminating abnormally (Microsoft SQL Server, Error: 3201)

This is strange because the file is there, and I can even see it from Server B. What am I doing wrong??

I tried to create a manual backup job on server B using a new file on A, using the following command:

BACKUP DATABASE [DATABASENAME] TO DISK = N'\\SERVERA\TRANSLOGS\DATABASENAME.bak' WITH FORMAT, INIT, NAME = N'DATABASENAME-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO

This results in the following error:

Executed as user: SERVERB\SQLAgentAc. Cannot open backup device '\\SERVERA\TRANSLOGS\DATABASENAME.bak'. Operating system error 5(error not found). [SQLSTATE 42000] (Error 3201) BACKUP DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed.

I hope someone can help me with this! Thanks in advance!

Thijs1979

I found the solution. Not only the SQL Agent service but the SQL service as well has to run under the SQLAgentAc account. Now SQL Server has access to the shares and can copy the files.