Tag: SQL Server

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:

• SAS® 9.2 Intelligence Platform: Security Administration Guide: How to Configure Integrated Windows Authentication
• or SAS® 9.3 Intelligence Platform: Security Administration Guide: How to Configure Integrated Windows Authentication

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.

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!