topic Re: Using Windows Active Directory in SAS Linux using kerberos passing credentials to SQL Server DB in Administration and Deployment

Using Windows Active Directory in SAS Linux using kerberos passing credentials to SQL Server DB

jim11 — Mon, 04 Nov 2024 14:41:14 GMT

Looking for information on how to setup in a Linux Base SAS deployment using version 9.4 m7 , where we currently can pass Windows Active Directory sign on information into SAS but want to be able to then pass that AD information to databases in SQL Server. In talking with SAS they said they recommended using Kerberos. We are looking for information on how to setup the pass through of info to Linux so that users would only need to supply their sign on information once and the sign on information wouldn't need to be in a lib ref statement, etc. it would be passed automatically. How would we set this up in SAS to pass the info. If anyone also knows the Kerberos settings that would be needed, that would be helpful as we are not sure how to set that up. We have setup Kerberos thru our Linux admin and currently when we try to pass the information we are getting the following error:

ERROR: CLI error trying to establish connection: [SAS][ODBC SQL Server Wire Protocol driver]Security Services Error: Unspecified
error at the GSS layer. There may be other messages with more info. : [SAS][ODBC SQL Server Wire Protocol driver]Security
Services Error: Server not found in Kerberos database.
ERROR: Error in the LIBNAME statement.

The libname statement is setup as follows:

LIBNAME tempwork odbc dsn=temp_work SCHEMA=XXXXXX;

Please let me know if you need further information

Thanks

Re: Using Windows Active Directory in SAS Linux using kerberos passing credentials to SQL Server DB

JuanS_OCS — Mon, 04 Nov 2024 14:58:02 GMT

Hi there,

there are several options, this one is relatively easy, shared a while ago by @PaulHomes :

https://platformadmin.com/blogs/paul/2015/07/active-directory-authentication-for-sas-on-linux-with-realmd/

realmd can make the life easy for the first step, which is the OS side.

From here, you will need to ensure the Kerberos registration in AD through a service account (UPN) trusted for delegation, which contains the SPNs of your SASservice/host.domain and of your database too, as documented.

Re: Using Windows Active Directory in SAS Linux using kerberos passing credentials to SQL Server DB

jim11 — Thu, 07 Nov 2024 16:18:12 GMT

I reviewed the link, I think that that is setting up Windows AD pass to SAS in linux which is what we do today using realmd. I would like to find out once we are able to pass the info, how can I get SAS to use the info and pass it down to SQL Server databases, etc so that users would not need to embed signons and passwords in their SAS code. We would like it to be able to just pass the credentials that sas used from AD and use them again for SQL Server authentication. Is there a part in that where SAS can pass the info or Linux? The error I am seeing when we try saying that the server (SQL Server database) is not in the Kerberos database error message...not sure where to update that or fix this issue?

Re: Using Windows Active Directory in SAS Linux using kerberos passing credentials to SQL Server DB

JuanS_OCS — Thu, 07 Nov 2024 16:42:37 GMT

Okay, I understand you have SSO working with Kerberos and SAS working fine.

Does this mean..?

your SAS EG users can login with IWA already, without credentials?
Does a kinit & klist work giving you a valid ticket?
Have you enabled your SAS Servers (starting with SAS Workspace server) to enable working with Kerberos? Eg:

-authkerb
-princ your-service-account@YOUR.REALM.COM
-keytab /path/to/service-account.keytab

Do you have a keytab created for your UPN and SPN as I indicated earlier? Eg:

ktutil
addent -password -p your-service-account@YOUR.REALM.COM -k 1 -e RC4-HMAC
wkt /path/to/service-account.keytab

Did you set your SAS startup scripts to load the environment variables?

export KRB5_KTNAME=/path/to/service-account.keytab
export KRB5_CONFIG=/etc/krb5.conf
export SAS_USE_KERBEROS=1

Having you already have done this:

You need to configure your SLQ database for Kerberos authentication, to allow GSSAPI
You created a SPN for the database (ensure it is registered in your SPNs together with the SPN of your SAS service)
Test your Kerberos Authentication, without SAS, with your SQL server client of choice
In SAS Metadata, have an Authentication Domain for your database. Meaning: a) create a group for your database users, include your users, in connection tab add the login of your service account to database connection (UPN?), assigning a label to your Authdomain (eg, SQLAuth); Optionally in addition, in your library, point to the database and the chosen Auth Domain. If you do this, when you use the libname generated by the metadata, the authdomain will be automatically used.
If you your code (eg autoexec) use the libname, just use the "authdomain" when it is not taken directly from the metadata.
Do test your connection

This being said, I assume your service account, your users, the SAS server and the SQL server are under the same domain tree, and not in branches or other domains. If you need to cross domains, Kerberos/SSO will not work (or it is quite challenging and not supported by Microsoft, Linux or SAS).

If you or your team have enough confidence in Kerberos, you can probably achieve it by yourself. If not, as you see it has some complexity, I would highly advise to reach out to a certified SAS professional, either in SAS or an specialized partner.

Re: Using Windows Active Directory in SAS Linux using kerberos passing credentials to SQL Server DB

jim11 — Thu, 07 Nov 2024 17:15:54 GMT

Let me clairify...When we log into SAS in Linux via putty we provide the username and it will use that to validate against AD and allow you in...without the user providing the password...in SAS EG we provide the username and password same as our Windows AD account so in that way users don't have to have different credentials for SAS as we are using AD to validate them... At this time we don't have SSO setup in sas where they would log into their laptop and SSO would get them logged into SAS, etc...

Re: Using Windows Active Directory in SAS Linux using kerberos passing credentials to SQL Server DB

JuanS_OCS — Thu, 07 Nov 2024 18:11:11 GMT

The reason why I was detailed on the list of checks was also to point you out in the direction of items to do in case you have not done it yet. What else do you need?