SAS EG | Libname ODBC using windows authentication

[MariaD]

Hi folks,

We have a SAS server installed on Windows 64 bits. Our users works with SAS EG connected to this SAS server using integrated windows authentication.

Recently our policy to connect to SQL through ODBC changed. Now, it's only permitted the connection using windows authentication too. 

We tried to add the Trusted_Connection option on ODBC connection but still not working. We only have ODBC, we don't have OLE DB licensed. Could you please advise?

PROC SQL;

CONNECT TO ODBC (required="DSN=myDNS;Trusted_Connection=yes");

CREATE TABLE TEST AS

SELECT *

     FROM CONNECTION TO ODBC (

SELECT ....

);

QUIT;

Regards,

[SASKiwi]

This pretty much mirrors how we do database connections as well and it works great for us. I suggest you try this test. Instead of connecting to SAS from EG using Windows Authentication, put in your userid and password. Then try an ODBC LIBNAME connection using the Trusted_Connection option. If this works then the problem lies with the setting up delegation of your Windows Authentication credentials, to a secondary server (SQL Server).

You could also try logging onto the SAS server itself, start a SAS session and try the same ODBC LIBNAME connection using the Trusted_Connection option. This way you are sure you are only doing one level of WA and that is to the database.

[MariaD]

Thanks @SASKiwi . We tried the second options, added our credentials to libname statement and it's working fine. I'll tried the first options and let you know.

[SASKiwi]

@MariaD - in that case, to get delegation working correctly with EG IWA connections you will need to go through the configuration of SPNs as explained by the other posters.

[vkrishna]

@MariaD 

No changes required in SAS if object spawner service running under the local system account and trusted for delegation enabled on this system. If SAS object spawner service running under a different service account you need to configure custom SPNs for this account. To configure SPNs for object spawner service account see - Manually Registering Object Spawner SPNs

To access Microsoft SQL databases from SAS Enterprise Guide using IWA you need to enable SPNs for SQL service account.

If SQL Server service is not on the same machine as the object spawner service, register custom SPNs for SQL service account - Manually Registering SQL SPNs

Note: It is recommended to use SETSPN -S because -S will verify that there are no duplicates SPNs.

-Vamsi

[SimonDawson]

There is a fairly heavy push from Microsoft to adjust how integrated windows authentication is handled in the Microsoft Windows operating system and environments using Microsoft Active Directory for kerberized authentication. They appear to be obsoleting unconstrained delegation.

There is a SAS usage note that discusses briefly what this means in SAS. There will be more updates to SAS software and documentation for both resource based delegation and constrained delegation in time.
http://support.sas.com/kb/63/143.html