As the first step in the decommissioning of the site has been converted to read-only mode.

Here are some tips for How to share your SAS knowledge with your professional network.

Secret Sequel: Keeping Your Password Away from the LOG

From sasCommunity
Jump to: navigation, search


Passwords are things not to be shared. As good SAS® programmers we don't hard-code our passwords in our programs, but keep them in separate files or macro variables. However this is not enough! Unfortunately the casual (or malicious) user can "turn on" macro debugging utilities like MPRINT, MLOGIC, or SYMBOLGEN and literally read our password right from the LOG, in plain view. This paper presents a series of simple methods to keeping your secret password secret when accessing remote databases with PROC SQL.

Online Materials

  1. View the pdf for the Secret Sequel paper as initially presented in 2007.
  2. View the pdf for SAS Global Forum Paper 013-2009 - Secret Sequel: Keeping Your Password Away From the LOG.

See the SAS Global Forum PowerPoint presentation.

Download the secret sequel example code as plain text.

The %secretsequel macro

This macro code is included in the downloadable example. That example includes and example of the creation of the password file as well as the use of the %PASSTHRU macro.

%macro secretsql(dbname, username);
  %local currmprint dd uu pp;
  %* Make sure that MPRINT is off;
  %let currmprint =%sysfunc(getoption(mprint));
  option nomprint; 
  Proc SQL noprint nofeedback;
      SELECT dsn, uid, pwd into :dd, :uu, :pp
      FROM mystuff.passtab(password=ABCdef)
      WHERE dsn eq symget('dbname')
      AND uid eq symget('username')
    connect to odbc(dsn=symget('dd') uid=symget('uu') pwd=symget('pp'));
    create table mytable as select * from connection to odbc(
      . . . . your pass-thru SQL statement goes here . . . .
    disconnect from odbc;
  %* If MPRINT was on, turn it back on;
  option &currmprint;
%mend secretsql;

Paper Caveat

Under some combinations of OS and versions of SAS, the SYMGET function does not execute inside of the CONNECT statement. As a result the macro variables are not resolved correctly. If this happens, you can replace the SYMGET with the %SUPERQ macro quoting function. The CONNECT statement becomes:

connect to odbc(dsn=%superq(dd) uid=%superq(uu) pwd=%superq(pp));

Notice that the macro variables are not quoted inside of the %SUPERQ function.

New news:
Recently a user reported that on their system the value resolved by %SUPERQ will be surfaced if MPRINT is turned on. The paper discusses how to make sure that MPRINT is turned off.

Contact Info

Contact the authors Art and Paul.

See also