Based on my previous posts, we are almost done with the basics of SAS libraries and how the various clients can access them.  Before we leave this topic and go onto third-party database engines, I wanted to spend a few minutes talking about some best practices for making sure that your libraries are accessible to your SAS jobs – even if you aren’t using intelligent clients like SAS Enterprise Guide.

As you will recall from our previous post "Seeing SAS data through metadata, there are lots of good reasons to use SAS metadata to manage libraries instead of having each user specify the library details in their individual program.  Fortunately, smart clients like SAS Enterprise Guide are metadata “aware” and since you authenticate to those clients when you connect to the server (even if you cache your credentials), it “automagically” navigates through the authentication and authorization process for us.

When clients can't find the connection

But what happens when I submit a metadata libname engine (MLE) library in a program that we submit from the UNIX command line?  For example, let’s say I want to submit the following program:

libname mydata2 META library="metaref";
proc datasets nolist nodetails;
   contents data=mydata2._all_;
run;

By now, you should know that the META engine on the LIBNAME statement tells SAS to use the metadata definition for this library instead of the physical path. The problem is that SAS Foundation doesn’t intuitively know about the metadata server, so we see an error in the log telling us just that!

But no worries, we can fix that pretty simply, by using a SAS options statement before we allocate our SAS library. Here is an example:

options metaserver="mymetadataserver" metaport=8561 metauser="myuserid" metapass="mypasswd" metarepository="Foundation";
libname mydata2 META library="metaref";
proc datasets nolist nodetails;
   contents data=mydata2._all_;
run;

This tells SAS that whenever I reference something that needs a metadata server, it knows how to get there (and authenticate correctly.) The following log shows the program ran without incident.

So what?  Where is the “best practice” in that?!?

Ok, you could have figured that out on your own (or reading the friendly SAS 9.3 Language Interfaces to Metadata manual!)  What most users struggle with is how to connect to the metadata server when they aren’t exactly excited about adding this bit of extra code into their programs and, more importantly, how do they deal with the whole password thing since they don’t want passwords floating around in free text?

One option would be to use the same credentials for everyone when you connect to the metadata server.  I don’t recommend this option but if you really want to do this, take a look at the documentation, specifically the section entitled “Specifying Connection Properties Directly”.

For those users who want to write once and use everywhere, we can put the options statement in an autoexec file that just gets run when we invoke SAS.  As you may recall, when you run a SAS program from the command line, SAS looks to see if there is a file called “autoexec.sas” in the current directory and if so, runs that. Otherwise, it looks to see if there is a file called “autoexec.sas” file in your SASROOT directory and if it exists, it will run that (but not both!)

If you want to put the options statement in a more general autoexec (e.g., !SASROOT/autoexec.sas) that gets executed each time SAS fires up, then we will need to make this a bit more dynamic since we are passing in the metauser and metapass parameters to the options statement and you don’t want to use the same account every time someone wants to connect to the SAS Metadata.

Encoding a password for an autoexec file

One practice I have found useful is to create a simple process that encodes the password using PROC PWENCODE and then just including that in your normal autoexec file every time you run a program that requires your credentials.

The process is quite simple if you follow the steps below:

1. Encode your password and save the resulting password in a file.
2. Create a file that reads the password and sets the options statement.
3. Include that file in your program.

Let me show you.

Step 1.

Instead of storing your password in a text file that everyone could read, use the following program to encrypt your password and then set the permissions on that new file so that only you can read it.

filename pwfile '~/pwencode';
proc pwencode in='mypassword' out=pwfile;
run;

This program will create a file called pwencode in your home directory on UNIX. Be sure to set the permissions on this file so that only you can read and write to this file. Note that whenever your password changes, you will need to rerun this program with the corrected password.

Step 2.

Now that we have the password stored in an encrypted and accessible file, we will now show you how to read the file and pass the information to the SAS options statement to be able to connect to the SAS Metadata server.

/* Change these values based on your organization */
%let myuserid=gnelson;
%let myserver=mymetadataservername;
/* Read the password file */
filename pwfile '~/pwencode';
data _NULL_;
   infile pwfile obs=1 length=l;
   input @;
   input @1 line $varying1024. l;
   call symput('dbpass',substr(line,1,l));
run;
/* Specify connection options. Change as needed for your installation */
options metaserver="&MYSERVER" metaport=8561 metauser="&MYUSERID" metapass="&DBPASS" metarepository="Foundation"

This program does the heavy lifting each time you want to grab the password and pass it to the SAS Options statement.

Step 3.

Now we are ready to use the options statement wherever we need the connection details for SAS Metadata. For example, using our program above, we change it to now include the program in Step 2.

%include '~/dbpass.sas';
libname mydata2 META library="metaref";
proc datasets nolist nodetails;
   contents data=mydata2._all_;
run;

Retrieving connection details: alternatives

For those that have been using SAS for any length of time may quickly realize that there are many other options for handling passwords including storing the passwords in databases, retrieving them from a corporate identity management service such as LDAP or Active Directory 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 connection options for metadata documentation.

So, now it’s your turn. How do you help your users manage metadata credentials for SAS Clients that are not metadata aware?

Until next time, Happy Data!

--greg