topic Re: Stored Process Runs on SAS EG but not on Stored Process Web Server in Administration and Deployment

Stored Process Runs on SAS EG but not on Stored Process Web Server

dobby — Sat, 12 Jun 2021 12:58:29 GMT

I have a stored process that runs fine from EG but fails with error for the Oracle tables.

ERROR: ORACLE prepare error: ORA-03114: not connected to ORACLE. SQL statement: SELECT * FROM SATURN.SGBSTDN.

The stored process accesses both Oracle and MS SQL Server databases but seems to access the MS SQL Server database without issues when run from the Stored Process Web app server. We are using pre-assigned libraries and authentication domains. The Stored Process runs with the general users account and that has the authentication domains set with the correct credentials for all database. It appears that the connection is not even hitting the Oracle database from the Stored Process Web App server. If it did then I would expect an error that the userid/password is incorrect. Again, the same stored process runs fine from SAS EG.

How can I confirm if the stored process is actually connecting to the Oracle database or from where the connection is originating (client computer or SAS Server)?

Does the SAS Stored Process Server attempt to connect to the database with the _RMTADDR or _RMTHOST (client) IP instead of the SAS Server?

Re: Stored Process Runs on SAS EG but not on Stored Process Web Server

Sajid01 — Sun, 13 Jun 2021 11:36:15 GMT

Hello
The basic requirement is that to connect to oracle database, oracle client is needed.

The server on which the code is executing and connecting to, must have an installation of oracle client.

Make sure that the machine on which the stored process is executing has oracle client installed and the properly configured.

Re: Stored Process Runs on SAS EG but not on Stored Process Web Server

dobby — Sun, 13 Jun 2021 12:25:54 GMT

Hi,

Thanks for your reply. Oracle client is configured on the server that the code is running from. As I stated, the stored process runs fine from SAS EG which is executed through the same server as the Stored Process Server. I have tested the Oracle connection using the ODBC Administrator from the server as well and that works.

I'm using the Oracle Instant Client on the server.

Do some SAS components such as EG work with the Oracle Instant Client and others such as Stored Process Web App server doesn't?

How can I enable debugging to see that the SAS Stored Process Web App server is attempting to connect to the correct Oracle database and any additional log message beyond what it shows in the log from the code?

Re: Stored Process Runs on SAS EG but not on Stored Process Web Server

Sajid01 — Sun, 13 Jun 2021 13:00:24 GMT

If the oracle connection has been established through metadata server, please see if sassrv account has the necessary permission.
Alternative for a test try adding libname in the stp itself and see if that works

Re: Stored Process Runs on SAS EG but not on Stored Process Web Server

SASKiwi — Sun, 13 Jun 2021 20:05:41 GMT

EG starts a workspace server session that will use the user credentials defined in the connection profile.

A SAS stored process runs on the Stored Process Server that by default uses the sassrv service account (you can confirm the account used in SAS Management Console). It is this account that needs the right Oracle database permissions.

Re: Stored Process Runs on SAS EG but not on Stored Process Web Server

dobby — Sun, 13 Jun 2021 20:06:30 GMT

Hi,

I added the LIBNAME in the stored process itself and it worked fine. What does this mean?

Re: Stored Process Runs on SAS EG but not on Stored Process Web Server

SASKiwi — Sun, 13 Jun 2021 20:22:17 GMT

It means the Stored Process Server account has the necessary Oracle database permissions. Since the database connection is not working when assigned in metadata it could be the sassrv account does not have the required metadata permissions.

Re: Stored Process Runs on SAS EG but not on Stored Process Web Server

dobby — Sun, 13 Jun 2021 20:29:45 GMT

I've confirmed that the account has the correct permission to the Oracle database. If it was a permission issue then I would expect either of the following error:

ORA-01031: insufficient privileges or ORA-00942: table or view does not exist.

If the credentials were incorrect then I would get ORA-01017: invalid username/password; logon denied.

The Oracle error I'm getting is:

ORA-03114: not connected to ORACLE.

This suggests that it is not even hitting the database of it trying to connect to a database that doesn't exist when the pre-Assigned libraries are used.

When creating the stored process from a project, is there something special that I need to do in the wizard in step 4 of 6 (Librefs) for Pre-Assigned libraries?

Re: Stored Process Runs on SAS EG but not on Stored Process Web Server

Sajid01 — Sun, 13 Jun 2021 21:08:49 GMT

This means your sassrv account does not have the proper permissions to connect to oracle in the metadata.

Re: Stored Process Runs on SAS EG but not on Stored Process Web Server

Sajid01 — Sun, 13 Jun 2021 21:12:42 GMT

Please see this https://support.sas.com/kb/17/720.html This is a bit dated but relevant.
Look at item no 3

