I was having the same issue. My scenario:
- ArcGIS Server 10.4.1 (without the noted patch applied), running as an arcgis user that is a domain account
- SQL Server 2014
- MXD with a single layer that references the geodatabase with a domain account
- The arcgis domain account was added to the geodatabase using ArcCatalog: Administration > Add User
- Publishing a simple map service, i.e. no feature service, KML, etc.
I got the following error in ArcMap:
001487 : Failed to update the published service with the server-side data location. Please see the server's log for more details
and the following 3 errors in the AGS logs, all with a 'Source' of System/PublishingTools.GPServer:
1) Failed swizzling the service
2) Failed to create the service.: Updating the server connection string for layer DB_NAME.SCHEMA_NAME.LAYER_NAME failed. Attempted connection string was SERVER=SQL_SVR_HOST_NAME;INSTANCE="DSID=deba8037-3eb1-48c8-ae85-5af6cab4ac1b";DBCLIENT=sqlserver;DB_CONNECTION_PROPERTIES=SQL_SVR_INSTANCE_NAME;DATABASE=DB_NAME;VERSION=sde.DEFAULT;AUTHENTICATION_MODE=OSA. Table name is DB_NAME.SCHEMA_NAME.LAYER_NAME. Please verify the data exists on the server.
3) Error executing tool. PublishServiceDefinition Job ID: j5fc1408de51349e3b665bab437ddd069 : ERROR 001487: Failed to update the published service with the server-side data location. Please see the server's log for more details. ERROR 001369: Failed to create the service. Failed to execute (Publish Service Definition).
I was able to publish to other geodatabases in the same instance. I did a SQL Profiler trace on the failed publish and found the following error:
The EXECUTE permission was denied on the object 'SDE_get_primary_oid', database 'DB_NAME', schema 'sde'.
When I compared the SQL Server permissions on that stored procedure, I found that the 'public' db role had no permissions at all in the database where the publish was failing, and 'execute' permissions in the DB where it was succeeding.
Perms on sde.SDE_get_primary_oid in database where publish was failing:
Perms on sde.SDE_get_primary_oid in database where publish was succeeding:
A cursory glance at other stored procedures in the sde schema showed that the SQL 'public' role had execute permissions on all of them, even in the database where the publish was failing. For some reason, the required permissions were not granted to SDE_get_primary_oid only. This was unexpected since in both databases I added the same domain user account using the Create Database User tool.
The solution was simply to add execute permissions for the SQL 'public' role on the sde.SDE_get_primary_oid stored procedure. And, of course, to swizzle my bad self.
