March 18, 2024 at 5:31 pm
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.
Regards,
Tony
March 19, 2024 at 6:10 pm
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
April 11, 2024 at 6:57 pm
It's the SQL Server account rather than the agent account that needs access to the backup folder (At least for the RESTORE FILELIST command that has the error). It looks like you are using an admin share i$ - I think you need to be local admin to access these types of shares. You might want to create a normal share.
When accessing files over a share there are both share and NTFS permissions to take into account. Also, check the permissions on the file to make sure they are inheriting the permissions. You might need to replace child object permissions if this is not the case.
Note: Using domain accounts can make it easier to provision access (managed service accounts are a good option).
Hope this helps.
PS
I created a log shipping service tool that might be worth a look.
DBA Dash - Free, open source monitoring for SQL Server
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply