We help IT Professionals succeed at work.

Access to the path 'C:\\Program Files\\Microsoft SQL Server\\MSSQL13.MSSQLSERVER\\MSSQL\\Log\\Backup DBs_Subplan_1_20200207170004.txt' is denied.

I am new with SQL Server.  

A while ago I installed SQL Server 2016 in Windows 2012 R2.   I set up the maintenance plans to do daily backups of the database and transaction logs.  

Those maintenance plans worked just fine.

I recently decided I wanted to a text file of the backups to get created.   so I modified each maintenance plan to send a report (.txt) to the "C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Log" folder.

I did this by modifying the maintenance plan and clicking the icon for Reporting and Logging.  Then selecting the checkbox for "Generate a Text file report", select Create a new file, then specified the path above.

Each time the maintenance plan runs, I see an error in the history:

Access to the path 'C:\\Program Files\\Microsoft SQL Server\\MSSQL13.MSSQLSERVER\\MSSQL\\Log\\Backup DBs_Subplan_1_20200207170004.txt' is denied.

I tried changing the path to the same one the backups go to but still got the same error.  

Very perplexed.  

Why would it be able to write all other logs to the "C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Log" folder but not the backup reports?
Comment
Watch Question

Database Analyst
Commented:
You must check and make sure the path C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Log is accessible to your SQL Server Service startup account and is usually read/modify - or FULL CONTROL permission needed under the folder Security tab as usually SQL Service must be able to create folders, put files on it and delete them during cleanup
Julie KurpaSr. Systems Programmer

Author

Commented:
Thanks!
I had indeed looked at the privileges for the folder and compared it to other installations where this issue didn't occur.

What I sew was very odd to me.


On the server that doesn't have the issue, two users have privileges to the folder:
  SQLAgent$myinstance  (read & execute, read, write)
  MSSQL$myinstance  (full control)

What I see on the server with the problem, I didn't see SQLAgent$myinstance or MSSQL$myinstance.  I saw two users with the same privs as those above  but their names are long, funky numeric  S-1-5-80-biglongnumber-biglongnumber.

OK now that I am looking at this closer, I see that the files in this folder are several months old.  Nothing recent. If memory serves me, the date i when I first did this installation.  

There is another folder "C:\Program Files\Microsoft SQL Server\MSSQL13.myInstance\MSSQL\Log"  (instead of "MSSQL13.MSSQLSERVER") that has current files.   It also has the correct users SQLAgent$myinstance & MSSQL$myinstance specified instead of that funky numeric user.

Realization:    I was pointing to the wrong folder.  That "MSSQL13.MSSQLERVER" folder is one that I had apparently abandoned in favor of using an different instance name.  

I just tested it with the maintenance plan and no error.

I feel like such dope.   I blame it on my inexperience with SQL Server.

I wonder if I should just delete that "MSSQL13.MSSQLERVER" folder.....
lcohanDatabase Analyst

Commented:
I would not delete anything in particular if it is not sure why is there - it may be that there is a Default SQL Server installation plus a Named Instance on that same server so you would break the SQL Named instance if you delete the folder. You should be able to see that under Control Panel - Admin tools - Services.
Julie KurpaSr. Systems Programmer

Author

Commented:
Thanks.   Under services, I only see references to the instance "MyInstance".  No reference to any MSSQLSERVER.

In looking at the contents of the folder, all folders are empty except for the "MSSQL13.MSSQLSERVER\DATA" which only has 1 file "MS_AgentSigningCertificate".  

The other folder with the instance name has the same file but with a more recent date.  

I suppose to be on the safe side, I should just leave it be.  :D
lcohanDatabase Analyst

Commented:
yeah...I would just leave them in particular that they don't contain huge amounts of data
alternatively if you go to Start then Programs then Microsoft SQL Server then Configuration Tools then SQL Server Configuration Manager then SQL Server Network Configuration then here you should be able to locate all the instance installed on your machine.
Julie KurpaSr. Systems Programmer

Author

Commented:
Thanks for getting me to look in the right place Icohan.