topic Re: Proc Export - Password in SAS Programming

Proc Export - Password

Toni2 — Fri, 15 Jul 2022 09:10:56 GMT

my SAS runs on my organisation server. When i want to extract data in the organisation location in an Excel file i use the below :

proc export dbms=excelcs data=&&var&i.
       	outfile="&outpath.\&&var&i.._&today..xls";
       	sheet=&&var&i.;
       	version="2002";                     
		server		= "organisationservername";
		port		= 8000;
		ServerUser = "compuk\&lwin";
	    ServerPass = "&pwin";                          
RUN;

the problem with the above code is that i have to provide my ServerPass which is my personal windows password. Since the code used by multiple users if i save it in the program file it will be visible to everyone. I saved the below in the Tools > options > SAS programs > Submit SAS code when server is connected

/* Insert custom code after server connection here */
options symbolgen;

proc pwencode in='my personal password goes here' method=sas002;
run;
%let user_pass=&_pwencode.; 


*--------------------- End of User password setup ----------------;
proc sql noprint;
  select value into: user_id
   from sashelp.vmacro
where name="_CLIENTUSERID";
quit;
%let user_id=%sysfunc(dequote(&user_id.));
%let lwin=&user_id.;
%let pwin=&user_pass.;

but when i run Proc Export i receive the below error :

ERROR: Server is unable to authenticate user credentials. (missing or invalid SERVERUSER='<domain\username>'; 
       SERVERPASS='<password>'; enclosed in quotes).

i want to find a way to save somewhere my password and when i want to extract to Excel file SAS does not need to ask me to provide it

Re: Proc Export - Password

Sajid01 — Sat, 23 Jul 2022 16:21:15 GMT

This is because the password encrypted by SAS is different from the password stored at the server level.
The solution depends upon the environment architecture. Speak to the administrators.

Re: Proc Export - Password

Kurt_Bremser — Sat, 23 Jul 2022 16:46:38 GMT

The issue of sharing code where a private password is needed is solved like this:

Every user has a program file which sets the macro variables containing the credentials; the file is stored in the same location in each user's HOME, and the permissions are set so that only the user can read it.

The shared programs will then contain this

%include '$HOME/sas/password.sas`;

to fetch the credentials.

Re: Proc Export - Password

Tom — Sun, 24 Jul 2022 15:44:26 GMT

@Kurt_Bremser wrote:

The issue of sharing code where a private password is needed is solved like this:

Every user has a program file which sets the macro variables containing the credentials; the file is stored in the same location in each user's HOME, and the permissions are set so that only the user can read it.

The shared programs will then contain this
%include '$HOME/sas/password.sas`;
to fetch the credentials.

Or better still develop your own macro that reads from the text file so that no macro variable is needed. You could use the MODE=3 method of this macro: https://github.com/sasutils/macros/blob/master/fread.sas

Place the file with the password into a folder like .ssh that only you can read.

ServerPass = "%read_password($HOME/.ssh/organisationservername.txt)"

Re: Proc Export - Password

SASKiwi — Sun, 24 Jul 2022 20:12:47 GMT

Is there a particular reason why you are creating really old 2002 format XLS spreadsheets? If you changed to using XLSX spreadsheets, SAS can produce these itself and then you wouldn't need to use the SAS PC Files Server and to supply user names and passwords.