topic Re: Register Table in Oracle Library through SMC in Administration and Deployment

Register Table in Oracle Library through SMC

Mushy — Wed, 22 Feb 2023 13:54:03 GMT

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

Re: Register Table in Oracle Library through SMC

LinusH — Wed, 22 Feb 2023 14:20:30 GMT

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.

Re: Register Table in Oracle Library through SMC

Mushy — Wed, 22 Feb 2023 14:31:26 GMT

@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

Re: Register Table in Oracle Library through SMC

LinusH — Thu, 23 Feb 2023 13:01:42 GMT

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?

Re: Register Table in Oracle Library through SMC

AhmedAl_Attar — Thu, 23 Feb 2023 14:21:36 GMT

@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

Re: Register Table in Oracle Library through SMC

AhmedAl_Attar — Thu, 23 Feb 2023 14:25:59 GMT

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

Re: Register Table in Oracle Library through SMC

AhmedAl_Attar — Thu, 23 Feb 2023 14:28:48 GMT

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/

Re: Register Table in Oracle Library through SMC

Mushy — Fri, 24 Feb 2023 08:20:36 GMT

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

Re: Register Table in Oracle Library through SMC

Mushy — Fri, 24 Feb 2023 08:55:46 GMT

@gwootton Could you help me in fixing this issue:

Re: Register Table in Oracle Library through SMC

AhmedAl_Attar — Fri, 24 Feb 2023 10:44:48 GMT

@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

Re: Register Table in Oracle Library through SMC

gwootton — Fri, 24 Feb 2023 13:30:50 GMT

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?

Re: Register Table in Oracle Library through SMC

Mushy — Fri, 24 Feb 2023 14:38:09 GMT

@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

Re: Register Table in Oracle Library through SMC

gwootton — Fri, 24 Feb 2023 14:47:13 GMT

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.

Re: Register Table in Oracle Library through SMC

Mushy — Fri, 24 Feb 2023 15:07:29 GMT

@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