title: ODBC Password Security in SAS
body: <p>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.</p>
  
  <p>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.</p>
  
  <p>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.</p>
  
  <p><strong>ATTEMPT 1 - ENCODING</strong> (<a href="http://www.pnwsug.org/content/keep-your-database-passwords-out-clear-quick-and-easy-tips-protect-yourself" rel="nofollow">link to whitepaper here</a>)</p>
  
  <pre><code>proc pwencode in='mypassword' method=sasenc;
  run;
  </code></pre>
  
  <p>gives:</p>
  
  <pre><code>{sasenc}ACFD24061BF77D7D5362EE7C2D00D08B
  </code></pre>
  
  <p>If I replace my plaintext password with the encoded value in my code then the ODBC passthrough statement runs fine.  </p>
  
  <pre><code>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;
  </code></pre>
  
  <p>And the log correctly masks out the values with XXXXXXXs.  </p>
  
  <pre><code>961  proc sql noprint;
  962    connect to odbc as remote (datasrc=cmg_report user=&amp;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
  </code></pre>
  
  <p>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? <strong>EDIT:</strong> This provides some security if your ODBC password happens to be used elsewhere, that's about it though.</p>
  
  <p><strong>ATTEMPT 2 - USING SYMGET</strong> (<a href="http://support.sas.com/resources/papers/proceedings09/013-2009.pdf" rel="nofollow">link to whitepaper here</a>)</p>
  
  <p>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.</p>
  
  <pre><code>%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;
  </code></pre>
  
  <p>I get the below message saying that the login failed:</p>
  
  <pre><code>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'.
  </code></pre>
  
  <p>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.</p>
  
  <p>Any other tips/suggestions/ideas would be much appreciated.</p>
  
  <p>Thanks</p>
  
  <p><strong>EDIT:</strong>  It would be especially good if there was a technique to do this that worked with <code>options symbolgen macrogen</code> turned on.</p>