Author: Paul Homes

Handy SAS Usage Note for Troubleshooting UNIX Authentication

Whilst troubleshooting why LDAP based users couldn’t authenticate against a SAS Metadata Server configured for host authentication, I found this handy resource: SAS Usage Note 39891: Using PROC PERMTEST to diagnose UNIX host authentication issues in SAS® 9.2. The server in question already had correctly functioning LDAP host authentication via PAM, it was just that the SAS Metadata Server wasn’t able to authenticate the LDAP users. The usage note helped me fix the problem by leading me to the SASFoundation/SAS9.2/utilities/bin/sasauth.conf file where I found it had methods=pw instead of methods=pam (must have chosen the wrong options at install time). I actually didn’t need to run PROC PERMTEST this time but the usage note contains clear instructions on how to when required. Interestingly PROC PERMTEST doesn’t seem to appear in the Base SAS® 9.2 Procedures Guide.

Spreading the WORK load

I found a handy tip from Rafi in the discussion Allocating SAS WORK Libraries Dynamically in SAS 9.2 in the LinkedIn group SAS Architects/Administrators/Implementation Specialist. It explains how to use a variation on the SAS system option WORK= to spread work libraries over a number of I/O paths using either random or available space methods. This can potentially help you improve I/O performance for work libraries en-masse, and/or get access to more available space, by spreading those work libraries over a number of different devices.

This usage of the WORK= SAS system option is documented in a number of places:

Interesting SAS notes relating to Portal Promotion

I noticed a couple of recent SAS usage notes relating to the new portal content promotion capability in SAS 9.2:

SAS Problem Note 42231 discusses a potential problem whereby incorrect letter casing in the specification of a template name could result in the removal of the entire Portal Application Tree. Of course, if you always follow the practice of doing a SAS metadata backup before any metadata promotions it wont be an issue as you will have a backup to restore from. It sounds like there is a hotfix planned.

The problem note also alerted me to SAS Usage Note 41692 which provides a link to download the portal promotion scripts for use with versions prior to 4.3. A quick look through the documentation shows the scripts can be used to go from a source environment of SAS 9.1.3 (or later) to a target environment of SAS 9.2 (or later) – no 9.1.3 to 9.1.3. I’m not entirely sure, but it also sounds like the downloadable scripts might also be of use to Portal 4.3 users who want to avoid the potential problem mentioned in 42231.

I’d be keen to hear of anyone’s experiences with the portal promotion tools if they want to share.

SAS 9.2 OLAP Cube Identity Driven Member Level Security

Identity-driven member level security for SAS 9.2 OLAP cubes, as the name suggests, uses the identity of the requesting user to restrict access to specific members of a dimension, and in so doing control the subset of cube data that the user has access to. Imagine that you have an OLAP cube containing sales data for all sales people, but an individual sales person is restricted to only seeing a subset of that data. Among all of the other dimensions, perhaps one of the dimensions in the cube has the sales person’s user id contained within it. We can construct an identity-driven MDX expression that can be used to filter the cube data on that dimension to only include members that match an identity attribute such as user id. Of course there are also other ways of using other identity attributes to limit access to cube data but I’ll keep it simple in this example.

To quote from SAS® 9.2 OLAP Server: User’s Guide > What’s New in the SAS 9.2 OLAP Server > Security For Cubes documentation:

Identity-driven security enables you to substitute identity values in a permission condition. It enables you to insert a placeholder into the permission condition that, at query time, gets resolved to a string that represents the user identity.

You can see the various identity value placeholder properties available to you in: SAS® 9.2 OLAP Server: User’s Guide > Modifying and Maintaining Cubes > Cube Security > Identity-Driven Security. Some of the ones that we might use with an individual user include:

  • SAS.Userid – the normalized user id for the person querying the cube (e.g. BILLY@MYDOMAIN)
  • SAS.IdentityName (or SAS.PersonName) – the name of the person querying the cube, as seen in the SAS Management Console User Manager plug-in (e.g. Billy Baxter)
  • SAS.ExternalIdentity – a site specific identifier for people that have been bulk-loaded into metadata from directories such as Active Directory, OpenLDAP etc. This could be an employee number for example (e.g. e12345678)

