SAS and IWA: Two Hops

Update 26Sep2018: This post is now several years old and naturally technology and security have progressed in that time. For more up to date information regarding delegation and, in particular, the requirement for constrained delegation when working with Windows Defender Credential Guard in Windows 10 and Windows Server 2016, please see Stuart Rogers’ very useful SAS Global Forum 2018 Paper: SAS 9.4 on Microsoft Windows: Unleashing Kerberos on Apache Hadoop.

In an earlier post I mentioned that I would jot down a few notes about my experiences with SAS® software and Integrated Windows Authentication (IWA). This is the first of these posts and concerns the initial configuration. Chances are, if you knew you wanted to use IWA before you installed SAS, then it would have been discussed and implemented during the initial installation and configuration. If you decided to implement IWA after the fact then you would most likely have followed the instructions from either:

A basic SAS and IWA configuration might then look something like this. In the diagram below we have a client PC (saspc001), a dedicated metadata server machine (sasmeta) and an application server machine (sasapp). This is a homogenous environment consisting of all Windows machines in the same Windows domain. Other configurations might have multiple domains that trust each other, and now with SAS 9.3, some of the SAS servers may also be UNIX based (assuming the prerequisites are met).

In the diagram above a SAS Enterprise Guide user working on the saspc001 workstation initially connects (1) to the SAS Metadata Server on sasmeta using a connection profile with IWA enabled. When they run a project, an IWA connection (2) is then made to the SAS Object Spawner on sasapp to launch a standard SAS Workspace Server to execute the SAS code. The logical SAS Workspace Server has been configured in metadata to accept IWA connections. Both of these IWA connections involve only 1-hop from the workstation: saspc001 to sasmeta, and saspc001 to sasapp.

Problems might then arise when secondary connections need to be made from the workspace server to additional servers and access denied errors are seen in the SAS log. One example of a secondary connection includes executing code on the workspace server that reads a CSV or XML file from another file server (filesrv) using a UNC path (e.g. \\filesrv\share\file.xml). Another example might be assigning a library in the workspace server session that uses SAS/ACCESS Interface to ODBC or SAS/ACCESS Interface to OLEDB to connect to a Microsoft SQL Server database on another server (sqlsrv). These examples are shown in the diagram below as (3) and (4) respectively.

Both of these example involve IWA being used in 2-hops from the client. In the first hop (2) IWA is used to connect from saspc001 to sasapp as before. In the second hops the SAS workspace server process has to then connect and authenticate to the secondary servers: sasapp to filesrv (3), and sasapp to sqlsrv (4). It is these second hops which may fail if additional measures have not been taken:

  • Trusted for Delegation: the intermediate server (sasapp in this example – where the workspace server is running) needs to configured in Active Directory as Trusted for Delegation. This must be done by a domain admin. This configuration is mentioned in the SAS Intelligence Platform: Security Administration Guide on the Windows Privileges page for both SAS 9.2 and SAS 9.3. In a future post I’ll show the method I use as a non-domain-admin to double check this as part of the troubleshooting process.
  • Force Kerberos: you also need to ensure the Kerberos protocol is used and not NTLM. Whilst you could get all of your users to configure their SAS client connection profiles to use Kerberos, it is usually preferable to leave the clients alone and instead configure the SAS servers to only offer Kerberos and not NTLM. This is documented in the in the SAS Intelligence Platform: Security Administration Guide on the How to Force Use of Kerberos page for SAS 9.2, and the How to Configure Integrated Windows Authentication page for SAS 9.3. Things can get a bit trickier when DNS host aliases (or CNAMEs) are used in environments configured for disaster recovery. In a future post I’ll show some examples of additional Service Principal Names (SPNs) that might be required in these situations.

So if you find yourself getting access denied messages when using SAS and IWA in situations where multiple hops are involved, I hope this post gives you some ideas of things to investigate further.

For more posts in this series have a look at the IWA tag.

Dynamic Logging Reconfiguration in SAS® 9.3

