Architecting, installing and maintaining your SAS environment

Libname Meta and Stored Process Server

Reply
Frequent Contributor
Posts: 115

Libname Meta and Stored Process Server

Hi,

I defined a library in the SAS management console that uses Windows Authentication.

The definition works fine in Enterprise Guide using the libname declaration ( LIBNAME core META library="COREDB")

However, the same definition fails in the stored process server with this error. I can't stop the Stores Process Server from trying to connect with that domain as oppose to the user domain.

I am not sure if the sassrv account has to be granted delegation system in the OS (Windows 2008 Server 64).

SYSDBMSG=ODBC: [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed. The login is from an untrusted domain and cannot be used with Windows authentication.

Jaime

PROC Star
Posts: 425

Libname Meta and Stored Process Server

Hi Jaime,

There are a number of things that need to be in place to get a SAS platform deployment taking full advantage of integrated windows authentication (IWA).   I have only just recently finished helping a customer get IWA working with EG, SAS app servers, server side UNC path and SQL Server library access in a multi-environment, multi-app-server, multi-node clustered platform with lots of users spread over a few AD domains.  Whilst it took a fair bit of prep, methodical testing and troubleshooting it did work well.

If you haven't already seen them I would review the following resources for starters:

There are a number of things that need to be in place for IWA to work well:

  • The machines hosting the Metadata Server, Object Spawner(s) and SQL Server need to be in the same AD domain or in domains with a trust relationship.
  • The machines accounts in AD have to be tagged as 'Trusted for Delegation' to allow additional hops to secondary servers
  • The Kerberos protocol needs to be used for multi-hop IWA
  • The SPNs have to be consistent either through default SPNs, adding any additional required SPNs (using setspn) or manually specified in the client connection profile and/or metadata server definitions.
  • Consistent use of authentication domains
  • SQL Server server / library definitions in metadata configured to use IWA
  • Careful testing/troubleshooting to ensure no cached or stored credentials are available for use (which might make it appear that IWA is working when userid/passwords are being used).
  • Review the SAS and SQL Server logs for connections to confirm IWA and Kerberos are being consistently used

When I get the time I was planning on doing some blog posts about my experiences, but that might not be for a little while yet.

I'll start by asking how confident are you that your EG use of the library was using IWA and not cached/stored credentials?  Did you review the metadata server, object spawner and sql server connection logs to confirm that IWA connections were being done? An initial SAS 9.2 installation does not provide a standard workspace server configured for IWA so you would have had to specifically configure this.  I am wondering if your EG test works because IWA wasn't attempted whereas the stored process server test did attempt IWA and failed and that perhaps the environment does not have everything configured yet to allow IWA?

To help you with this some more information would be required:

  • What is the topology of your installation.  Which machines are your metadata server, workspace server, stored process server and SQL server hosted on.  Are they all in the same domain or different domains?  What are the physical machine names?  What machine names are used in metadata - the physical ones or logical aliases?
  • What EG connection profile are you using?  Is it configured to use IWA, any specific SPN and protocol? What host name is used?
  • What are the metadata properties for your SQL Server, connection and library as registered in metadata?
  • What are the metadata properties for the SAS workspace server, stored process servers etc.
  • Which server is your stored process configured to run on?  Is it a workspace or stored process server?
  • What client are you using to test your stored process?  EG, AMO, SAS Stored Process web app etc.

I know that there are a lot of questions here, but there are lots of things that need to be aligned to get IWA working nicely across the entire platform.

Cheers

Paul

Ask a Question
Discussion stats
  • 1 reply
  • 862 views
  • 3 likes
  • 2 in conversation