In this post I’m going to highlight a simple example for a couple of reasons. Firstly, whilst the documentation includes lots of general examples, I couldn’t find many that demonstrate the use of an identity-driven member level security filter. Now I can refer to this post when I need to remember how it’s done. Additionally, during my investigation, I kept getting stumped by an MDX syntax error dialog during the definition of the permission condition. It took me a while to discover that I could just accept the error because at run-time, when the identity substitution happens, the MDX will become syntactically valid. Publishing my experiences might help others save themselves a bit of time and frustration too.

My example cube was built from a SAS table that included a sales person user id column. The value of that column for each row was the user id for the sales person that had access to that data – i.e. that row would contribute to the cube subset that the sales person would be able to see when they query the cube. The format of the user id in that column was quite specific. It was in a normalised format, as explained in the SAS documentation. The user id was converted to uppercase and included a domain suffix on Windows. In Windows environments it would most likely be in the format USERID@DOMAIN (e.g. BILLY@MYDOMAIN), whereas in some UNIX environments it might be in the format USERID (e.g. BILLY). If you don’t know what the format is for your environment you can probably work it out by looking in the SAS Metadata Server logs or turning on some of the debugging options for the SAS OLAP Server. If you don’t get the format correct you are likely to end up with an MDX filter that filters out everything so there is nothing to display!

Information on how to set up member level security on a dimension can be found in the SAS® 9.2 OLAP Server: User’s Guide > Cube Building and Modifying Examples > Setting Member Authorizations On A Dimension. I won’t duplicate that information here, suffice to say that in my example cube I want to apply an identity-driven member level security filter on normalized userid members in the lowest level of a DimSalesPerson dimension using the SAS.UserId property. I want it to apply to all known SAS identities (i.e. SASUSERS group) so I start out by adding an explicit grant of the Read permission to the SASUSERS group on the DimSalesPerson dimension in the cube. This enables the Edit Authorization… button that I click to open the Add Authorization dialog. I select the radio button for Create an advanced MDX expression using the expression builder then click the Build Formula… button. I can then enter the MDX expression to filter the cube data for the current user identity.

When constructing the MDX filter expression you can insert placeholders for Identity Values (such as SAS.Userid) which will be substituted at run-time with the appropriate value corresponding to the user making the request. These Identity Values need to be in a specific format SUB::IdentityValueName (e.g. SUB::SAS.Userid). Whilst I couldn’t find the format documented anywhere, you will see an example of it if you use the Build Formula dialog’s Data Sources list to add an Identity Value into the expression. There is also an example screenshot showing SUB::SAS.IdentityGroups at the bottom of SAS® 9.2 OLAP Server: User’s Guide > Cube Building and Modifying Examples > Setting Identity Driven Security. It doesn’t show it used within a larger MDX expression however.

In my example I want to use the normalized user id to choose the appropriate member of the DimSalesPerson dimension, so I type the following into the Build Formula dialog’s Expression Text field and click the OK button.

{[DimSalesPerson].[All Sales People].[SUB::SAS.Userid]}

Tip: if you want help in working out the syntax for this filter, open the OLAP cube in SAS Enterprise Guide, use point-and-click to filter in the slicer on an example user id in the appropriate dimension. When the table looks like it displays data for a single user, view the MDX:

    SELECT CrossJoin({[DimTime].[All Time].Children }, {[Measures].[SaleAmountSUM] }) ON COLUMNS,
    {[DimGeo].[All States].Children } ON ROWS
    FROM [TransactionsCube]
    WHERE ([DimSalesPerson].[All Sales People].[BILLY@MYDOMAIN])

You should see what you need in the MDX WHERE clause and you can replace the user id with the identity property – SUB::SAS.UserId in this case.

This is where, initially, I got stuck for a while. I saw the following error dialog because it was not valid MDX since SUB::SAS.Userid was not actually a real member.

