BI Tools

Tips and tricks for building information maps, OLAP cubes, reports, and dashboards

BI Admin

Learn your way around a SAS BI installation.

Visual Analytics

Learn your way around the SAS Visual Analytics tool

Coding & Data

Extract, transform, and load your data into the SAS BI toolset

Stored Processes

Create and design stored processes like a rock star

Home » BI Admin

Administration: Retrieving Passwords from the SAS Metadata

Submitted by on 2014-01-13 – 8:11 AM 2 Comments

When working with the SAS metadata, sometimes a developer needs to retrieve a user ID and password from the metadata. For instance a developer may need the encoded password to connect to the database using the SAS pass-through with relational databases like Oracle and TeraData.  [More discussion about the pass-through :  here with TeraData, here compared to Libname, and here in SAS 9.2 doc set.]

As Stig explains – you still need the password and how can you get it?

How do I get an Encoded Password from the SAS Metadata?

If you have a database user and password stored in metadata (as you do if a library is registered there), you sometimes would like to retrieve said username and password.  For example, say you have an Oracle library in metadata. In order to use that library, you have defined an authentication domain (e.g. “Oracle”). On a group or user in metadata, the username and password is registered on that Authentication Domain.

It would look something like the following figure in the metadata user properties:

sas metadata find user id

Here, all the members of the Developers group, have access to the Oracle user “SAS_READ”. If they, for example in DI Studio, want to access an Oracle table, the username and password is retrieved automatically from metadata. DI Studio fetches all the information needed in order to build a connection to the database. This is great, because you do not have to hard code this into the job. That is why you have a metadata server, remember! 😉

Now, if you were a member of the group Developers, you would have access to the usernames and (encrypted) passwords that are defined for that group. [More about encrypted passwords here.] Sometimes, you want to connect manually to the database. If you, for example, would like to write an SQL pass-through, you would normally have to hard code the username and password in the code.  

Fetch the Password with a Macro

I have written a SAS macro that could be used to do fetch the username and password from metadata. You need to provide the Authentication Domain that the username and password is stored under. This can be found by looking at the properties > Data Server for the library in question, in SAS Management Console:

sas metadata find server details

Now that you have the name of the server, you need to take a look at the connection details for this server, under Server manager.

sas metadata authentication

 

In this example, you would need to specify “Oracle_BII” as the argument to the following macro:

%macro Auth(AuthenticationDomain);
%global Password UserID;
data _null_;
   length uri UserId Password UserId AuthId AuthenticationDomain $256;
   rc=metadata_getnobj("omsobj:AuthenticationDomain?@Name='&AuthenticationDomain'",1,uri);
   rc=metadata_getattr(uri,"Id",AuthId);
   rc=1;
   tn=1;
do while(rc>0);
    rc=metadata_getnasn("OMSOBJ:AuthenticationDomain\"!!AuthId,"Logins",tn,uri);
     if rc>0 then do;
        arc=metadata_getattr(uri,"UserID",UserID);
        arc=metadata_getattr(uri,"Password",Password);
        call symput("UserID",compress(UserID));
        call symput("Password",compress(Password));
     end;
     tn=tn+1;
end;
run;
%put NOTE: For AuthenticationDomain &AuthenticationDomain., the UserID &UserID has password &Password.. Executed as &sysuserid..;
%mend;

%Auth(Oracle_BII)

Learning More About Metabrowse

If you are not familiar with the data step functions I used to read metadata, you can (if you have access to SAS Foundation) open up the Metabrowse utility, and look at the structures there.  Type metabrowse in the command box:

sas metadata metabrowse

Here you can take a look at your environment by going to Foundation > AuthenticationDomain. Actually, the same method that I used to read the username and password, can be used to read a lot of other interesting information from metadata. [Angela was talking about the Metabrowse utility here.]

Never miss a BI Notes post!

Click here for free subscription. Once you subscribe you'll be asked to confirm your subscription through your email account. You email address is kept private and you can unsubscribe anytime.
The following two tabs change content below.

Stig Eide

Stig Eide Stig Eide works for KLP insurance as a platform administrator. He has many years of experience with SAS EBI and DI from an administrator's perspective. He holds the highest possible “Certified Platform Administrator for SAS 9″, Level 4. In addition, he considers himself an expert in BASE coding ;).

Latest posts by Stig Eide (see all)

Spread the love

Tags:

2 Comments »

  • Stig Eide says:

    It should be noted that in some cases, you have access to more than one username for an authentication domain. For example, you could be a member of a group that have one databaseuser attached to it, whilst you also have a database user attached to your own metadatauser.
    From 9.2, SAS would solve this by using the login “closest” to you.
    But, my macro does not do this, and takes just the first it can find.

  • And in case you don’t have access to a Foundation SAS environment, Metacoda, http://www.metacoda.com, provide registered users a free utility, Metacoda Metadata Explorer for the SAS Management Console.

    For technical details on the utility and a link to where you can request it, see http://platformadmin.com/blogs/paul/2012/08/metacoda-metadata-explorer-plug-in/