Saturday, March 10, 2012

SAS and LDAP

I was just tasked to read in LDAP records so we could cross-reference userids with login identifiers and general ledger information.

Using SAS to read LDAP was a bit of a challenge. I had used C# to read LDAP before and had been successful at several engagements. However, I had never journeyed into SAS land on the LDAP front. The information on how to do it is sparse. More than that, LDAP is very, very sensitive to wrong information and the response when rnning the code is simply 'LDAP Failed'.

LDAP provides a tremendous datasource for SAS developers.

Let me show you how to work with it:

1. Download and install LDAP browser by Softerra. It is crucial to help navigate the LDAP waters.
2. Pick a simple LDAP server to start with. I will use Colorado State University. Lots of colleges have public LDAPs so they are perfect. For our example, we are going to find details on the faculty of the history dept (my undergrad was history).

Here are the specifics:

LDAP server:directory.colostate.edu
Port:389
DNBase:ou=History (1776), ou=Faculty/Staff, dc=colostate, dc=edu

The SAS code is broken into 6 main pieces:

1. Connect to the server
2. Find the person we need
3. Parse the information
4. End the connection
5. Close the connection
6. Convert the XML into a dataset

Code:
filename outxml 'c:\temp\LDAP.xml';
libname outxml xml 'c:\temp\LDAP.xml';


data _null_;
  file outxml; 
  length entryname $200 Attribute $100 Value $100 filter $100;

  rc =0; handle=0;
  server="directory.colostate.edu";
  port=389;

  /* Make sure these are in order */

  base=" ou=History (1776), ou=Faculty/Staff, dc=colostate, dc=edu";  
  bindDN="";  Pw="";

  /* open connection to LDAP server */
  call ldaps_open(handle, server, port, base, bindDn, Pw, rc);
  if rc ne 0 then do;
     msg = sysmsg();
     putlog msg;
  end;
  else
     putlog "LDAPS_OPEN call successful.";


  shandle=0;
  num=0;
  filter="(objectClass=*)";
  attrs=" ";

  /* search the LDAP directory */
  call ldaps_search(handle,shandle,filter, attrs, num, rc);
  if rc ne 0 then do;
     msg = sysmsg();
     putlog msg;
  end;
  else 
     putlog "LDAPS_SEARCH call successful. Num entries: " num;

  * Start the XML;
  put     '<?xml version="1.0" encoding="windows-1252" ?>'
      /@3 '<TABLE>'
   ;


  do eIndex = 1 to num;
    numAttrs=0;
    entryname='';

    /* retrieve each entry name and number of attributes */
    call ldaps_entry(shandle, eIndex, entryname, numAttrs, rc);
    if rc ne 0 then do;
       msg = sysmsg();
       putlog msg;
    end;

    /* for each attribute, retrieve name and values */

    put @6 '<LDAP>' ;

    do aIndex = 1 to numAttrs;
      Attribute='';
      numValues=0;
      call ldaps_attrName(shandle, eIndex, aIndex, Attribute, numValues, rc);
      if rc ne 0 then 
         do;
            msg = sysmsg();
            putlog msg;
         end;

      do vIndex = 1 to numValues;
        call ldaps_attrValue(shandle, eIndex, aIndex, vIndex, value, rc);
        if rc ne 0 then
           do;
              msg = sysmsg();
              putlog msg;
           end;
        else 
     do; 
             *if vIndex > 1; *This is done to keep duplicates out of XML;
              put @9 '<' Attribute +(-1) '>' Value +(-1) '</' Attribute +(-1) '>' ;
     end;
        end;
      end;
   put @6 '</LDAP>' ;
    end;
  put @3 '</TABLE>';

  /* free search resources */
  call ldaps_free(shandle,rc);
  if rc ne 0 then 
     do;
        msg = sysmsg();
        putlog msg;
     end;
  else
     putlog "LDAPS_FREE call successful.";

  /* close connection to LDAP server */
  call ldaps_close(handle,rc);
  if rc ne 0 then 
     do;
         msg = sysmsg();
         putlog msg;
     end; 
  else
     putlog "LDAPS_CLOSE call successful.";
run;

data test;
   set outxml.LDAP;
run;

Some notes on the above code:

1. The XML libname engine is used to do a conversion from vertical format (LDAP data is vertical) to a dataset layout. Since SAS handles this automagically with XML, why not use it instead of fancy data step.

2. Anyone can run the above code. The CSU LDAP server is in the public so why not.
3. Use LDAP Broser to determine the DNBase and other pertinent information needed. The tool is free and helps get the names and order correct.

[UPDATE: For authenticated servers, you need to bind the user name. For example:

server="directory.colostate.edu";

port=389;
base=" ou=History (1776), ou=Faculty/Staff, dc=colostate, dc=edu";
bindDN="CN=achurchill,CN=Users,DC=MyDomain,DC=savian,DC=net";
Pw="your password";
]

3 comments:

Kimberley Homer said...

If I need to do a TLS bind with a certificate, can I put that into the SAS code? Or should I just get the data in "vertical" form and then use SAS's XML function to convert it?

Alan Churchill said...

Hmmm, I don't know. Make sure you are using the latest SAS version. They have made a number of changes in this space since I posted the comment.

- Alan

Ramya Kolluru said...
This comment has been removed by a blog administrator.

CTRL+Z does not generate EOF in Windows 10

In Windows 10, when I was trying to generate an EOF for a Java program, the CTRL+Z did not work. After doing some research (and help from f...