Tag: Base SAS

A little while ago, as part of the work on our recent Metacoda Plug-ins 3.0 R2 release, I used SAS® 9.3 M2 to set up a metadata bound library by following the useful instructions in the Setting Up a Metadata-Bound Library section of the SAS® 9.3 Guide to Metadata-Bound Libraries document. This post is about verifying that, post-setup, I no longer had direct access to the tables in the library.

Metadata bound libraries allow you to force clients to visit the SAS Metadata Server first, before they can gain access to the contents of the tables in the library. This gives the metadata server the opportunity to verify appropriate metadata access permissions. Direct access to those secured libraries and tables from Base SAS, without a visit to the metadata server, will be blocked. I first heard about metadata bound libraries at the SAS Global Forum in April this year and had been looking forward to trying them out. If you’re interested in them as well I recommend reading the documentation. Andy Ratcliffe wrote a NOTE: blog post about them recently too. If you don’t yet read Andy’s NOTE: blog I’d definitely recommend adding it to your RSS reader, and he also wrote a post last week about RSS in How Do You Read?.

This is the code I use to configure the metadata bound library (not the real pwencoded password):

The SAS® METALIB Procedure, available in SAS 9.3, 9.2 and 9.1, is used to keep logical table metadata in sync with the physical tables that the metadata describes. It can also be used to report on any differences or discrepancies between the physical table and logical metadata descriptions. This post shows how ODS OUTPUT can be used in combination with PROC METALIB to capture its report output as SAS tables for further analysis and processing. Continue reading “SAS PROC METALIB and ODS OUTPUT”

SAS® platform administrators can get quite familiar with the SAS NOXCMD option, usually when someone asks why their programs that used to work (in a different SAS version or execution environment) are now failing. Perhaps the programs ran ok with SAS Enterprise Guide 4.1 using a SAS 9.1.3 server but now fail with SAS Enterprise Guide 4.2 using a SAS 9.2 server. Maybe they were developing some code using SAS Display Manager (I know it’s no longer de rigueur but us old-timers will still confess to using it from time to time – some even quite proudly as a badge of honour!). When that code gets deployed in a stored process on a SAS 9.2 server it then starts failing (hmmm perhaps we should have been developing and testing it in SAS Enterprise Guide or SAS Data Integration Studio after all ;)). You may have seen logs with this error:

SAS Problem Note 41058: Unable to submit X commands from SAS® Enterprise Guide® connecting to a SAS® Metadata Server when running SAS® 9.2 or later. I noticed at the end of this SAS note it says “… the commands will execute on the metadata server and not on your local PC“. Whilst this would be the case if it were the SASMeta – Workspace Server (a workspace server restricted to metadata administrators that runs on the metadata server machine), it would only be the case for the SASApp – Workspace Server if the workspace server happened to be on the same machine as the metadata server. In many of the installations I see, the metadata server runs on its own dedicated machine, and so this is often not the case. To clarify, it runs the commands on the host machine that the SASApp – Workspace Server is configured to run on.

In the rest of this post I am going to provide some more info, from the platform administrator’s perspective, and try to offer some suggestions and pointers to additional resources that help you with the goal of trying to work with the NOXCMD option. Ideally we want to try to retain the security benefits by not turning it off for the entire user base, in a shared platform environment, when a subset of your users encounter it and perhaps try to persuade you to turn XCMD back on!

The NOXCMD option disables a number of SAS language features that allow the execution of operating system commands such as:

the use of PIPE in the FILENAME statement (SAS documentation links for Windows and UNIX)

The SAS documentation for NOXCMD can be found under its positive alternative, the XCMD system option. Here are links to the relevant documentation for Windows and UNIX.

We have become more familiar with NOXCMD in recent years because the the SASApp – Workspace Server, SASApp – Pooled Workspace Server and SASApp – Stored Process Server in SAS 9.2 installations commonly have the XCMD option disabled (i.e. NOXCMD). This is the default setting in newly installed SAS 9.2 deployments for its security benefits. To allow the general SAS user community (such as SAS Enterprise Guide users) to execute operating system commands on the servers, a SAS platform administrator has to make a conscious decision to enable XCMD, preferably after careful consideration of the potential security consequences ;). Here is a screenshot fragment showing where XCMD can be turned on in the server’s Launch Properties tab. You get to this tab using the SAS Management Console 9.2 Server Manager plug-in via the Advanced Options button on the Options tab in the server’s properties dialog.

While you are in the SAS Management Console, you might also notice that the SASMeta – Workspace Server is different in that it has XCMD enabled by default. Normal SAS users can’t use the SASMeta – Workspace Server because metadata access controls make it visible only to metadata administrators (members of the SAS Administrators group).