It turns out this was ok, because although it was not itself a valid member, at run-time when the user queried the cube it was replaced with the real member value (e.g. BILLY@MYDOMAIN) and became valid MDX (assuming of course that BILLY@MYDOMAIN was a member!).

Tip: How can I tell whether the syntax error is due to the placeholder or the rest of the MDX? One method that I use is to start out by using a constant for a valid member (e.g. {[DimSalesPerson].[All Sales People].[BILLY@MYDOMAIN]}) instead of the placeholder. Verify the MDX syntax is ok for the constant value and that the filter works as expected when you open the cube (i.e. you see the subset of data for that constant user). If all is ok then go back and replace the constant with the placeholder (e.g. {[DimSalesPerson].[All Sales People].[SUB::SAS.Userid]})

So even though I had this error I could click the OK button. I then saw the following warning dialog where I could confirm I did indeed want to save the invalid MDX expression as a permission condition by clicking the Yes button.

With the identity driven member level security filter applied, now whenever the cube was queried the user would only see the subset of data they had been granted access to.

As I mentioned earlier this is a relatively simple example. In reality you would probably want to apply the identity-driven filter to a suitable sales-people group and allow some management-level people to see larger or complete subsets of the cube. You might also want to filter based on group memberships and probably make the filter a bit more robust in case the user id doesn’t exist in the cube. Also, if you are running the third maintenance of SAS 9.2 you could also apply the permission conditions in batch using a permissions table.

The method I use to review all the SAS OLAP cube member level security permission conditions that are present in metadata is to use our Metacoda Security Plug-ins ACE Reviewer. You can click on the image below if you want to see it full size:

Finally, the following SAS usage notes can be very helpful for debugging or troubleshooting with the SAS OLAP Server:

BTW: The inspiration for this post came from a question asked on the SAS Discussion Forums > SAS Stored Processes > Thread: Role based security as input parameter, data read from oracle db on demand

Update 02Feb2011: If you’re reading this post then you might also be interested in a related post on Angela Hall’s Blog: Implementing OLAP Member Level Security for All Vantage Points

Update 03Feb2011: In some testing I found the following permission condition useful for filtering on a user id level (that has unique member values across the entire dimension) in a cube at an arbitrary level (i.e. not knowing/specifying how deep the level is within the dimension/hierarchy or what it’s parents are) and then including all unknown ancestors above it.

Ancestors(Head(Filter([DIM_SALES].AllMembers,[DIM_SALES].CurrentMember.Level.Name = 'SALESPERSON_ID' and [DIM_SALES].CurrentMember.Name = 'SUB::SAS.UserId')).Item(0))

I’m likely to forget this so am jotting it down here in case I might need to use it as the basis for future OLAP member level security work. I don’t know about you but I always find MDX work is like mental gymnastics, better than sudoku :) , so I like to keep examples of MDX functions in action. I hope it might give you some ideas too.

Update 04Feb2011: Thanks to a post from Bob in the SAS discussion forum thread linked above I discovered the SAS MDX <!–CONDITION–> ‘operator’ today (looks like an XML comment!). It is used for specifying member level security permission conditions when you have more than one hierarchy in the dimension you are securing. I can’t find it in any of the SAS reference docs but there are a couple of usage notes that mention it:

Using SAS92HFADD & ViewRegistry on Windows Server 2008 R2

In my earlier post on Reviewing Installed SAS 9.2 Software and Hotfixes I mentioned that I ran into a few User Access Control issues on Microsoft Windows Server 2008 R2 when using the SAS ViewRegistry Report utility and the SAS 9.2 Hot Fix Analysis, Download and Deployment Tool (SAS92HFADD).

To get the SAS ViewRegistry Report and SAS92HFADD utilities functioning correctly on Windows Server 2008 R2, the User Access Control (UAC) feature will require you to go through a few extra hoops (unless you have it disabled). UAC is a security feature that, when enabled, means that administrators log in and operate as standard users most of the time, and when they need to do something that requires elevated privileges they will be prompted by Windows (or as I also found out sometimes silently blocked). If you want more information (and a fuller explanation of UAC) then read the Microsoft document User Account Control Step-by-Step Guide.

