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,
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.
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.
@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.
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
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
The SAS Users Group for Administrators (SUGA) is open to all SAS administrators and architects who install, update, manage or maintain a SAS deployment.
Learn how to install the SAS Viya CLI and a few commands you may find useful in this video by SAS’ Darrell Barton.
Find more tutorials on the SAS Users YouTube channel.