ODBC Password Security in SAS

Question

We want to remove hardcoded passwords from ODBC connection strings in our SAS code, and also prevent any of the passwords from appearing in the SAS log files.

There seems to be plenty of whitepapers discussing how to go about this but I either find problems with them, or can't get them working.

Prompting the user each time for the PW is not a viable alternative. Also, storing the password in a macro variable is an acceptable approach, as long as you have a way to suppress it from printing to the log with MACROGEN and SYMBOLGEN options turned on.

ATTEMPT 1 - ENCODING (link to whitepaper here)

proc pwencode in='mypassword' method=sasenc;
run;

gives:

{sasenc}ACFD24061BF77D7D5362EE7C2D00D08B

If I replace my plaintext password with the encoded value in my code then the ODBC passthrough statement runs fine.

proc sql noprint;
  connect to odbc as remote (datasrc=cmg_report user=myuser password='{sasenc}68B279564BD2695538CDCDB301E8A357563480B0');
  create table sqlo as 
  select *
  from connection to remote
  (
  select top 1 * from application
  )
  ;
  disconnect from remote;
quit;

And the log correctly masks out the values with XXXXXXXs.

961  proc sql noprint;
962    connect to odbc as remote (datasrc=cmg_report user=&user_cmg password=XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX);
963    create table sqlo as
964    select *
965    from connection to remote
966    (
967    select top 1 * from application
968    )
969    ;
971  quit;
NOTE: Table WORK.SQLO created, with 1 rows and 29 columns.
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.34 seconds
      cpu time            0.01 seconds

The problem with the above approach is that if someone has access to the code, they can login using the encrypted password, without needing to know the plain text password. So while it hides the actual password it doesn't provide security. Seems kind of silly to me or am I missing something? EDIT: This provides some security if your ODBC password happens to be used elsewhere, that's about it though.

ATTEMPT 2 - USING SYMGET (link to whitepaper here)

The problem with this is that I simply can't get the technique described to work in SAS. I'm running SAS 9.2 on XP, trying to connect to an SQL Server DB.

%let my_password = password;

proc sql noprint;
  connect to odbc (dsn=cmg_report uid=myuser pwd=symget('my_password'));
  create table sqlo as 
  select *
  from connection to remote
  (
  select top 1 * from application
  )
  ;
quit;

I get the below message saying that the login failed:

1034      proc sql noprint;
1035        connect to odbc (dsn=cmg_report uid=myuser pwd=XXXXXX('my_password'));

ERROR: CLI error trying to establish connection: [Microsoft][SQL Server Native Client 10.0][SQL
Server]Login failed for user 'myuser'.

It looks like it is trying to use "symget" as the actual password (as it has been masked out in the log). There are some responses to this whitepaper saying to wrap the symget in a %sysfunc call but the symget() function is one of the few functions that SAS does not allow within a %sysfunc call so I don't see how that could be possible.

Any other tips/suggestions/ideas would be much appreciated.

Thanks

EDIT: It would be especially good if there was a technique to do this that worked with options symbolgen macrogen turned on.

Answer 1

Rob, we ran into a similar issue and came up with a different method that allows all of our team members to run the same program without having our id/passwords stored in the programs. It requires that each team member have a text file stored safely (no permissions except for owner) that SAS can access.

Here is an example of contents of an ID/PW file:

machine odbc login XX_odbc_id_XX password XXodbc_pw_XX
machine oracle login XX_oracle_id_XX password XX_oracle_pw_XX

We operate on a UNIX server, so we store our indivual id/pw files locked up in our home directory so no one else can access it, in this case it is named ".netrc". The macros at the end of this thread should be stored somewhere, then the program would look like the following:

%let id_pw_text_file = ~/.netrc;

%ODBC_Acct;

proc sql;
   %ODBC_Connect
   create table sqlo as
      select * from connection to odbc
      (
          /*  [ Insert ODBC query here ]  */ 
      );
   %ODBC_Disconnect
   quit;
run;

I tried to revise the macros to work in your environment and to remove a lot of code specific to our systems, but obviously I wasn't able to test it to make sure it works. Let me know if you have an issue and I'll try to help fix it. Hope this helps.

/*********************************************************************
*  Name:  ODBC_Acct                                                  *
*  Desc:  Set global macro vars containing a users ODBC username     *
*         and password. Retrieves this information from a users      *
*         specific ID/PW file.                                       *
*********************************************************************/
%macro ODBC_Acct( mprint );
   %local __mprint __symbolgen __mlogic;
   %if ( %length( &mprint ) = 0 ) %then %let mprint = NO;
   %if ( %upcase( &mprint ) = NO ) %then %do;
      %let __mprint = %sysfunc( getoption( mprint ));
      %let __symbolgen = %sysfunc( getoption( symbolgen ));
      %let __mlogic = %sysfunc( getoption( mlogic ));
      options nomprint nosymbolgen nomlogic;
   %end;
   %global  odbc_user  odbc_pw;
   %Get_ID_PW( &id_pw_text_file , odbc , odbc_user , odbc_pw )
   %if ( %upcase(&__mprint) ne NOMPRINT ) %then %do;
      options &__mprint &__symbolgen &__mlogic;
   %end;
%mend;

/*********************************************************************
*  Name:  ODBC_Connect, ODBC_Disconnect                              *
*  Desc:  Returns SAS/Access connect or disconnect statements        *
*         for accessing ODBC.                                        *
*********************************************************************/
%macro ODBC_Connect( mprint=no );
   %local __mprint __symbolgen;
   %if ( %upcase(&mprint) = NO ) %then %do;
      %let __mprint = %sysfunc( getoption( mprint ));
      %let __symbolgen = %sysfunc( getoption( symbolgen ));
      options nomprint nosymbolgen;
   %end;
   connect to odbc as remote (
      datasrc=cmg_report
          user = "&odbc_user"
      password = "&odbc_pw"
      );
   %if ( %upcase(&__mprint) ne NOMPRINT ) %then %do;
      options &__mprint &__symbolgen;
   %end;
%mend;
%macro ODBC_Disconnect;
   disconnect from odbc;
%mend;

/*******************************************************************************
*  Name:  GetID_PW                                                             *
*  Desc:  Get loginid and password from a secured file                         *
*------------------------------------------------------------------------------*
*  Arguments:                                                                  *
*    1st   Required. Source file containing IDs and passwords.                 *
*    2nd   Required. Host id.                                                  *
*    3rd   Required. Specify the macro variable to put the loginid.            *
*    4th   Required. Specify the macro variable to put the password.           *
*------------------------------------------------------------------------------*
*******************************************************************************/
%macro Get_ID_PW( source , rhost , usrvar , pw_var );
   %let source_file = &source
   %if ( %sysfunc( fileexist( &source_file ) ) ) %then %do;
      %let rc  = %sysfunc( filename( dummy , &source_file ) );
      %let fid = %sysfunc( fopen( &dummy ) );
      %do %while( %sysfunc( fread( &fid ) ) = 0 );
         %let rc = %sysfunc( fget( &fid , inrec , 500 ) );
         %let machine = %scan( &inrec , 2 , %str( ) );
         %if ( %upcase( &machine ) = %upcase( &rhost ) ) %then %do;
            %let &usrvar = %scan( &inrec , 4 , %str( ) );
            %let &pw_var = %scan( &inrec , 6 , %str( ) );
            %goto Break;
         %end;
      %end;
      %Break: %*;
      %let rc = %sysfunc( fclose( &fid ) );
      %let rc = %sysfunc( filename( dummy ) );
   %end;
   %else %do;
       %put ::: ID/PW file "&source_file" not found;
   %end;
%mend;