Re: Stored Process Runs on SAS EG but not on Stored Process Web Server

dobby — Mon, 14 Jun 2021 03:41:11 GMT

I followed step 3 and granted Read and ReadMetaData to the SAS General Users group but I'm getting the same Oracle Error.

ERROR: ORACLE prepare error: ORA-03114: not connected to ORACLE. SQL statement: SELECT * FROM SATURN.SGBSTDN.

In fact, I get the error from within EG as well if I explicitly select Stored process server only for the Server type in step 3 of the wizard. It runs fine in EG if I leave it at Default server or Workspace server only.

Re: Stored Process Runs on SAS EG but not on Stored Process Web Server

Sajid01 — Mon, 14 Jun 2021 12:30:02 GMT

That is weird You are not able to run the stored process using the stored process server even in the SAS EG.

Please check the stored process log [SASConfig]/Lev1/Web/Logs/SASServer1_1/SASSToredProcess9.4.log and ;SASConfig]/Lev1/SASApp/StoredProcessServer/Logs along with other logs along with Metadata Server Log/ Object Spawner log.

It be worthwhile if you can enable EG Logging

Rather than spend time searching what happened in the past, I suggest start a new EG Session, create and run a stored process with stored process server. Please check all the relevant logs for this session.(the two logs above, metadata server, object spawner logs and the SAS EG log).

They will hold some clue.

Re: Stored Process Runs on SAS EG but not on Stored Process Web Server

dobby — Mon, 14 Jun 2021 14:06:17 GMT

Thank you.

The EG logs seems to provide some details of the error. The Stored Process and Object Spawner logs are not being updated to show the error. Maybe the logging level isn't set to the appropriate level.

I've shipped off the EG log file to SAs so let's see what comes back from them.

Re: Stored Process Runs on SAS EG but not on Stored Process Web Server

Sajid01 — Mon, 14 Jun 2021 14:49:02 GMT

Thanks.
I see this.

2021-06-14 09:56:01,887 [1] ERROR SAS.EG.ProjectElements.StoredProcess [(null)] - get_Server() - SDS stored process creation failed. 
SAS.EC.ServerInterfaces.SDSException: Item does not exist
   at SAS.Shared.DNADecoder.CreateObject()
   at SAS.EG.ProjectElements.StoredProcess.CreateSdsStoredProcess()
   at SAS.EG.ProjectElements.StoredProcess.get_Server()

Can you validate the stored process server in the SAS Management console?

Re: Stored Process Runs on SAS EG but not on Stored Process Web Server

Sajid01 — Mon, 14 Jun 2021 14:53:17 GMT

If you are not able to validate the stored process server, and if it is possible, please try restarting the server.
Try validation of SAS Process Server Again. Hopefully it should work.

Re: Stored Process Runs on SAS EG but not on Stored Process Web Server

dobby — Mon, 14 Jun 2021 16:23:18 GMT

The server validated successfully. I did a restart and they are now running on in both EG and from the Stored Process Web Server. I'm not sure exactly which of the changes fixed the issue but thank you so much. I appreciate your patience and responsiveness.

Re: Stored Process Runs on SAS EG but not on Stored Process Web Server

dobby — Mon, 14 Jun 2021 18:10:40 GMT

Looks like I spoke too early. Went to lunch, came back to confirm that all is well before updating the users and now I'm getting the same error with the stored processes running of the stored process server.

Re: Stored Process Runs on SAS EG but not on Stored Process Web Server

Sajid01 — Mon, 14 Jun 2021 18:30:25 GMT

1.Do you get the same message on the EG?
2.We need to find where your stored process logs are

In the file [SASConfig]]/Lev1/Documents/Instructions.html you will find the details of your installs including the location of log files. You will find the locations of stored process logs.
Please have a look at them.
These types of issues do come, and that is why company's hire SAS Admin. So no worries.

Re: Stored Process Runs on SAS EG but not on Stored Process Web Server

dobby — Mon, 14 Jun 2021 18:41:12 GMT

Got a response from SAS and they asked me to run the following SAS code in the stored process.

%put &=SYSVLONG &=SYSSITE;

%put &=SYSSCPL &=SYSSCP;

%put &=SYSUSERID &=SYSHOSTNAME;

It returns SYSUSERID=sassrvlocal. I didn't find sassrvlocal setup as a user in management console (metadata). It is a local Windows account that's on the SAS server and used as the Spawned Server Account. Should there be a metadata account created for sassrvlocal?

Re: Stored Process Runs on SAS EG but not on Stored Process Web Server

Sajid01 — Mon, 14 Jun 2021 18:52:00 GMT

This is the SAS general Server account.

So how did this stored process run?