One of the exciting new features in SAS 9.3 (well for me anyway) is the ability to replace or modify the logging configuration on-the-fly for a running server without needing to restart it. Previously in SAS 9.2 you could dynamically change the levels for loggers on servers (via SAS Management Console Server Manager Loggers tabs) but not modify the appender configuration. Now in SAS 9.3 you can completely change the logging configuration file and apply it without restarting the server. This is exciting because it means you can do something like add a new appender for a new custom log file for the metadata server in the middle of the day with out having to restart the entire platform of dependant services. That’s really neat!

I tried it out. I made a small change to the metadata server logconfig.xml, saved it, and then waited … watching the log file. I’d thought that maybe the servers had a watchdog thread to monitor changes to the log config file (a bit like the log4j’s configureAndWatch) but after watching and waiting for a little while nothing happened. I then checked the documentation (always a good idea) and found that you actually need to prod the server to tell it to change its logging config via proc iomoperate (also new in SAS 9.3).

This is the code I used to tell the SAS Metadata Server to reload its logging configuration file (logconfig.xml) – the one I had changed.

proc iomoperate;
connect host='sas93lnx01' port=8561 user='sasadm@saspw' password='thesecretpassword';
set log cfg="/opt/ebiedieg/Lev1/SASMeta/MetadataServer/logconfig.xml";
quit;

It worked. The metadata server immediately closed it’s current log file (SASMeta_MetadataServer_2012-01-10_sas93lnx01_1266.log) and opened a new one with a _0 suffix (SASMeta_MetadataServer_2012-01-10_sas93lnx01_1266.log_0) using the modified config. I wondered why it had added the _0 suffix? Subsequent executions increase the log file suffix (_1, _2, etc.). Perhaps it was done to delineate the old and new logging config in case someone wanted to parse the logs? If anyone knows how to make it continue to log to the same log file I’d love to know.

Of course this dynamic logging reconfiguration also means you can readily switch between normal and trace level logging config files to troubleshoot a problem and then switch back again quickly – all without a restart. Something like this:

* switch to trace log configuration;
proc iomoperate;
connect host='sas93lnx01' port=8561 user='sasadm@saspw' password='thesecretpassword';
set log cfg="/opt/ebiedieg/Lev1/SASMeta/MetadataServer/logconfig.trace.xml";
quit;

* >>> REPLICATE THE PROBLEM;
* switch back to normal log configuration;
proc iomoperate;
connect host='sas93lnx01' port=8561 user='sasadm@saspw' password='thesecretpassword';
set log cfg="/opt/ebiedieg/Lev1/SASMeta/MetadataServer/logconfig.xml";
quit;

For more info check out the following SAS documentation references:

SAS and IWA (Integrated Windows Authentication) Notes

Over the past year I have worked on a number of SAS® 9.2 platform installations helping to get Integrated Windows Authentication (IWA) working nicely for single sign-on (SSO) for the SAS platform.

Whilst it’s relatively easy to turn IWA on, it can get a bit trickier when you try to go a bit further than the initial connection. Some of the situations I have encountered and resolved along the way have included:

  • SAS Enterprise Guide clients using IWA and running projects on SAS Workspace Servers accessing files from other servers using UNC paths (e.g. \\server\share\file.csv). This required configuring the workspace server machine as trusted for delegation and forcing the use of Kerberos.
  • SAS Enterprise Guide clients using IWA and running projects on a SAS Workspace Server accessing a further SQL Server database using OLEDB and IWA. This also required trusted for delegation and Kerberos as well as options on the OLEDB library definition.
  • Using a SAS Workspace Server to access a further SQL Server database using ODBC and IWA.
  • All of the above where all of the connections were made using host name aliases (DNS CNAMEs) rather than the physical machine names to support transparent disaster recovery switch-over. This involved adding additional host name alias based SPNs for seamless IWA.
  • Scheduled deployed SAS Data Integration jobs run using Operating System services running as the local system account getting IWA access to a SQL Server database running with a specific service account and not the local system account.
  • IWA access to the SAS PC Files Server.

