Five strategies to eliminate passwords from your SAS programs

Many people mistakenly assume that just because you want to use a SAS program to access a protected resource (such as a database table), you must include the credentials for the resource inside your program.

Few things cause a database administrator to lose more sleep than coming across this within a SAS program:

 libname ora10 oracle
    path=ora10g2 schema=PAYROLL
    user=HR PASSWORD="SHHH_SECRET";

It doesn't have to be this way! SAS 9.2 offers so many security-related features that you should never have to code user IDs and passwords in your SAS programs again. This blog post summarizes my five favorite approaches.

1. Use the AUTHDOMAIN= option to access SAS libraries

The AUTHDOMAIN option allows you to delegate the authentication by allowing SAS to "look up" credentials as needed using the SAS metadata environment. For each domain that has a unique set of credentials, your SAS administrator can create an "auth domain" in metadata and associate it with a database server (or other resource). Every SAS/ACCESS database engine supports AUTHDOMAIN=, in LIBNAME statements as well as in PROC SQL CONNECT statements.

With AUTHDOMAIN, the above LIBNAME example becomes:

libname ora10 oracle
  path=ora10g2 schema=PAYROLL
  AUTHDOMAIN="ORACLE"

The beauty of this solution is that SAS can resolve the database credentials differently for each user or group who runs this program, using the credentials that are defined in metadata for those identities.

Bonus: AUTHDOMAIN works for other resources too, such as FTP connections.

2. Use the META engine or INFOMAPS engine to access data sources

The META engine provides a layer of indirection in front of a SAS library, so that not only do you not need credentials to access it, you don't even need to know the details of how the data tables are stored. A LIBNAME statement can be as simple as:

libname MYLIB META Library="My SAS Library";

where "My SAS Library" is an administered library that a SAS administrator defined in SAS Management Console. MYLIB might resolve to a folder with SAS data sets, or it might be a set of tables in a Teradata database. The implementation details are hidden from the program and the programmer.

Sometimes you might see an example that looks like this:

libname MYLIB META Library="My SAS Library"
  metaserv="host.company.com" /* don't want this */
  metaport=8561 /* don't want this */
  user="userid" /* don't want this */
  pw="mySecret";  /* don't want this */

