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

[dobby]

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?

[Sajid01]

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.

[dobby]

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?

[Sajid01]

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

[dobby]

Hi,

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

[SASKiwi]

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. 

[Sajid01]

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

[SASKiwi]

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.

[dobby]

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?

[Sajid01]

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

[dobby]

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.

[Sajid01]

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.

[dobby]

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.

[Sajid01]

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? 

[Sajid01 · Accepted solution]

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.