LIBNAME Oracle vs Libarary in Management Console. Password missmatch

[PhilipH]

Dear SAS Experts,

we have a LIBNAME statement that we run in EG to create a Oracle Library and it works fine.

When we create the Library using the Management Console (SAS 9.4) and try to register tables in the metadata we receive an error saying invalid username/password. Looking at the libname statement generated I can see that the password has been encrypted.

If I copy the same libname statement into EG the same wrong username/password error appear. However, if I remove the Auth Domain option and change the password into clear text the connection works.

I wonder how, what does the Management Console do differently when creating libraries and trying to register tables than the EG.? Why is the password encrypted? Can I switch off the encryption?

Thanks for any clues.

[Timmy2383]

It's likely that the Authdomain being referenced when EG vs when in SMC is different, and so resolving to incorrect username and password.

Are you logged in as the exact same user in SMC and EG when performing your testing? If you're logged into SMC as sasadm@saspw, and you have multiple Authdomains with the same name, it can get confused (because sasadm@saspw can see them all and likely uses the first one it finds, I think).

Try logging into SMC as the same user you are using in EG.

[PhilipH]

HiTimmy2383 ,
Thanks for your reply! With respect to the testing...I can try that in a few hours again.
I looked up what this Auth Domain Business is about and think it is just a way to group logins.

http://support.sas.com/documentation/cdl/en/bisecag/67045/HTML/default/viewer.htm#p1du6ccnyjmlkdn1pw...

Why would it matter which one I select? I do have multiple Authdomains but they are all named differently and are all really just a test called Oracle_Auth1, OracleAuth2, etc. They have no real effect on the username and password submited to the Oracle Server, right?

[Timmy2383]

I believe every account in metadata has an authdomain called "DefaulAuth" associated with it. This typically would have the username/password for that particular account (assuming that information has been added to the metadadta, which it is not by default). Then, you can create custom Authdomains, usually on the "Accounts" tab of a metadata group, which have specific usernames/passwords associated with them. Who is able to see/use this authdomain depends on their membership in metadata groups.

In your case, if you entered the same username/password for OracleAuth1, OracleAuth2, etc then the same username/password would be submitted to the Oracle database no matter which one you specified. However, the account you're using to register the tables in metadata may not be using using the right one.

Did you specify a specific username/password or authdomain for the library connection? This would be on the "Data Server" tab for the library in SMC.

[PaulHomes]

I imagine that the password stored in metadata is incorrect (or it is being retreived as an unrestricted/admin user). I would suggest you use proc pwencode to encode the known correct password so you can see the encoded/encrypted version of that password and then be able to spot it when used. Is it the same as the one you see when you display the libname in SAS Management Console?

Also, is the encoded/encrypted password that you saw {SAS002}B6535B5C02BB1BC110FD31944FC989D3 by any chance? If so, that's the pwencoded version of 8 asterisks and mean you are trying to use the library as a highly privileged user and are deliberately not being given the correct password. See this thread for more info.

[PhilipH]

Dear PaulHolmes.

The password we use it --not-- encoded as {SAS002}B6535B5C02BB1BC110FD31944FC989D3.
It is something else. But we just compared our encoding with proc pwencode and the one
that we find in the libname from the management console is indeed something different every
day we try to fix the problem 🙂
When we look at the error trying to register tables the password is shown as XXXXXXXXXXXXXXXXXXXX ;
without quotes, showing a space at the end.

Timmy, the Oracle account we used to register tables is specified using the managment console
user manager (Usermanager-> users-> accounts) We are absolutely sure that this user/password specified there is correct.
The connection (server manager) uses the Oracle_ADB, our custom AuthDomain and path leads to the service name in the tnsnames.ora. We cant use DefaultAuth because that is all the SAS users and has nothing to do with users on the Oracle-DB-side.

I wonder, do we need a SAS technical user set up on the Oracle DB side?

When we use EG to register the library and use our custom AuthDomain "Oracle_ADB" it works fine. Even DefaultAuth works strangely..the users in DefaultAuth are not known to the OracleDB.
However to register tables using Management Console it does not.
We are going nuts here 🙂

[PhilipH]

It seems that the password encoded by management console is different to what it should be (as compared to EG).

We noticed that it does not matter which password we type in. The password will always be set to some odd default with ...D301

we got the reason. it depends who us logged into the management console. depending on the user logged in. the password changes.

it does not really matter what we type as a password into the connection details. very odd.

[JuanS_OCS · Accepted solution]

Hi,

you have got an interesting problem here, and you have got the attention of wise colleagues as well 🙂

What I would do in your case:

- Take your prefered Oracle DB client from your SAS compute machine and connect to the DB and open the table with your Oracle user that is supposed to be used on the Auth Domain. If it works, then we can go to the next, if not, this would be connectivity problem (which I does not seem to be since it works without Auth Domain, but worthy to ensure every step).

- Go to the SAS Management Console  as admin user (metadata unrestricted), then:

a) Go to the user group where your DB user is registered. Ensure that user and password are well typed (type them again just to be sure, since the password would be wrong or empty). Copy the AuthDomain to somewhere else. And check the users you want to connect to the DB are included as members of this group.

b) Go to your Oracle DB connection on Servers Manager, and Ensure it uses the correct Auth Domain, authentication type (user/password), data source, etc, as you used on your previous connection from the Oracle DB client.

c) Go to your Oracle library on the metadata, and ensure it is assigned to the good SASApp, and the Data Server tab (ensure you made the selections even if they are selected), to the DB server, the Schema Name, the connection and the default login (the one defined on the DB user group).

- Open a new SAS Management Console  as one of the users on the DB usergroup and try to register the tables. This should work at this point, therefore also on Enterprise Guide. The EG users will require to disconnect from the SASApp and connec to it again to get the new library definition.

Hope this helps a bit

[PhilipH]

Hi Juan the Wise.

- Take your prefered Oracle DB client..
done, works

- Go to the SAS Management Console as admin user (metadata unrestricted)
done

- Open a new SAS Management Console as one of the users on the DB usergroup and try to register the tables.
that works now. we fixed the issue. what let us to the solution was the fact
that the passwords encoding was just not right for the one of the admin-like users.
Logging in as sasadm it worked. The password encoded correctly.

We then looked at the user manager for the user Oracle_ADB that has to oracle user account added to it.
we played around with the users and groups, restarted the object spawner a couple of times.

to be honest we did not manage to recontruct the whole error.

[JuanS_OCS]

Hi,

well, happy that one of the issues is solved.

In principle, the admin users that have the Metadata-Unrestricted role, they won;t be able to create a session on the workspace server, therefore cannot see the password, encoded or not, since the encoded password is generated by SAS code.

Under very certain circumpstances, even those users can do most of the work and see passwords (but still not everything), but I would not recommend you to reproduce that, in order to keep a clear separation or roles.