If you run your programs in SAS Enterprise Guide or within a SAS stored process, you don't need (and don't want) the connection/credential information! The SAS session you're connected to already knows who you are, and the extra connection information isn't necessary. (If you make use of the META engine in client applications, you'll want to read up on the nuances described in this tech support paper.)

The INFOMAPS engine provides an administered view of data, further abstracted from the physical structure of the data tables. Check out my previous post for a detailed example of programming with Information Maps.

3. Use SAS Token Authentication

SAS Token Authentication uses your established SAS metadata connection to generate and validate single-use tokens for every other SAS-related resource that you might need access to. To put this another way, once the SAS metadata server knows who you are, it "vouches for you" and facilitates connections to anything else you might need, including SAS workspace servers, OLAP servers, database servers (using AUTHDOMAIN) and more.

A big advantage of SAS Token Authentication is that you don't actually need a host account for all of the resources that you might connect to. This cuts down on the sys admin tasks required to get a group of users up and running. It's a best-practice alternative to using group host accounts on a SAS workspace server; configure the SAS workspace server to use SAS token authentication instead.

4. Use Integrated Windows Authentication

The best way to hide passwords? Don't have them in the first place. That's what many customers do when they implement fingerprint readers or retina scanners in their corporate workstations. The users of these workstations wouldn't know how to supply a password if you asked for one.

Even if your users need a password to log in to your workstation, you can use Integrated Windows Authentication to prevent them from seeing another password challenge as they connect to their SAS environment.

SAS Token Authentication and Integrated Windows Authentication are examples of authentication mechanisms that SAS 9.2 supports. Using these authentication mechanisms can reduce the "authentication friction" that results when your SAS applications must hop among different protected resources that would traditionally require a user ID and password to access.

5. If you must, use PROC PWENCODE to obscure passwords

Sometimes, despite your best efforts, you cannot avoid the odd password in your programs. For example, if you've got to access password-protected SAS data sets, you need to specify the password. But there is no need to have the clear-text password appear in your code. You can use the PWENCODE procedure to encode the password so that prying eyes cannot guess at it. For example:

proc pwencode in="ItzaSecret"
  method=sas002;
run;

yields this in the log:

18         proc pwencode in=XXXXXXXXXXXX
19           method=sas002;
20         run;

{sas002}A16E11553D7986C1414C840C1B9E412A2C2342FC

NOTE: PROCEDURE PWENCODE used (Total process time):
real time           0.01 seconds
cpu time            0.00 seconds

You can then use the encoded password in your program:

proc means
  data=mylib.protected
  (pw="{sas002}A16E11553D7986C1414C840C1B9E412A2C2342FC");
run;

WARNING: Using an encoded password does not prevent someone else from running your program, encoded-password intact, and accessing the same protected resource. So you still need to protect the content of your program. However, at least someone glancing over your shoulder won't be able to guess your password, and most likely won't be able to memorize the encoded gobbledy-gook that appears in the password field.

tags: information maps, integrated windows authentication, meta engine, SAS 9.2, sas administration, SAS tips, sas token authentication, security

7 Comments

  1. Anonymous
    Posted November 26, 2010 at 11:46 am | Permalink

    Further obfuscation would be to create a macro variable containing the password (in a separate script on the same SAS session) so that anyone trying to recycle your script just has &pwd references that won't resolve to anything.

  2. Atul
    Posted March 28, 2011 at 2:51 pm | Permalink

    AUTHDOMAIN= option seems to be working for connections to Teradata from Enterprise Guide 4.2. However the same doesn't work from DI Studio 4.2.
    Internally SAS uses proc metadata to retrieve credentials from Metadata logins repository.
    EG allows any user, with metadata stored, to extract logins details for that user.
    However DI Studio 4.2 reports *Invalid credentials or Access Denied* error messages if an attempt is made to connect to Teradata or Mainframe using the AUTHDOMAIN option in the explicit proc sql.

    I guess it's the use of the procedure *proc metadata* that issues the error messages from within DI 4.2

  3. Chris
    Posted March 28, 2011 at 2:57 pm | Permalink

    Atul, I hope that you follow up with SAS Technical Support if this is holding you back. I think that there must be a way to get this to work, even in Data Integration Studio.

  4. Doc Muhlbaier
    Posted December 7, 2011 at 4:13 pm | Permalink

    The MACRO variable that "anonymous" describes is the only way we recommend. The macro variables are stored with a common name and relative directory location in the user's protected home directory and %included as needed. We like that better than pwencode because the appropriate user shows in the audit trail.

    [We don't have a metadata server and our DBAs don't like Integrated Windows Authentication (we mostly have databases on Unix servers).]

  5. jaap karman
    Posted November 20, 2013 at 3:04 pm | Permalink

    Another more simple approach to user user/password in code.
    Let a SAS macro resolve them with a password-vault file on a well secured OS-layer location
    For example the personal user home location. Say the "my documents" of Windows.
    Anybody heard complaining that location is not safe?

5 Trackbacks

  1. [...] you spot the problem? Other than the use of a user ID and password, which can be avoided, what else is wrong with this program? Why does it fail to [...]

  2. [...] a bit queasy about putting your database credentials into a SAS program like this, check out these five strategies to eliminate passwords from your SAS programs. tags: MySQL, sas/access Bookmark on Delicious Digg this post Recommend on Facebook Share on [...]

  3. [...] or using the AUTHDOMAIN option as outlined in Usage Note 38204 or in Chris Hemedinger’s post "Five strategies to eliminate passwords from your SAS programs". In addition, you could use the METACONNECT= and METAPROFILE= system options as described the [...]

  4. [...] And if you're squeamish about putting your plain-text password in a program, don't forget that you can use PROC PWENCODE to scramble it from prying eyes. options emailhost= ( "smtp.gmail.com" port=465 SSL auth=plain /* [...]

  5. [...] application isn't metadata-aware” and would definitely say that Chris Hemedinger’s article "Five strategies to eliminate passwords from your SAS programs" is a [...]

Post a Comment

Your email is never published nor shared. Required fields are marked *

*
*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <p> <pre lang="" line="" escaped=""> <q cite=""> <strike> <strong>