Unable to publish query layer with discrete parameters

[raja-gs]

Hello,

I'm unable to publish Query layers with parameters in WHERE clause to our ArcGIS Enterprise portal - from Arcpro and also directly on arcgis server manager. Within Arcpro, params are recognized and map is displayed properly. Layer publishes successfully when parameter is removed. Query references data from a registered Oracle DB.

I tried even a simple query like 'select * from tablename where colname = ::var1'. Other layers referencing same DB connection are working fine.

I've reached out to ESRI support too. But I'm wondering if someone faced this weird issue or if I'm missing something obvious. Below are some details

DB: Oracle 19c Enterprise, ArcGIS Pro 2.9.1, ArcGIS Enterprise 10.8.1

Publishing error: 

ERROR 001487: Failed to update the published service with the server-side data location. Please see the server's log for more details.

Server log:

Failed to create the service.: Updating the server connection string for layer WCT failed. Attempted connection string was ENCRYPTED_PASSWORD=.................AUTHENTICATION_MODE=DBMS. Table name is USER.%WCT_1. Please verify the data exists on the server.

[SzuNyiapTang]

Hi @raja-gs 

It seems like the issue is the connection from ArcGIS Server to Database. 

It could be one of the following issue:

1. Firewall not open between ArcGIS Server Machine and Database Server Machine.

2. The registered Database connection is using another user which does not have access to the layer.

Cheers,

Tang

[TanuHoque]

@raja-gs 

I just tried publishing (By reference aka not-copying data while publishing) from Pro 2.9 to a portal 10.8.1 and it worked for me. The database is an Oracle one. I just wanted to share my findings here.

[raja-gs]

@TanuHoque 

After ruling out many things, ESRI tech seems to think this could related to DB user permissions. Could you tell me what privileges you granted to DB user connecting to Oracle (other than Read)? And, should the Arcgis service account (used for running arcgis Windows services) have access to Oracle DB as well? Thanks

[TanuHoque]

hi @raja-gs 

After ruling out many things, ESRI tech seems to think this could related to DB user permissions.

hmm... please share the bug number once it is logged.

Could you tell me what privileges you granted to DB user connecting to Oracle (other than Read)?

I think I have both read and write privileges. Unfortunately (and sadly) I'm not good with Oracle and security 😐

And, should the Arcgis service account (used for running arcgis Windows services) have access to Oracle DB as well?

Not unless you are using OS authentication or you chose some advanced options when you registered the workspace to server datastore. Otherwise, the user credentials you provided in the connection file when you access your Oracle instance from Pro is what gets used during publishing and by the map service while reading.

let's see what analysts find.

That said, did you try saving out as a .sd file from Pro and use the server Manager to publish using the .sd file? Just checking in case that works somehow.

[raja-gs]

Yes, I also tried publishing the SD directly on ArcGIS server. Same error message. I'm glad to hear service account need not have access to DB (since we aren't using OS auth), because that would be a major issue for our setup. 

We'll focus on the DB user account and we'll see if ESRI tech support has any luck. And, I shared the support number in chat if you'd like a look. Thanks for your time! 

[raja-gs]

Thanks Tang and Tanu,

Without the discrete/range parameters, I'm able to publish query layers using exact DB and connection.  So, I suppose it's not a firewall or DB user issue. I also tried unregistering all managed connections (other than the arcgis datastore), and registering again. I see dsconnections files are updated properly. Still, same error. I'm just waiting for ESRI tech to respond back.

[raja-gs · Accepted solution]

ESRI analyst reproduced the issue and has logged a bug.

Per our discussion, issue is fixed in ArcGIS Enterprise 10.9.1 version. Hope it helps someone.