Feature Service Replica Creation fails from Read Only Connection

[FMGLGIS]

Hi, hoping someone can share their experience publishing services for offline use using a connection with a db account with db_reader role in SQL Server. We have a SQL Server database, ArcGIS Enterprise 10.9.1 and EGDB 10.8x.

Our map services are feature enabled and published using an sde connection using a sql server db account which has "db_datareader" role, this practice is mirrored across numerous EGDBs registered against AGS Server as data stores.

We are able to successfully take these feature services offline from one database but not another. There is a  db account with "db_owner" role which IS successfully able to create replicas for offline use in field maps/arcgis pro, however items published from the connection using data reader role yeilds the following issues.

The Feature Class is versioned without the ability to move to base, has global IDs. The only difference is this db_reader account on this database.

SEVERE

Jul 3, 2024, 15:01:39

Error executing tool. CreateFeatureServiceReplica Job ID: jc67fd73fb01c4230ade1369e0fc49195 : ErrorMsg@SyncGPService:{"code":400,"description":"Failed to create replica."} Failed to execute (Create Feature Service Replica).

System/SyncTools10x.GPServer

SEVERE

Jul 3, 2024, 15:01:37

Error: Can't create empty replica. Read only data or unversioned data cannot be replicated. Data versioned with the option to move edits to base cannot be replicated. GlobalIDs are required for two way and one way replica data. Creating one way archiving replica requires archiving to be enabled on the parent. .

System/SyncTools10x.GPServer

[Joshua-Young]

I believe this is because of how Esri takes feature services offline. When a feature service is taken offline the user is actually taking a version of the data offline and not the base data. Since the db_datareader role can only read and not write, an error is thrown because the account does not have permission to write the replica to take offline. Or if you data is a traditional version then db_datareader cannot create the new tables for the replica.

Esri's way of handling offline/sync has long been a problem for me. I had a similar problem when I wanted to make a feature service published from a SQL Server view table be part of a distributed collaboration with ArcGIS Online. Even though the data the view referenced was setup for offline/sync use, the view's feature service could not be setup for sync because ArcGIS kept trying to create a replica off of the view table and throwing an error.

[FMGLGIS]

Thanks mate, this was good info and lead us down the right path. Resolution in my response to twangtx.

[twangtx]

Did y'all ever end up working through this issue? We're in the same boat where we're trying to have services from Portal make a copy to AGO using a readonly DB user account. It works fine with an owner level account, but we don't want to use such an elevated account, if possible.

We're considering setting up a headless account that has the read and write roles (without the owner role), but we're unsure if that's enough for this purpose. We have to go through our DBA to get it create and that's not a fun or easy process, so I'd like to get it sorted in request to our DBA.

[FMGLGIS · Accepted solution]

Hi, we did end up finding a resolution, in part due to Joshua Youngs response above. ESRI suggest using the built in tooling for db account creation via geoprocessing tools, as this grants the specific required permissions for each role. However as we are too far into our work to create a new account, we opted to enable db_datareader and db_datawriter (not owner) which has resolved the replica creation issues. ESRI said this is a valid approach and will not break the egdb. A long term solution would be to experiment with the db account creation tooling esri has and find the appropriate account type for your use case.

[twangtx]

Thank you!