I can access files on the server's C drive... is that really the only way?

@MAW74656 maybe not, but we dont want to know that you can access the files, we want to know if the account running the sql service that creates the file has access. I just gave a method which would create a file in another fashion, there may be others, I just am unaware of them atm.

I misunderstood. I get Access is Denied also. Are we talking about the temp file on the server in %AppData%\local? Which account must I grant access to?

Sorry for the confusion, I meant the folder location where the file will end up. Good news if you got access denied, your issue is reproducible! I would reccomend making subfolder (such as C:\Temp) and granting access there to test.

The template is on a share. That file is copied to the same share with a date appended to the filename. So the question remains: Grant access to whom?

if you want to find out who is running the service and it isnt clear to you, you can always run exec xp_cmdshell 'whoami'

Right, but the %appdata%\local\temp folder isnt where you are writing the file, its C:\test.xls

Ok, exec xp_cmdshell 'nul >> C:\test\test.xls' works without any permissions changes. What does that tell us?

It tells us that your folder within the c drive is writeable, can you change your first command (with ACE) to reference that location?

Doesn't work, get error 7399 The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" reported an error. The provider did not give any information about the error. then also error 7303 from my OP. But thats probably just because the Sheet1$ doesn't exist?

Its possible, but I am unsure, try it with valid data.

That works if I grant my account full control to that folder.

Your account, or the 'whoami' account? Because then it might be just writing to the root of the drive is an issue. Otherwise, issue resolved?

My account, but none of this helps me work with the file on a network share.

So none of the same troubleshooting steps would apply? Testing permissions for the service, verifying it can actually write files via another method (which you said it cant, therefore showing that you need to grant the account with whoami reporting permissions)?

But thats my problem... the whoami account DOES have full permission to the share location. I even ran Procmon on the SQL server and on the file share server, when I use my own windows credential the command doesn't even get to the shared server. When I use sa it does AND works. Both requests get made using the SQL Server service account.

Let us continue this discussion in chat.