We are using LSF for batch scheduling, with the lsfadm user. When running jobs that connects to databases (MSsql, Oracle) we want to use Active Directory users for access to the databases. What we have done till now:
This works, but when we want to use AD-users for accessing databases we can’t get it to work. When trying to use AD users instead of local database users the batch is not using the database user, but instead uses the lsfadm user in the connection string/libname. We then get errors in the libname statement. We have also tried to put the AD-users account directly on the lsfadm user, but no luck.
How are you scheduling these LSF jobs? We schedule ours in SAS Management Console, and when we do this we are prompted for the user to run the job under and so enter our own user name. Our database library connections are set up for Windows Authentication so it all works fine.
In response to your question about having the AD users schedule flows, you are correct that it can get messy and you have the potential of having the same flow scheduled by multiple users. We use a service account or batch user, something named "marketing_batch" or similar. When scheduling the flow, we use that user.
That doesn't help with your problem, though. I feel like you're really close as you've described your auth domains. Of course, they are the key to doing what you want. My only advice is to make sure your connection in the server object specifies the authentication domain that matches what you have in your metadata group. From the description, it seems like there's a slight adjustment necessary somewhere.
What you've described is entirely possible.
I have tried to define the libname in both the metadata and in an user written code that runs a database procedure. I get the same error:
88 /* Access the data for A.19 analytix_tos_test */
89 LIBNAME anltxtos ODBC DATAsrc=analytix_tos_test SCHEMA=dbo AUTHDOMAIN="analytix_access" ;
NOTE: Credential obtained from SAS metadata server.
ERROR: CLI error trying to establish connection: [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user '**\lsfadm'.
I get the same error if I choose the correct "Default Login" in the libname. Still the lsfadm is being used.
Hey @SASKiwi ,
This looks very familiar. I've attached links to the two SAS notes that I use when setting up SQL Server connections.
As you can see from 42605:
Important note: This type of authentication does not support supplying a user ID in the form of Domain\Userid from the connection in SAS. Authentication of the user ID is done through the use of Kerberos in the operating system. No user ID or password are required on the connection string in SAS.
That seems to be what we're seeing here with your situation. Even though you have the AuthDomain defined correctly, the actual authentication is being done via Kerberos. The user/password, or in this case, the AuthDomain, is not actually passed to the database.
I hope these notes help. I refer to them often when setting up MS Sql Server connections. We have some that use database id's and require AuthDomains, and some that use Kerberos.
Hope this is helpful.
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.