Avoid hard-coding database passwords in your SAS code

It's pretty common to connect to other databases within SAS code.  In my current job, I connect to Oracle, Sybase, Teradata, and MS SQL databases on a regular basis.  The credentials (userid + password) are different on each system and I have to pass them as part of a LIBNAME statement or a PROC SQL connect statement (before pass-through querying) to use those databases. 

There are a couple of things you can do to secure your credentials so they're not hard-coded in your SAS files:

1. At the very least, encrypt your password with PROC PWENCODE.  Others can still use your credentials in SAS code if they happen to come across them, but they won't actually be able to see what your password actually is.
2. Store your credentials as macro variables in a separate file that lives in a personal directory that nobody else can access and %INCLUDE that file in your main program.  Now nobody can see your password but you.

To encrypt a password ("mysecret") using PROC PWENCODE, run this simple statement:

PROC PWENCODE IN="mysecret";  run;

This outputs the encrypted version of the password to the log as follows.

 {sas002}75F37A290F99C066181D56E908E72A6239A5E508

Copy that to the clipboard.  Next create a simple text file with a .sas extension on a personal drive, let's call it the X drive.  In this .sas file, there will be two macro variable declarations, one for the database userid and one for the database password.  For example, the file X:\sas\credentials\oracle_database_credentials.sas file contains the following two lines:

%let oracle_uid=bubba;
%let oracle_pwd={sas002}75F37A290F99C066181D56E908E72A6239A5E508;

I only have to do the PWENDCODE and oracle_database_credentials.sas steps once, and then again each time the password changes.

Each program that I subsequently write to access the Oracle database will have code that looks like the following:

%include 'X:\sas\credentials\oracle_database_credentials.sas';

proc sql;
    connect to oracle (user=&oracle_uid password="&oracle_pwd" path='oracleserver');
    create table sample as
        select * from connection to oracle (
            select * from stuff.sampledata
        );
    disconnect from oracle;
quit;

With the SYMBOLGEN option turned on, the value of oracle_pwd will be echoed to the SAS log, so it's a good idea to do the PWENCODE step in addition to the X-drive %include step.  You don't want to go through the trouble of X-drive protecting your password only to have it appear in .log files for all to see.