IWA -> SAS/ACCESS to OLE DB -> SQL Server Issues

[jklein271]

We recently upgraded to Metadata and have had no luck with the connection found in the subject. The metadata install followed the instructions for IWA so on the EG client side we checked the "Use Integrated Authentication" under my user profile. All of our testing went extremely smooth until I tested a libname connecting to SQL server via SAS/ACCESS to OLE DB. The same exact libname has never had an issue in the old EG 4.1 Repository setup using IWA. However, with IWA checked, the connection fails every time with:

ERROR: Error trying to establish connection: Unable to Initialize: Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.
ERROR: Error in the LIBNAME statement.

It seems the libname is definitely hitting SQL server itself from the looks of that SQL server error. For some reason, my credentials are somehow stripped even with IWA checked. Tech support finally had us try to uncheck IWA and pass our credentials manually via the profile. Even though it's the same u/n and p/w as my network login, it works this way and not via IWA. This is obviously a work around, but we'd rather let IWA drive the credentials. We are waiting for a response from Tech Support, but I thought I'd throw it out there. I can't imagine we are the only EG 4.2 / Metadata setup that is trying to use IWA and SAS/ACCESS to OLE DB to SQL Server. Thanks.

[twocanbazza]

Hi.

I assume you have set up the OLE DB library via the metadata etc. rather than straight SAS code?

Does the error occur when testing from other clients, ie from SMC (with IWA set)

We are using IWA, and are connecting to Oracle and SQL server via OLEDB, with no issues. Reading your comments the one difference is we are using a generic logon to the DB's (set up using SAS Groups and Authentication Domains)...

Barry

[jklein271]

As a quick follow up, we are still have some IWA issues. With my login info manually put into the active profile, I have no problems submitting libnames to network paths. However, as soon as I check IWA and submit the same libname statement, I get "ERROR: User does not have appropriate authorization level for library X.". We've read a number of articles on SAS support relating to this error (customized folder reset, etc) and none of them seem to be applicable. IWA really is the only issue we haven't ironed out since the metadata install and I'm just seeing if anyone out there is seeing IWA related issues. Thanks.

[twocanbazza]

Hi.

We have seen this problem as well, and is now resolved... ie we can access network data.

Issue we saw, was that the sas installer said is all you have to do is check this box and IWA will work... well correct sort of, it worked when accessing data on the same machine...

There was additional setup required to acces data on other machines, like setting up the object spawner on the application server for IWA which wasn't done as part of the installation. Has this been done at your site?

Barry

[ChrisHemedinger]

Another piece that might be missing is the "Trusted for Delegation" setting that allows the SAS server machine to connect to another network resource (your SQL Server) on your behalf. This is usually an Active Directory setting for the Windows machine where SAS is running.

See this topic:
http://support.sas.com/documentation/cdl/en/bisecag/61133/HTML/default/viewer.htm#/documentation/cdl...

Chris

[dwd125]

We are having the same issue. I was able to confirm that our SAS Install tech did not set the Object spawner for IWA. I set the spawner from user/password to IWA via the Management Console and restarted the spawner and the metadata server. Sill no luck. We still get ERROR: User does not have appropriate authorization level for library X.
ERROR: Error in the LIBNAME statement. The target network share is a Windows DFS share if that makes any difference.

What were you access to confirm this resolved your issue.

[twocanbazza]

Hi.

Not sure of the question "What were you access to confirm this resolved your issue. "

Any way have a look at, http://support.sas.com/documentation/cdl/en/bisecag/61133/HTML/default/viewer.htm#/documentation/cdl... to ensure you have got all your ducks in a row... (a link of chris's Link)

The file system we are accessing is NTFS.

Cheers

Barry

[jklein271]

Thanks for the replies, guys. The first thing we noticed was in Chris's link under the limits section. The second bullet states the following:

"If you use IWA for a workspace server that accesses Windows network resources, the Kerberos protocol must be used and the object spawner account must have the trusted for delegation Windows privilege."

We can't set the trusted for delegation Windows privilege because the object spawner is a local system account only. Was there an option during install to select local or domain and we took the wrong path? If so, is there any way to fix this post object spawner install or would this require a re-install?

We're hoping this could be our missing link as we now have kerberos and IWA set according to the documention across the board. Previously, we had missed the setting on the object spawner itself under SAS management console. It had been set to username/password.

[twocanbazza]

Interesting, our object spawner and connect spawners are running as Local system accounts also, but we are able to access data on network drives while using IWA.

I'll do some investigating and see what I can find.

[twocanbazza]

Prob a good time to ask, what are operating systems that you have you spawners running on?

Ours Windows 2008...

And we don't seem to have the trusted window delegation assigned to any users.

[jklein271]

Server 2003 for Itanium. No comments about the itanium (we'll be moving off it soon).