Microsoft_Testing_Team
Log shipping setup failing with error - Access is denied
We are setting up a log shipping from the Dev to the Reporting Server. However, the GUI process is failing with the below error.
Source VM: XXX-SQL-D
Target VM: XXX-RPT-D-DB
Error from Log File:
Cannot open backup device '\\XXX-SQL-D\i$\MSSQL\Backup\LSTONY\LOG\LSTONY.bak'. Operating system error 5(Access is denied.).
RESTORE FILELIST is terminating abnormally. (Microsoft SQL Server, Error: 3201)
I could see that the "NT SERVICE\SQLAgent$DEV" account on XXX-RPT-D-DB is having a privilege issue on folder \\XXX-SQL-D\i$\MSSQL\Backup\LSTONY\LOG\.
Please note that the target uses "NT SERVICE\SQLAgent$DEV" to process this backup, copy and then restore it to the target folder.
A few things we tried:
- Provided full access to this folder to everyone using security, but still no luck.
- Validated the sharing. Able to view the source folder and create/read files on the folder using the user I logged in. So the folder is discoverable remotely.
Since the GUI setup for initial log shipping is all automated, I do not see an option to change the account which runs the backup, copy and restore. Is it possible to change accounts, if so the solution I have is to run all the processes as a proxy account or an AD account which has universal access to all machines to bypass permission issues.