Updating/registering metadata for a certain table in ORACLE from Management Console

[devrant]

Hello!

I'm facing the following issue when updtating a certain table's metadata in MC:

/********************************************************************************

*	Tabellennummer: 1
*	Tabellenname: QW_KOSTENTRAEGER
********************************************************************************/


1                                                          Das SAS System                               17:11 Monday, April 24, 2017

NOTE: Copyright (c) 2002-2012 by SAS Institute Inc., Cary, NC, USA. 
NOTE: SAS (r) Proprietary Software 9.4 (TS1M4 MBCS3170) 
      Licensed to ******.
NOTE: This session is executing on the X64_SR12R2  platform.



NOTE: Updated analytical products:
      
      SAS/STAT 14.2
      SAS/ETS 14.2
      SAS/OR 14.2
      SAS/QC 14.2

NOTE: Additional host information:

 X64_SR12R2 WIN 6.3.9600  Server

NOTE: SAS-Initialisierung used (Total process time):
      real time           0.00 seconds
      cpu time            0.01 seconds
      
NOTE: The autoexec file, D:\SAS\Config\Lev1\SASApp\WorkspaceServer\autoexec.sas, was executed at server initialization.
1          
2          proc metalib;
3          
4             omr (libid="B40000SI"      repid="A5OV2K45");
5             report(type = summary);
6             update_rule = (noadd);
7             select (A5OV2K45.BE0001XD);
8          
9          run;

ERROR: ORACLE connection error: ORA-01017: invalid username/password; logon denied.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROZEDUR METALIB used (Total process time):
      real time           0.17 seconds
      cpu time            0.03 seconds
      
10         

/********************************************************************************/

When I want to register a certain table I get the following:

1                                                          Das SAS System                               17:35 Monday, April 24, 2017

NOTE: Copyright (c) 2002-2012 by SAS Institute Inc., Cary, NC, USA. 
NOTE: SAS (r) Proprietary Software 9.4 (TS1M4 MBCS3170) 
      Licensed to ********.
NOTE: This session is executing on the X64_SR12R2 platform.



NOTE: Updated analytical products:
      
      SAS/STAT 14.2
      SAS/ETS 14.2
      SAS/OR 14.2
      SAS/QC 14.2

NOTE: Additional host information:

 X64_SR12R2 WIN 6.3.9600  Server

NOTE: SAS-Initialisierung used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
      
NOTE: The autoexec file, D:\SAS\Config\Lev1\SASApp\WorkspaceServer\autoexec.sas, was executed at server initialization.
1          libname dwh list;
ERROR: Libref DWH ist nicht zugewiesen.
ERROR: Error in the LIBNAME statement.

However if I call libname list or proc datasets it works perfectly with the same credentials:

29         libname dwh list;
NOTE: Libref=   DWH 
      Scope=    Object Server
      Engine=   ORACLE
      Physical Name= ****.world
      Schema/User= ***
30         %let lib=dwh;


31         proc datasets library=&lib;
SYMBOLGEN:  Macro variable LIB resolves to dwh
32         	
32       !  contents data=QW_KOSTENTRAEGER;
33         quit;

NOTE: PROCEDURE DATASETS used (Total process time):
      real time           6.08 seconds
      user cpu time       0.12 seconds
      system cpu time     0.03 seconds
      memory              1912.40k
      OS Memory           20728.00k
      Timestamp           04/24/2017 05:30:53 PM
      Step Count                        6  Switch Count  2

Note: for now I don't have access to \Lev*\ folder so couldn't dig deeper.

Any suggestions what could be the problem?

Thanks!

[PaulHomes]

I assume the first log fragment is provided by the SAS Management Console Update Metadata feature? Who are you logged into SAS MC as - an unrestricted user (e.g. sasadm@saspw) or a normal user?  When you used Update Metadata were you prompted for credentials or were they automatically used (from stored credentials for the DB servers auth domain on your user or a group you are a member of)? Have you confirmed those credentials are valid by using the database client to login (e.g. sqlplus)?

What was used to generate the 2nd and 3rd log fragments and what is the different=ce between them - it looks like they might be from the same session (given the jump from 1 to line 29). What code did you submit in between? Were you expecting the dwh library to have been pre-assigned (is it configured so in metadata or autoexec)? I assume you submitted a libname statement for dwh with the required connection parameters. If so, can you supplied that code (suitably anonymized)? How have you confirmed that any credentials fetched from metadata are the same as any credentials you supplied on a libname statement?

Sorry for the number of questions, I'm just trying to understand your situation.

[devrant]

Hello Paul,

Yes indeed, the first part is from MC, I'm logged in as sasadm@saspw (unrestricted). The credentials were stored credentials, that I've previously added as Auth Domain for the "oracle users" user group. In SQL developer the DB is working perfectly with the same user credentials and path/schema. The library is pre-assigned as I've configred it in metadata.

The second log snippet is also from MC, when I tried to register the table in the library. I was able the register all the other tables in this DB/schema without any (unexpected) problems. The LIBNAME statement generated by MC, if I remove pre-assigment is the following:

LIBNAME dwh ORACLE  PATH="path"  SCHEMA=schema USER=user PASSWORD="secret" ;

When I run (after removing pre-assignment) the above LIBNAME statement with my own user, that is a member of the previously mentioned "oracle users" user group in EG,  the log is as follows:

29         LIBNAME dwh ORACLE  PATH="***.world"  SCHEMA=schema USER=user PASSWORD=XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX ;
NOTE: Libref DWH was successfully assigned as follows: 
      Engine:        ORACLE 
      Physical Name: ***.world

When I run (after removing pre-assignment) a self written LIBNAME statement with my own user in EG,  the log is as follows:

29         libname dwh oracle  path=***.world schema=schema user=user password=XXXXXX ;
NOTE: Libref DWH was successfully assigned as follows: 
      Engine:        ORACLE 
      Physical Name: ***.world

Note: I've used 2 different sessions of EG for each LIBNAME statement.

Sorry, I should've probably mentioned that I used EG for the third log part. Here I've been logged with my own user, that is a member of the previously mentioned "oracle users" user group. Yes the credentials saved in metadata are the same as the ones I've supplied, because that is how I set it up (and also as the ones I use for SQL developer).

Thanks!

[PaulHomes · Accepted solution]

I suspect your problem stems from the fact you are trying to do work as an unrestricted user that involves fetching credentials from metadata. Unrestricted do have a restriction. They are not allowed to fetch passwords from metadata (I assume because they can access everyones logins). A password is returned but it is 8 asterisks (********). Is the encoded password you are seeing {SAS002}B6535B5C02BB1BC110FD31944FC989D3 (pwencode of 8 asterisks)?  You can see a similar problem in this thread https://communities.sas.com/t5/Administration-and-Deployment/Login-password-not-getting-updated-in-S...

Trying doing the SAS MC Update Metadata using a restricted account, such as your normal login, that is able to fetch the shared oracle credentials from metadata.

[devrant]

Thanks Paul!

This did the trick:

I've added my own account to SAS Administrators user group.

Cheers!

[PaulHomes]

That's good to hear. Thanks for marking it solved.