Register Table in Oracle Library through SMC

[Mushy]

Hello,

I have created the Oracle library through Oracle server , linked to the authentication domain by creating a user with valid oracle account.

When I register a table it prompts for username and password , though I have mentioned the authentication domain as default login.

I am expecting to connect to the database without prompting for credentials.

Any help is appreciated!

Thanks

Mushy

[LinusH]

There are probaly several ways of doing this, but I usually create a metadata group which carries this credentials (with the specific authdomain), and then you would be a member of this group.

[Mushy]

@LinusH  Thanks for the response.

Indeed thats how I have created as well. I have also added the oracle group with the special authentication in the library and gave all permissions.

But, when I try  to register table it initially asks for credentials. Is there a setting or am i missing anything to directly authenticate withount prompting for credentials?

Thanks,

Mushy

[LinusH]

I don't have access to such 9.4 environment, so I just f unsorted thoughts:

- make sure that you are logged in so you get a match for the desired metadata user (see bottom right corner - you don't want anything that says PUBLIC).

- Check the metadata server log if that gives any clues about the authentcation process

- Can you access the database using a regular libref using authdomain in SASApp?

- Can you "View data" from let's say DI Studio or EG?

[AhmedAl_Attar]

@Mushy 

Try reading through this SAS online doc Overview of Common Data Sources - its part of the SAS Data Administration Guide.

Hope this helps,

Ahmed

[AhmedAl_Attar]

Here is How to establish Oracle Connection
https://documentation.sas.com/doc/en/bicdc/9.4/bidsag/n017taop8cx07in1r3awuwxlyb9m.htm

[AhmedAl_Attar]

Here is a follow-up Blog from 2013, that can provide some extra insight and advice.
https://blogs.sas.com/content/sgf/2013/05/15/pre-assign-sas-libraries-if-so-which-method/

[Mushy]

Hi @AhmedAl_Attar  Thanks for the information and links. I am indeed following the same process to create the library. The mail question is to know why I am getting a prompt to enter credentials when I try registering a table or updating the metadata?

Have you faced such issue?

Thanks

Mushy

[Mushy]

@gwootton  Could you help me in fixing this issue:

[AhmedAl_Attar]

@Mushy 

Just to reply to your question. No, I never experienced being prompted for my credentials everytime I needed to register a table.

I would highly suggest you contact SAS Tech Support

They would be the best and fastest way for your resolution.

Hope this helps,

Ahmed

[gwootton]

You should not be prompted for credentials when registering a table if you are running the register table wizard as a user who is able to run a workspace server (i.e. not sasadm@saspw) and a user who has credentials stored under the authdomain configured for the connection to the database. If you right-click the library and choose "display libname statement" does that statement work when you run it in a SAS session?

[Mushy]

@gwootton  Thanks for your time for responding.

I created a new server under server manager, updated the path with double quotes and associated with the correct machine.

I created a new user and updated the account with the auth domain mentioned in the server and correct oracle credentials.

I created a new library with the correct auth domain and schema , I added the above oracle user in authorisation and granted him all permissions .

now when I see the libname or try to register tables.. I get the prompt to enter username and password. After I enter the credentials I am able to successfully view the libname or register tables.

Let me know if you need more information.

Thanks

Mushy

[gwootton · Accepted solution]

It sounds like you are conflating credentials with metadata users.

You are logged in the SAS Management Console as a metadata identity (e.g. sasdemo, sasadm@saspw, etc.)
To log in to oracle, there is a separate credential (e.g. oracleuser / oraclepass).
To give sasdemo access to oracleuser/oraclepass, we can use an Authentication domain.
To do this we would create the Authentication domain (e.g. OracleAuth), then under our user (e.g. sasdemo) properties in the account tab we would add a login under the oracleauth domain oracleuser/oraclepass. We could also do this in a group that the sasdemo user is a member of.

Then if we are logged in to SAS Management Console as sasdemo and try to connect to the database to register tables, it should use our stored credential to do so.

[Mushy]

@gwootton Indeed I messed with the credentials. Now I could register the tables without having to provide the credentials. Super thanks for your help:-) 

Best Regards,

Mushy