File DSN ODBC connection not working in SAS Enterprise Guide

[andrewbrock]

We have an ODBC connection on the SAS server that uses a File DSN (for a SQL Server database connection). When we run SAS directly on the server we can assign the library like so:

libname database odbc noprompt="filedsn=d:\DSNs\ Reporting.dsn";

…and it works fine.

(the file path above refers to a place directly on the server)

When we use SAS EG to run the same command we get this error:

1                                                          The SAS System                           11:04 Monday, September 18, 2023



1          ;*';*";*/;quit;run;
2          OPTIONS PAGENO=MIN;
3          %LET _CLIENTTASKLABEL='Program';
4          %LET _CLIENTPROJECTPATH='';
5          %LET _CLIENTPROJECTNAME='';
6          %LET _SASPROGRAMFILE=;
7          
8          ODS _ALL_ CLOSE;
9          OPTIONS DEV=ACTIVEX;
10         GOPTIONS XPIXELS=0 YPIXELS=0;
11         FILENAME EGSR TEMP;
12         ODS tagsets.sasreport13(ID=EGSR) FILE=EGSR
13             STYLE=HtmlBlue
14             STYLESHEET=(URL=file:///C:/Program%20Files/SASHome/SASEnterpriseGuide/7.1/Styles/HtmlBlue.css)
15             NOGTITLE
16             NOGFOOTNOTE
17             GPATH=&sasworklocation
18             ENCODING=UTF8
19             options(rolap="on")
20         ;
NOTE: Writing TAGSETS.SASREPORT13(EGSR) Body file: EGSR
21         
22         GOPTIONS ACCESSIBLE;
23         
24         libname database odbc noprompt=XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX;
ERROR: CLI error trying to establish connection: [Microsoft][ODBC Driver Manager] General error: Unable to create file buffer
ERROR: Error in the LIBNAME statement.
25         
26         GOPTIONS NOACCESSIBLE;
27         %LET _CLIENTTASKLABEL=;
28         %LET _CLIENTPROJECTPATH=;
29         %LET _CLIENTPROJECTNAME=;
30         %LET _SASPROGRAMFILE=;
31         
32         ;*';*";*/;quit;run;
33         ODS _ALL_ CLOSE;
34         
35         
36         QUIT; RUN;
37         

I would have thought that it should be telling SAS to find the File DSN on the server? Are File DSN-based ODBC connections supported in SAS EG? Does the File DSN need to be locally on the client that is running SAS EG instead of on the server?

[SASKiwi]

I suspect that the driver requires some file buffers be written to disk, and no folder has been defined as to where these should be stored. When you run SAS in batch,  you have write access to the folder this SAS session is being run from. However when you run in EG, you don't have write access to the server folder this SAS session is being run from. This folder is likely to be similar to this: <SAS Home Folder>\SAS\Config\Lev1\SASApp\WorkspaceServer.   

[andrewbrock]

Thanks - so are you perhaps suggesting that the users in question need write access to this directory from a Windows file permission perspective? Or that it's just impossible?

[andrewbrock · Accepted solution]

The solution was to place the DSN on a SMB share and then change the path to the file DSN to be on that share. It would appear that it needs to be accessible to the client machine that's running EG, not the server.

[SASKiwi]

It depends where your EG SAS sessions run. On the remote server or locally on your PC.