Using AUTHDOMAIN from the command line in a SAS Grid environment

[csteiz]

I'm seeking a solution to saving third-party database credentials in my SAS scripts.  Specifically, I need to find a way for SAS scripts that connect to Oracle database servers don't need to contain a user's database password. These scripts are frequently run on a schedule using crontab in our environment, in addition to being run manually by end users.

I'm familiar with using AUTHDOMAINs in my scripts to avoid using database passwords in the script.

For a script that I can run successfully in SAS Studio or SAS EG, I find that it fails when I launch it using crontab.

I see the following message in my program's log:

6 PROC SQL;
7 CONNECT TO oracle (user=&my_sso. AUTHDOMAIN="DatabaseAuth" path="Database");
NOTE: Credential could not be obtained from SAS metadata server.
WARNING: No login information was available for authdomain DatabaseAuth.
ERROR: ORACLE connection error: ORA-12162: TNS:net service name is incorrectly specified.

Any suggestions?  It doesn't seem possible that SAS has not provided a solution for encrypting (NOT ENCODING LIKE PROC PWENCODE) passwords that is also compatible with the command-line interface.

[csteiz]

I'm thinking that maybe teh SAS program I'm running from the command line is not connecting to the SAS Grid, preventing it from pulling my AUTHDOMAIN-saved credentials. I'm wondering if I can use SIGNON somehow at the start of my SAS script to establish my UNIX credentials for the SAS program's session.

For example:

SIGNON AUTHDOMAIN=UNIXAuth, where UNIXAuth contains my UNIX credentials for signing into SAS grid.

[Patrick]

If you run under EG the process runs under your user, if you run in batch then the user is the batch user. This batch user needs to access SAS Metadata to retrieve the credentials and to do so there needs to be a SAS metadata identity for this batch user with the right metadata permissions.

What I'm doing more and more:

Instead of using syntax like... (N.B: If you're using AUTHDOMAIN then you don't provide a user).

CONNECT TO oracle (AUTHDOMAIN="DatabaseAuth" path="Database");

...I'm using...

CONNECT using <libref to Oracle>;

To do so there must of course be a libref available pointing to the DB already available. This could be a pre-assigned library in SAS Metadata (eventually with defer=yes). Also for this approach you still need a SAS Metadata identity that got read access to the SAS Metadata library definition.