topic Re: password text is showing up in the log when MPRINT is enabled in SAS Enterprise Guide

password text is showing up in the log when MPRINT is enabled

jteres — Thu, 13 Feb 2014 18:07:46 GMT

hi-

I'm using EG 5.1 and have a macro like the following:

%macro TD_Date_Pull ( sdate = ,

edate = ) ;

%put NOTE: The macro &SYSMacroName is starting. ;

%let loop_dte = &sdate ;

%let t_today = %eval(%sysfunc(year(%sysfunc(today())))-1900)%sysfunc(month(%sysfunc(today())),z2)%sysfunc(day(%sysfunc(today())),z2) ;

%do %while(%eval(%sysfunc(inputn(&loop_dte, date9)) le %sysfunc(inputn(&edate, date9)))) ;

%let tdate = %eval(%sysfunc(year("&loop_dte"d))-1900)%sysfunc(month("&loop_dte"d),z2)%sysfunc(day("&loop_dte"d),z2) ;

%put NOTE: Loop is starting for TDATE &TDATE ;

proc sql stimer ;

connect to teradata as td ( user = &username

pw = &password

server = "&dbserver"

db = default_db

mode = teradata) ;

[sql code not relevant to question]

disconnect from td ;

quit ;

%let chrt_mth = &loop_dte ;

%let loop_dte = %sysfunc(intnx(month, "&chrt_mth"d, 1), date9) ;

%end ;

%put NOTE: The macro &SYSMacroName has completed successfully. ;

%mend ;

The value for &password comes from an AutoExect process flow that contains the following line

%let password = &My_PASSWORD ;

where the value from &My_PASSWORD comes from a prompt I have set up so that my password isn't hard-coded anywhere. The reason I have set it up this way is that I don't want to get the prompt each time I run code that connects to the database, but it appears that in doing do, I lose the masking on my password and it appears in the log.

Is there a way to prevent this without having to run the prompt along with every piece of code that uses my database access password?

Thanks,

Jed

Re: password text is showing up in the log when MPRINT is enabled

Quentin — Thu, 13 Feb 2014 18:54:22 GMT

That's surprising. You password is displayed in the log from the CONNECT statement, right? Would expect that to be masked. I've certainly stored passwords in macro variables before in similar settings, and they were always masked in the log.

Not directly related to why to password wouldn't be masked, but Chris H had a nice blog post summarizing methods for avoiding hard-coding passwords:

http://blogs.sas.com/content/sasdummy/2010/11/23/five-strategies-to-eliminate-passwords-from-your-sas-programs/

Re: password text is showing up in the log when MPRINT is enabled

Tom — Sat, 15 Feb 2014 03:26:33 GMT

Just turn off MPRINT !

options nomprint ;

connect to ...

Now if you want to make your macro more user friendly then restore the setting afterwords.

%let optsave=%sysfunc(getoption(mprint));

options nomprint ;

connect to ... ;

options &optsave ;

Re: password text is showing up in the log when MPRINT is enabled

jteres — Mon, 17 Feb 2014 15:10:40 GMT

"Doc, it hurts when I do this."

"Then don't do that."

Thanks Tom. I'm still hoping to get an answer to the larger question, but that work around is a good fix in the interim.

Re: password text is showing up in the log when MPRINT is enabled

Tom — Mon, 17 Feb 2014 15:45:26 GMT

One of the SUGI articles I found suggested using pw=symget('password') but that does work for me with SAS 9.4 and access to Teradata. Instead I get this message:

ERROR: Invalid option name (.