If you run your server with UAC disabled then you can just follow the SAS instructions as normal and disregard this particular blog post. Of course running with UAC disabled is not normally recommended. I seriously considered turning it off, but decided I would try to ‘do the right thing’ and also use it as a learning experience. After a bout of UAC frustration I spoke to one of my friends who specializes in Microsoft technologies and he told me he leaves UAC enabled and tends to have an administrative PowerShell window open for when he needs to do some admin things.

In this post I’ll explain what I needed to do, in addition to the SAS provided instructions, to get those utilities working on Windows Server 2008 R2 with UAC enabled.

ViewRegistry Report

The SAS ViewRegistry Report utility is used to generate HTML and text reports listing the SAS software and hotfixes currently installed. You can also feed the output of this utility into the SAS92HFADD utility to generate another report detailing the appropriate available hotfixes that have yet to be installed, together with scripts to download and install them.

When I followed the usage note instructions for Windows and just double-clicked on sas.tools.viewregistry.jar I found nothing happened. I then opened up a command window and ran java -jar sas.tools.viewregistry.jar which gave me a bit more information as shown below:

c:\Program Files\SAS\deploymntreg>java -jar sas.tools.viewregistry.jar
java.io.FileNotFoundException: C:\Program Files\SAS\deploymntreg\registry.lck (Access is denied)
Exception in thread "main" java.lang.NullPointerException
at com.sas.tools.viewregistry.Report.collectRegistryData(Report.java:98)
at com.sas.tools.viewregistry.Report.main(Report.java:66)

I was logged on as someone who was an administrator (sas), so I knew it wasn’t a file system permission issue and must be related to UAC. To run sas.tools.viewregistry.jar with elevated permissions I right-mouse-clicked over the Command Prompt item in the Windows Start Menu, and clicked the Run as administrator menu item:

This opened an Administrator: Command Prompt window where I issued the following commands to run the ViewRegistry Report utility as an administrator:

cd "C:\Program Files\SAS\deploymntreg"
java -jar sas.tools.viewregistry.jar

This time it worked, generating the DeploymentRegistry.html and DeploymentRegistry.txt files as expected.

If you expect to run the ViewRegistry Report utility regularly then you might find it easier to create the file C:\Program Files\SAS\deploymntreg\ViewRegistry.bat containing the following:

@echo off
cd /d "%~dp0"
java -jar sas.tools.viewregistry.jar
pause

To run ViewRegistry.bat as an administrator, right-click over it in Windows Explorer and select the Run as administrator item from the pop-up menu:

SAS 9.2 Hot Fix Analysis, Download and Deployment Tool (SAS92HFADD)

The SAS92HFADD utility takes the output from the ViewRegistry Report utility and generates another report detailing the appropriate available hotfixes that have yet to be installed, together with scripts to download and install them.

SAS92HFADD for Windows is downloaded as a WinZip Self-Extractor (SAS92HFADDwn.exe). I ran this and got the following error:

It also needs to be run as an administrator. This can be done by right-clicking over the SAS92HFADDwn.exe file in Windows Explorer and select the Run as administrator item from the pop-up menu:

I now had the C:\Program Files\SAS\SAS92HFADD directory and files as expected. Into that directory I then dropped a copy of the DeploymentRegistry.txt file from a run of ViewRegistry Report utility. I actually missed this step the first time around and got the >> %%% ERROR: Unable to open DeploymentRegistry.txt error in the tool_log.txt file as explained in the SAS documentation.

Next I ran the C:\Program Files\SAS\SAS92HFADD\SAS92HFADD.exe utility. Initially it looked like it was working:

However, after a few seconds, the window closed and there were no additional files (not even a log file) in the C:\Program Files\SAS\SAS92HFADD directory. I immediately guessed this also needed to run as an administrator. I right-clicked over C:\Program Files\SAS\SAS92HFADD\SAS92HFADD.exe in Windows Explorer and select the Run as administrator item from the pop-up menu:

