topic Re: PROC Export to Excel is giving an error in SAS Enterprise Guide

PROC Export to Excel is giving an error

MaxBeijer — Wed, 17 Jan 2018 08:38:50 GMT

Hi,

If I make a export in SAS EG 6.1 M1 to excel from our bussinesserver I got the error:

Error creating XLSX file -> \\infra\d$\SAS\ONT\BI\SASEGProjects\_EDWH\Controlescripts\BBO\Testje..xlsx . It is either not an
Excel spreadsheet or it is damaged. Error code=8000101D
Requested Output File is Invalid
ERROR: Export unsuccessful. See SAS Log for details.

This is the code i run:

data testje;
vandaag = today();run;

proc export data = testje
outfile = "\\infra\S$\SAS\ONT\BI\SASEGProjects\_EDWH\Controlescripts\BBO\Testje..xlsx"
dbms=xlsx replace;
sheet = Zorgverlener;
run;

If I run the code on our ETL Server it's working fine.

We have the license for the SAS/ACCESS Interface to PC File. We are on SAS 9.4 M1

Kindly help me in resolving the issue.

Thanks

Max

Re: PROC Export to Excel is giving an error

Kurt_Bremser — Wed, 17 Jan 2018 08:53:04 GMT

Are business and ETL server different physical or virtual machines? It might be that the business server has the resource mounted read-only.

Prime avenue for research are the permissions and user rights, IMO.

Re: PROC Export to Excel is giving an error

MaxBeijer — Wed, 17 Jan 2018 08:58:31 GMT

Hi Kurt,

The business and ETL servers are installed on the same physical machines in a grid environment on windows. With my administrator account it is working on both servers. With the normal user account it is only working on the business server.

Re: PROC Export to Excel is giving an error

Kurt_Bremser — Wed, 17 Jan 2018 09:22:48 GMT

That strongly points to a permission issue with your "normal" user.

Re: PROC Export to Excel is giving an error

MaxBeijer — Wed, 17 Jan 2018 10:05:41 GMT

What are the permissions to check because both servers are using the same metadata and the same "normal" user?

Re: PROC Export to Excel is giving an error

Kurt_Bremser — Wed, 17 Jan 2018 10:13:13 GMT

@MaxBeijer wrote:
What are the permissions to check because both servers are using the same metadata and the same "normal" user?

That has to be done on the "physical" side, concerning how shares are mounted and how permissions for users are propagated across systems.

It means you will have to check with your system and network admins.

Windows allows some very fine granularity through Active Directory, which can complicate things quite a lot if used.

Re: PROC Export to Excel is giving an error

JuanS_OCS — Wed, 24 Jan 2018 13:11:13 GMT

@Kurt_Bremser, all,

I think I have clarified the problem a little bit, however not sure how to move forward.

This is a problem happening on EG. The same code executed from a WorkspaceServer.bat session (in any Grid node, any SASApp) runs without problems. And this happens only for UNC paths, apparently, not on local paths.

Just a libname to that location it gets :

ERROR: User does not have appropriate authorization level for library XXXXXX.

Which does not make much sense, because the same UNC path works OK from the server itself, so it cannot be solved with a setspn.

@ChrisHemedinger, you are our guru on EG and SAS/ACCESS to PC Files. Do you have any clue? I am sure I am missing something really fundamental here.

Re: PROC Export to Excel is giving an error

ChrisHemedinger — Wed, 24 Jan 2018 13:24:50 GMT

Hi @JuanS_OCS - is it possible that your server machine is not configured as Trusted for Delegation? That's needed for Windows to pass on your identity to access another network resource.

When run directly from the server, Trusted for Delegation is not needed. But when you access via EG, which connects to your server using your network identity, Windows will not allow your access to go beyond that server to another network share (or network device, like a printer) unless this Active Directory setting is changed.

Re: PROC Export to Excel is giving an error

JuanS_OCS — Wed, 24 Jan 2018 13:37:17 GMT

Hello @ChrisHemedinger,

thanks a lot for chiming in!

I thought about it, but I am not sure on which way that makes sense. I mean, and to summarize:

It was tested either proc export or libname to the UNC (both give same results) on the following scenarios:

- EG connection with Application server 1 (Etl): works
- EG connection with Application server 2 (BS): not working
- local connection with Application server 1 (Etl): works
- local connection with Application server 2 (BS): works

I would think your statement would make 100% sense if scenario 1 and 2 would give same result, but not in this case.

For your information:

- the Grid is Windows based, 3 nodes.

- All SASApps are deployed the same on the 3 machines, equally.

- EG is on Citrix.

A question, on an attempt to follow that thread of possibility: what AD account needs to be trusted for delegation? The 3 server nodes? My account? the Citrix server? I will check that 🙂

Re: PROC Export to Excel is giving an error

Kurt_Bremser — Wed, 24 Jan 2018 13:38:12 GMT

@ChrisHemedinger wrote:

Hi @JuanS_OCS - is it possible that your server machine is not configured as Trusted for Delegation? That's needed for Windows to pass on your identity to access another network resource.

When run directly from the server, Trusted for Delegation is not needed. But when you access via EG, which connects to your server using your network identity, Windows will not allow your access to go beyond that server to another network share (or network device, like a printer) unless this Active Directory setting is changed.

Windows: a tool to make administrator's lives miserable.

SCNR.

Re: PROC Export to Excel is giving an error

JuanS_OCS — Wed, 24 Jan 2018 14:35:14 GMT

We could see that the 3 nodes are not Trusted for Delegation.

I still wonder why one SAS Application Server can do the job with UNC and other cannot. Don't you?

Before asking the Security teams to open such a big security hole, I would like to be sure about why there is a difference and on what.

Something else: the 3 Object Spawners run under the Local System account.

@Kurt_Bremser, totally true and understandable.

Re: PROC Export to Excel is giving an error

ChrisHemedinger — Wed, 24 Jan 2018 15:24:54 GMT

I don't know the answer, but sometimes the Windows event log (for the machine in question) can reveal what's going on. An admin should be able to connect/view the Windows event log for the server.

Since the event log gets crowded quickly, I'd suggest running the test that fails and view the most recent log entries immediately.