If you schedule SAS jobs to run in batch you might also want to check the NOXCMD status of the batch servers: SASApp – SAS DATA Step Batch Server, SASMeta – SAS DATA Step Batch Server and any others you may have. The XMCD/NOXCMD setting for these servers are specified not in metadata but in the batch server scripts. On Linux, my batch server scripts are in /usr/local/SAS/ebiedieg/Lev1/SASApp/BatchServer/sasbatch.sh and /usr/local/SAS/ebiedieg/Lev1/SASApp/BatchServer/sasbatch.sh. The SASApp one has -noxcmd whereas the SASMeta one has -xcmd. These are the default settings for batch server deployments in recent SAS 9.2 releases. If you have an older SAS 9.2 release (pre M2), you might find that the SASMeta one is also -noxcmd. The NOXCMD constrained SASMeta – SAS DATA Step Batch Server in older SAS 9.2 releases caused problems with scheduled metadata backups as described in SAS Problem Note 34923: A metadata backup that is scheduled to run by using the SAS DATA Step Batch Server might fail to copy the configuration files and the journal files

So what can you do if your security plan doesn’t allow you to enable XCMD for the general SAS user community, but some of your users, or batch processes, are failing to run operating system commands because of it? A couple of possible options include:

creating a new special purpose application server and associated workspace and/or stored process servers that are restricted in visibility (via metadata access controls) to a subset of trusted users/developers. There is a good example of this approach in Jim Fenton & Robert Ladd’s SAS Global Forum 2010 paper 311-2010: A Practical Approach to Securing a SAS® 9.2 Intelligence Platform Deployment

I’ll try to suggest some native SAS language alternatives to operating system commands in future blog posts. If you have any favourites yourself feel free to leave a comment.

I hope this post has helped you find out more about the NOXCMD option and that you don’t always have to turn it off at the first sign of trouble. Remember people tasked with managing security have a reputation to uphold: when it comes to security the first answer is almost always “no”, or perhaps the more pragmatic “no, but how about this alternative more secure method instead…” ;)

Every now and then I need to update the version of the ExcelXP tagset for a SAS® 9.1.3 platform installation.

SAS programmers use the SAS Output Delivery System (ODS) and ExcelXP tagset to generate good looking multi-sheet Microsoft Excel files (in XML format). This tagset has gone through a number of versions so the version you have installed could be an old one. The installation I saw today had v1.28 whereas the latest available version is v1.86 (as of today).

The options available for updating the version of the ExcelXP tagset are documented in SAS Usage Note 32394: Installing and Storing Updated Tagsets for ODS MARKUP at http://support.sas.com/kb/32/394.html.

I usually modify the main SASHELP.TMPLMST so the updated version is available for the entire SAS platform installation. This involves manually downloading the latest ExcelXP tagset PROC TEMPLATE code from http://support.sas.com/rnd/base/ods/odsmarkup/excltags.tpl and then running the downloaded code using a small %include wrapper as shown in the usage note.

Today it occurred to me that I could combine the steps, modifying the %include wrapper to automatically download and execute the latest tagset code from the SAS web site using the URL access method.

Whilst this code removes the manual step of downloading the code and providing the download location in the %include wrapper, there are a few things you might want to consider before using it:

Automatically downloading and running code from an internet site using the URL access method with %INCLUDE is potentially very dangerous. Whilst the URL in the code above currently points to a valid SAS web site location for the latest ExcelXP tagset code this may not always be the case. Consider what you might be %include-ing if the ExcelXP tagset code is ever moved (a 404 error page?) or the code at that location changes (such as in an unlikely worst case scenario where your providers DNS is compromised and support.sas.com is directed elsewhere!). Of course these concerns also exist with manually downloaded code but with manually downloaded code you do have the opportunity to review the code before it is executed – assuming you take that opportunity :)

The server or workstation you run the code on needs to have internet access. If you need to use a proxy server you will need to specify additional options on the initial filename statement.

Are you sure the version you are downloading and installing is the version that is required (and has been tested with any existing ODS code)?

After having read the caveats above you might decide that although this might have been an interesting exercise, it may not be appropriate for you to use. I fully understand any such reservations. Although I will probably use this code again myself, I will only do so after first checking the URL provides valid ExcelXP tagset code from the machine it is going to run on.

Finally, if you want to find out which version of the tagset you have installed you can use the following code:filename xlxptmp temp;
ods tagsets.excelxp file=xlxptmp;
ods tagsets.excelxp close;
filename xlxptmp clear;

After I had run the automated update the version check code generated the following log fragment:NOTE: This is the Excel XP tagset (SAS 9.1.3, v1.86, 04/15/08). Add options(doc='help') to the ods
statement for more information.