When it completed I had a new directory tree C:\Program Files\SAS\SAS92HFADD\WX6_1295740219 containing the following files as expected:

  • AnalysisReport\SAS_92_Hot_Fix_Report_WX6_1295740219.html
  • Log\tool_log.txt
  • DownloadTools\ftp_script.bat
  • DownloadTools\ftp_script.txt
  • DownloadTools\MD5_checksums.txt
  • DeployTools\WX6_install_script.bat

The AnalysisReport\SAS_92_Hot_Fix_Report_WX6_1295740219.html file is a report listing all of the available hotfixes that can be installed on the server (that haven’t already been installed):

The DownloadTools\ftp_script.bat file is an automatically generated script that can be run to download all the hotfixes identified in the report. I started out running this by double clicking on it in Windows Explorer. By this time I was almost certain that UAC would be a barrier, but as a learning experience I wanted to see how it would fail anyway. The following screenshot has the error message highlighted – at least there was an error message :)

I ran the FTP script again, this time by right-clicking over it in Windows Explorer and selecting the Run as administrator item from the pop-up menu:

The FTP script to download all the hotfixes ran successfully to completion this time. It took a while, as there were many hotfixes to download, but when it finished I had a bunch of hotfix executables in the DeployTools directory. Now it was time to install them.

To install the hotfixes I used the automatically generated DeployTools\WX6_install_script.bat script that silently installs each hotfix in turn. I started out running this by just double clicking on it in Windows Explorer. I guessed UAC would most likely block it, but once again I wanted to see how it would fail. It took a little while to complete, and appeared to be doing something without generating any errors, however when it had finished I could see by re-running the ViewRegistry Report utility that nothing had changed – no additional hotfixes had been installed.

I ran the install script again, this time by right-clicking over it in Windows Explorer and selecting the Run as administrator item from the pop-up menu:

I saw the command window open and all the commands flash by, possibly with errors, but way too fast for me to see – and also way too fast for the hotfixes to actually have been installed :)

The next method I tried, which worked this time, was to open a command prompt window as an administrator, change directory to the DeployTools directory and run the WX6_install_script.bat script:

The install script continued away for a while silently install each hotfix in turn:

Once the install script had completed, I went through the analysis report to ensure I did all of the outstanding manual tasks. I found the manual tasks by reading through the several linked documents in the analysis report that were tagged with a [D]. In this case it included upgrading metadata using the SAS Management Console, copying a plugins directory into a couple of locations, using the SAS Deployment Manager to rebuild a number of EAR files, and then redeploying the updated EAR files.

With all of this done I did a final check by re-running the ViewRegistry Report utility, copying over the new DeploymentRegistry.txt file, and then re-running SF92HFADD.exe. The resulting analysis report now had a nice green banner indicating that I was up to date with all of the available hotfixes.

Final Notes

This process assumes you want to install all of the available hotfixes. If your update strategy is to only install a subset of the hotfixes (such as those with alert notes) you can edit the generated ftp and install scripts to only download and apply those hotfixes you have targeted. The analysis report is a very useful document to help you determine what hotfixes are available for your platform/product mix that haven’t yet been installed. The report can also be used to help you chose which of those hotfixes are appropriate for your update strategy by reviewing the Issue(s) Addressed links.

Regarding UAC, at some point I also tried using a Windows Explorer (run as administrator) but found that the things it launched did not themselves run as administrator too. Looking back on it now, it probably would have been easier to avoid Windows Explorer completely and just use the administrative command prompt window (or PowerShell) – as my Microsoft oriented friend later suggested. I guess I could have also temporarily disabled UAC for the duration of this process and then re-enabled it afterwards. Taking this easy way out, however, would have meant I didn’t find out what I could and couldn’t do with UAC enabled, and I would have also missed out on the learning experience.

What I originally meant to be a reasonably short post has actually turned out to be much longer than I expected! If you’ve made it to the end and have any comments, or suggestions about alternative approaches, please let me know by leaving a comment below.