Getting all the stars aligned and making sure IWA is available and used in as many situations as possible can require a few additional steps. Testing and confirming that IWA Kerberos connections were actually being made rather than cached-credential based connections required careful attention to SAS log messages.

Along the way I’ve gathered a few notes and tips and was working on compiling a single blog post so I would have ready access to the information for next time. After a while it became clear this blog post was going to be a monster and so I’ve decided to break it up into a series of smaller more focused posts on specific topics. This will make it more manageable and I can then post them individually as I complete them.

The topics I’m planning on posting about include:

  • Forcing the use of Kerberos.
  • Making intermediate servers Trusted for Delegation.
  • Using AdExplorer to confirm Trusted for Delegation status.
  • Accessing SQL Server databases using SAS/Access Interface to OLEDB and IWA.
  • Accessing SQL Server databases using SAS/Access Interface to ODBC and IWA.
  • SPN requirements for accessing a SQL Server database running as a non-local-system account.
  • Additional SPN requirements for seamless IWA connections when using host name alias based connections to SAS servers for disaster recovery configurations.

Perhaps this can be one of my New Year’s resolutions? Time to get started on the first one.

Please let me know if any of these topics are of particular interest to you, or maybe if you have any other scenarios that you have encountered in your quest for SAS and IWA harmony. In the meantime I hope you all have a great 2012!

SAS Restricted Options on UNIX

In a tweet by Gordon Cox last month, I was reminded of the restricted options facility available with SAS® software on UNIX platforms. This is capability where an administrator can set mandatory SAS system options at multiple levels of granularity: globally, per-group, and/or per-user. The reason for this post is that I don’t look at the documentation for this very often and every time I do it takes me a while to track it down. I always think its going to be in the UNIX companion in the Base SAS area… but it’s not! That gets me every time. Instead it’s tucked away in the Configuration Guide for SAS 9.2 Foundation for UNIX Environments (PDF) in Chapter 2 – Restricted Options. You can find this document in the Install Center section of support.sas.com under SAS Installation Note 36467: Documentation for a SAS® 9.2 installation on UNIX.

The context of the tweet was that the restricted options facility is another mechanism whereby a default setting of the NOXCMD option for SAS platform servers could be overridden for a subset of trusted users or groups in a SAS platform installation. The NOXCMD option is discussed in an earlier post: NOXCMD: NO eXternal CoMmanDs!

A quick summary of restricted options:

  • SAS Systems Options under UNIX set by an administrator, that cannot be changed by a user
  • Processed in the order global, group, then user. The last instance of an option is the one that wins.
  • Global restrictions are read from the file !SASROOT/misc/rstropts/rsasv9.cfg
  • Group restrictions are read from the file !SASROOT/misc/rstropts/groups/<groupname>_rsasv9.cfg
  • User restrictions are read from the file !SASROOT/misc/rstropts/users/<userid>_rsasv9.cfg

On Linux (at least) I can use the command “id -gn <userid>” to find out the effective group name for a user, given their user id. For example, “id -gn sassrv” might generate “sas“.

In my SAS 9.2 installation on Linux, whilst everyone else is still constrained by the NOXCMD option, I can ensure that the SAS Enterprise Guide user Bob Baxter, who has a user id of bob, can still use operating system commands in the SAS programs he runs on the SASApp server, by creating the file /usr/local/SAS/SASFoundation/9.2/misc/rstropts/users/bob_rsasv9.cfg with the following contents:

-xcmd

Of course, this only applies to SAS processes launched and run as the requesting user. Whilst it can be used to override NOXCMD for specific users/groups using a standard workspace server, it cant be used to distinguish between different users on the same stored process server, since all users will share SAS stored process server processes running under a shared identity (like sassrv). In that situation directing the users to separate SAS application servers would be more appropriate. There is an example of this 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

Thanks to Gordon for reminding me about the restricted options facility.