topic Re: Using AD-user for database connection in batch triggered by LSF. in Administration and Deployment

Using AD-user for database connection in batch triggered by LSF.

nilshermann_wiggen_knowit_se — Fri, 26 Jun 2020 13:05:46 GMT

Hi!

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:

Created local database users and registered these in SAS metadata.
We have made authentication domains for each user login (to source databases).
We made a metadata user group with the database user account with the correct authentication domain
Then we have placed the lsfadm user in this goup.

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.

Re: Using AD-user for database connection in batch triggered by LSF.

AnandVyas — Fri, 26 Jun 2020 14:34:35 GMT

Are you able to connect to database using AD user outside of SAS successfully?

Re: Using AD-user for database connection in batch triggered by LSF.

SASKiwi — Fri, 26 Jun 2020 23:36:06 GMT

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.

Re: Using AD-user for database connection in batch triggered by LSF.

nilshermann_wiggen_knowit_se — Mon, 29 Jun 2020 08:16:51 GMT

Hi! Thank for your response 🙂 Yes, I can logon to the database outside sas. I also created and tested (successfully) an ODBC connection in OS where sas is running. Thanks again!

Re: Using AD-user for database connection in batch triggered by LSF.

nilshermann_wiggen_knowit_se — Mon, 29 Jun 2020 08:33:05 GMT

Hi! Thank you for the reply! We design the flow in Management Console with our own user. We get prompted when scheduling the flow, like you do, but then we enter "<domain>\lsfadm" and password. Your solution sounds easy and simple in a way, but we rather won't have the flows run by multiple usernames. Doesn't that get a bit messy in LSF? With this solution all administration users that is scheduling flows need access to the source databases also right? We rather want one AD user pr source database for this. In that way we are not dependent on one spesific administrator to administer the flow.
This works fine where we have local database users, but, as the problem is, not when using AD user.
Thanks again for you reply!

Re: Using AD-user for database connection in batch triggered by LSF.

kdebruhl — Mon, 29 Jun 2020 13:38:11 GMT

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.

Re: Using AD-user for database connection in batch triggered by LSF.

SASKiwi — Tue, 30 Jun 2020 22:45:51 GMT

@nilshermann_wiggen_knowit_se - Can you post an example of the failing database connection LIBNAME statement? Are you defining these in code or in metadata?

Re: Using AD-user for database connection in batch triggered by LSF.

nilshermann_wiggen_knowit_se — Thu, 02 Jul 2020 11:14:22 GMT

Hi!

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.

Re: Using AD-user for database connection in batch triggered by LSF.

kdebruhl — Thu, 02 Jul 2020 13:39:03 GMT

Hey @SASKiwi ,

This looks very familiar. I've attached links to the two SAS notes that I use when setting up SQL Server connections.

https://support.sas.com/kb/42/605.html

https://support.sas.com/kb/48/348.html

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.