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

[jteres]

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

[Quentin]

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-sa...

[Tom]

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 ;

[jteres]

"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.

[Tom]

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 (.