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";
]

Tuesday, March 06, 2012

SAS/IntrNet and IIS 7.5

First of all, I had to set up SAS/IntrNet recently. I do this at times and always struggle with security
issues in IIS. Hence, I document it below.

People may ask why use SAS/IntrNet anymore? Well, it is fast and is probably the fastest way to interact with SAS via the web. It has limitations but it implements a standard REST api that is used by loads of web companies (i.e. Twitter, Facebook, etc.). I absolutely love IntrNet due to its simplicity.

Let's make it happen:
===============================================


I posted a short while back on how to get SAS/IntrNet operational on IIS 7. Well, I had to do everything again under IIS 7.5 and eiteher things have changed slightly or I didn't get it all captured last time. So here we go again, but this time with pictures:

1. Open up IIS in Windows Server 2008 R2 and right-click on sites, Add a new site:


2. Fill in the details:


3. Pay attention to the application pool and any host header information. Host headers are nice for handling lots of different sites under a single domain name.

4. You should now have a basic site. Add in a virtual directory for the scripts directory:


5. Point it to the SAS/IntrNet scripts location (normally c:\inetpub\scripts).


6. Make sure that CGI is enabled on your IIS installation. if not, go to the server roles and enable it. if it is enabled, you should see it in the site information:





7. Go to Windows Explorer, go to the scripts directory, right-click and select properties. Go to the security tab and add in the app pool identity. This is different than previous versions of IIS. If you used DefaultAppPool as shown above, use the following id:

IIS AppPool\DefaultAppPool

[Note: Even  a single space at the end of the above name will cause it to not work.]



8. Go back to IIS, click on the site, select Handler Mappings, Add Managed Handler:



 9. CRITICAL STEP. While inside of Handler Mappings, click on request restrictions, go to the Access tab, and select Execute:



 10. Finally, go to the Authentication tab for your site and open it. Under Anonymous Authentication, select edit and change it to the pool identity:




If all of the above does not work, call or email.

Cisco Information Server (CIS) and MS OLAP

Ok, so my recent issue was integrating CIS with Microsoft OLAP cubes. The normal way to do this is to set up MS OLAP (SSAS) with a website a...