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;

Answer 2

So I also contacted SAS to see what they recommend for this type of issue and this was their (timely as always) response. Unfortunately it looks like their is no way to achieve this without disabling symbolgen:

In order to keep passwords from being hard-coded into SAS programs, or from appearing in SAS logs, the following methods are suggested:

1) The most secure option is to issue a LIBNAME statement using the required SAS/Access engine, and specify DBPROMPT=YES. This will prompt you for connection information to the database as the SAS code runs, so that no connection information will be stored in your program.

However, since this requires some manual interaction when running jobs, it may not be feasible in your situation.

2) Database connection information can be stored in the SAS registry and the password required for connection will be encrypted. In order to set this up, run SAS interactively, and from the Explorer window, highlight "Libraries". From the pmenus, select File-New. Enter the libref you wish to use for your database connection in the "Name" box, then select the database engine being used from the pull-down menu. Once the engine has been selected, you will see a window that allows you to enter username, password, path, and options. Fill in your connection information, then click the small button on the upper right that indicates "Enable at Startup". This method stores your connection information in the SAS registry and will automatically connect when you launch SAS. When running SAS in batch mode, you MUST specify -startlib at invocation in order for the library to be allocated. Your password will appear in your SAS log as an encrypted value.

3) If running PROC SQL Pass-Through, you can pass the password to SAS via the -sysparm option at SAS invocation, and use the &sysparm SAS macro variable where the password would normally be coded. An example follows:

sas -nodms -sysparm mypassword

1? PROC SQL; 2? CONNECT TO ORACLE(user=scott password="&sysparm");

Note that if the macro options MPRINT and/or SYMBOLGEN are in effect, the resolved macro variable will appear in the SAS log, and therefore, your password will appear in plain text in your SAS log. NOMPRINT and NOSYMBOLGEN are the default settings.

4) Also, if running PROC SQL Pass-Through, you can store your CONNECT statement in a file protected by operating system permissions such that only you have read permission, then use the %INCLUDE statement to include the CONNECT statement. An example follows:

sas -nodms

1? OPTIONS NOSOURCE2; 2? PROC SQL; 3? %INCLUDE 'myconnect.dat';

In the above example, OPTIONS NOSOURCE2 prevents the included code from being displayed in the SAS log. Specifying SOURCE2 lists the contents of the included file in the SAS log. NOSOURCE2 is the default setting.

5) With SAS 9.1 and later, you can use Proc PWENCODE procedure that will create encoded password can be used in place of plain-text passwords in SAS programs in batch.

http://support.sas.com/onlinedoc/913/getDoc/proc.hlp/a002595988.htm

and select PWENCODE Procedure.

Proc syntax is listed below. The encoded password is output to log.

proc pwencode in="plaintextPassword"; run;

Answer 3

Sorry, can't write a comment, but the %superq-Trick really works. Use it like this:

proc sql;
   select * from set_encrypedon (pw="%SUPERQ(_password)");
quit;

where you have a macro variable called _password.

Unfortunately, this can't be wrapped into a macro like

%macro pwd();
  "%SUPERQ(_password)"
%mend;

because then MPRINT will log your password again.

Answer 4

There is no secure method to define a ODBC connection!

Everybody can read auth domains, usernames and encode passwords by using the Metadata API. The linux admin or root user can access to any file system, including the home directories and it's password.sas files.

SAS also provide ChangePassPhrase to downgrade stored passwords to older SAS PWENCODE method. If you know the encoded password, than you can decode it for example online at https://decrypt-password.appspot.com/sas-pwdecode/

Sometime login credentials used for different services. A username and password for FTP or MAIL could also use for ssh. Files and metadata are not a secure store.

Answer 5

when SYMGET does not work in the CONNECT statement, try the %SUPERQ quoting function. It also resolves the macro variable without surfacing it in the LOG.