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.

SAS Management Console 9.3 Password Indication

You’ve probably encountered this issue too. As an administrator you just want to know whether someone has stored a password in metadata or not. You don’t want to know the actual password, just whether there’s one there or not, because if there’s no password it can’t be wrong, whereas if there is a password it might be wrong / outdated.

In SAS® Management Console 9.2 the User Manager plug-in always displayed ******** in the password column on the accounts tab for a user (or group) whether there was a password there or not. A small, but very nice, enhancement in SAS Management Console 9.3 means that it now displays a blank when there’s no stored password and ******** only when there is a stored password. This is a nice indicator to show whether or not a password is present.

Here’s a screenshot for Bob’s SAS metadata identity. We can see he has no password stored for his inbound DefaultAuth domain\bob login, but he does have a password stored for his outbound OracleDBAuth oraclebob login.

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!

Protecting the Unrestricted from Impersonation

I was asked a very insightful question about SAS® metadata security this week. This question and the ensuing investigation means I’ll now consider the inclusion of protections for unrestricted users in my metadata security plans. Especially for securing sensitive environments that have a separate group of user administrators who should not have access to login using unrestricted accounts.

Unrestricted users are very highly privileged users in SAS metadata. Metadata access controls do not apply to them. You might consider SAS metadata unrestricted users as somewhat like the UNIX root user.

The question I mentioned earlier came up when I was discussing how SAS metadata user administrators have the ability to impersonate other users for troubleshooting and testing purposes. This is a very useful feature. The person asked a question along the lines of:

Is it possible for a user administrator, someone in the “Metadata Server: User Administration” role, to impersonate an existing unrestricted user, and then as that impersonated unrestricted user make themselves unrestricted?

We decided to test it out and found that it was indeed possible.

This potential, for user administrators to elevate themselves to unrestricted via the impersonation of an existing unrestricted user, is easily protected against. An explicit denial of WriteMetadata to PUBLIC on each unrestricted user account will do the trick. This ensures that no user, other than an already unrestricted user, will be able to modify the account for those existing protected unrestricted users in order to attempt impersonation. e.g.

Unrestricted User Identity Explicit Permissions (ACEs)
SAS Administrator PUBLIC: -WM
Paul Homes (Admin) PUBLIC: -WM

Of course these additional protections should go hand in hand with other measures to protect unrestricted users such as limiting knowledge of unrestricted user passwords (like the sasadm@saspw password) and ensuring that the adminUsers.txt file is only editable by appropriate administrators.

Update 12Dec2011: Someone has since alerted me to the fact that these additional protections for unrestricted user identities are actually documented in the SAS® 9.3 Intelligence Platform: Security Administration Guide appendices under Checklist for a More Secure Deployment where it states the following:

Consider reducing WriteMetadata access to the user definitions for any unrestricted users. This prevents restricted user administrators from updating an unrestricted user’s definition and then logging on as that unrestricted user. To add this protection, access the Authorization tab of each unrestricted user and add an explicit denial of the WriteMetadata permission for PUBLIC.