ArcGIS Server - Unable to publish map services - SQL group listener for database

[ChrisSmith7]

Hey guys - need some help!

I'm having an issue where ArcGIS Server will not publish map services sourced to an MS SQL group listener alias. I can publish the same service when sourced directly to the SQL cluster - same data, same process (x,y event layer), except one publishes without issue, and the other not.

I have tried publishing the service using the ArcMap GUI. I've tried saving a service definition draft and staging/uploading using Server Tools. I've tried publishing from within Manager - nothing works when using the group listener. I've also tried manually setting APPLICATIONINTENT and MULTISUBNETFAILOVER as per Connections to highly available SQL Server databases—Help | ArcGIS Desktop. I've tried different naming conventions, not immediately starting the service, using different folders, creating new ones, registering the group listener db using different naming conventions, re-applying SQL user perms... nothing works.

I can connect to the instance in ArcCatalog and manipulate/geoprocess in ArcMap without issue... it's the publishing that almost immediately fails:

As per recommendation, I've added perms to every directory I've run across related to ArcGIS/Esri - I have no problems publishing other services, just ones related to the group listener.

I noticed others have had issues connecting - Group listener for Database is not connecting  - again, I have no issues from connecting from within ArcMap - just with publishing.

I've spun down all services, restarted Publishing Tools, recycled the ArcGIS Server Windows Services process, restarted the server, restarted the Web Adaptor server, adjusted min/max processes in Manager for each service... nothing works. 

Here's some server logs that may help from around the time processing failed... I bolded some of the things that looked interesting and found some stuff on Geonet, but nothing conclusive. Keep in mind, this is from multiple attempts back-to-back... I think I was loading from manager at the time and tried to publish a few times after failing:

SEVERE Apr 3, 2018, 9:49:18 AM Error executing tool. Publish Service Definition Job ID: j8bbd31a22a804aa39ea8f9d769a0e30c System/PublishingTools.GPServer
SEVERE Apr 3, 2018, 9:49:18 AM Error executing tool. Publish Service Definition Job ID: j8bbd31a22a804aa39ea8f9d769a0e30c : ERROR 001360: Failed to get basic item metadata for service definition upload. ERROR 001362: Failed to extract manifest from uploaded service definition. Failed to execute (Publish Service Definition). System/PublishingTools.GPServer
SEVERE Apr 3, 2018, 9:49:18 AM Failed to extract manifest from uploaded service definition. System/PublishingTools.GPServer
SEVERE Apr 3, 2018, 9:49:18 AM Failed to get basic item metadata for service definition upload.: ERROR: code:404, Could not find resource or operation 'i6d8d0dff-1297-4b6f-a71c-4c90a7463298' on the system. No resource could be found at that address. System/PublishingTools.GPServer
SEVERE Apr 3, 2018, 9:49:14 AM Error executing tool. Publish Service Definition Job ID: je029c3ad0688414592aff0969b65a73v System/PublishingTools.GPServer
SEVERE Apr 3, 2018, 9:49:14 AM Error executing tool. Publish Service Definition Job ID: je029c3ad0688414592aff0969b65a73v : ERROR 001360: Failed to get basic item metadata for service definition upload. ERROR 001362: Failed to extract manifest from uploaded service definition. Failed to execute (Publish Service Definition). System/PublishingTools.GPServer
SEVERE Apr 3, 2018, 9:49:14 AM Failed to extract manifest from uploaded service definition. System/PublishingTools.GPServer
SEVERE Apr 3, 2018, 9:49:14 AM Failed to get basic item metadata for service definition upload.: ERROR: code:404, Could not find resource or operation 'i6d8d0dff-1297-4b6f-a71c-4c90a7463298' on the system. No resource could be found at that address. System/PublishingTools.GPServer
SEVERE Apr 3, 2018, 9:49:11 AM Error executing tool. Publish Service Definition Job ID: j655e3bccf3ee497f876nn76c7c4b4f5b System/PublishingTools.GPServer
SEVERE Apr 3, 2018, 9:49:11 AM Error executing tool. Publish Service Definition Job ID: j655e3bccf3ee497f876nn76c7c4b4f5b : ERROR 001360: Failed to get basic item metadata for service definition upload. ERROR 001362: Failed to extract manifest from uploaded service definition. Failed to execute (Publish Service Definition). System/PublishingTools.GPServer
SEVERE Apr 3, 2018, 9:49:11 AM Failed to extract manifest from uploaded service definition. System/PublishingTools.GPServer
SEVERE Apr 3, 2018, 9:49:11 AM Failed to get basic item metadata for service definition upload.: ERROR: code:404, Could not find resource or operation 'i6d8d0dff-1297-4b6f-a71c-4c90a7463298' on the system. No resource could be found at that address. System/PublishingTools.GPServer
SEVERE Apr 3, 2018, 9:49:07 AM Error executing tool. Publish Service Definition Job ID: jfa1c4220fb794068be1yu7b29713aa55 System/PublishingTools.GPServer
SEVERE Apr 3, 2018, 9:49:07 AM Error executing tool. Publish Service Definition Job ID: jfa1c4220fb794068be1yu7b29713aa55 : 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). System/PublishingTools.GPServer
SEVERE Apr 3, 2018, 9:49:07 AM Failed to create the service.: Updating the server connection string for layer MY_MAP_DB.MAPUSER_MY_MAP_DB.%tbl_map_AGV_1 failed. Attempted connection string was ENCRYPTED_PASSWORD=MY_ENCRYPTED_PASSWORD;SERVER=AGXXXXXXX;INSTANCE="DSID=e851aec4-00c2-4795-87e6-n6f2652552b7";DBCLIENT=sqlserver;DB_CONNECTION_PROPERTIES=AGXXXXXXX,1031;DATABASE=MY_MAP_DB;USER=MapUser_MY_MAP_DB;AUTHENTICATION_MODE=DBMS. Table name is MY_MAP_DB.MAPUSER_MY_MAP_DB.%tbl_map_1. Please verify the data exists on the server. System/PublishingTools.GPServer
SEVERE Apr 3, 2018, 9:49:07 AM DoAfterSDExtraction failed. System/PublishingTools.GPServer
SEVERE Apr 3, 2018, 9:49:07 AM SwizzleService failed. System/PublishingTools.GPServer
SEVERE Apr 3, 2018, 9:48:42 AM Error executing tool. Publish Service Definition Job ID: j16h4ce5d483946b29e28e20j2360c3d7 System/PublishingTools.GPServer
SEVERE Apr 3, 2018, 9:48:42 AM Error executing tool. Publish Service Definition Job ID: j16h4ce5d483946b29e28e20j2360c3d7 : ERROR 001360: Failed to get basic item metadata for service definition upload. ERROR 001362: Failed to extract manifest from uploaded service definition. Failed to execute (Publish Service Definition). System/PublishingTools.GPServer
SEVERE Apr 3, 2018, 9:48:42 AM Failed to extract manifest from uploaded service definition. System/PublishingTools.GPServer
SEVERE Apr 3, 2018, 9:48:42 AM Failed to get basic item metadata for service definition upload.: ERROR: code:404, Could not find resource or operation 'i8baadf8d-6f3f-44b3-8de9-814e1b6b9315' on the system. No resource could be found at that address. System/PublishingTools.GPServer
SEVERE Apr 3, 2018, 9:48:38 AM Error executing tool. Publish Service Definition Job ID: ja20l40af8ee489fe9146479e8153a213 System/PublishingTools.GPServer
SEVERE Apr 3, 2018, 9:48:38 AM Error executing tool. Publish Service Definition Job ID: ja20l40af8ee489fe9146479e8153a213 : 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). System/PublishingTools.GPServer
SEVERE Apr 3, 2018, 9:48:38 AM Failed to create the service.: Updating the server connection string for layer MY_MAP_DB.MAPUSER_MY_MAP_DB.%tbl_map_AGV_1 failed. Attempted connection string was ENCRYPTED_PASSWORD=MY_ENCRYPTED_PASSWORD;SERVER=AGXXXXXXX;INSTANCE="DSID=e851aec4-00c2-4795-87e6-n6f2652552b7";DBCLIENT=sqlserver;DB_CONNECTION_PROPERTIES=AGXXXXXXX,1031;DATABASE=MY_MAP_DB;USER=MapUser_MY_MAP_DB;AUTHENTICATION_MODE=DBMS. Table name is MY_MAP_DB.MAPUSER_MY_MAP_DB.%tbl_map_1. Please verify the data exists on the server. System/PublishingTools.GPServer
SEVERE Apr 3, 2018, 9:48:38 AM DoAfterSDExtraction failed. System/PublishingTools.GPServer
SEVERE Apr 3, 2018, 9:48:38 AM SwizzleService failed. System/PublishingTools.GPServer
SEVERE Apr 3, 2018, 9:47:48 AM Error executing tool. Publish Service Definition Job ID: jd42c6ele01384ffca5089facv0e0fd01 System/PublishingTools.GPServer
SEVERE Apr 3, 2018, 9:47:48 AM Error executing tool. Publish Service Definition Job ID: jd42c6ele01384ffca5089facv0e0fd01 : 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). System/PublishingTools.GPServer
SEVERE Apr 3, 2018, 9:47:48 AM Failed to create the service.: Updating the server connection string for layer MY_MAP_DB.MAPUSER_MY_MAP_DB.%tbl_map_AGV_1 failed. Attempted connection string was ENCRYPTED_PASSWORD=MY_ENCRYPTED_PASSWORD;SERVER=AGXXXXXXX;INSTANCE="DSID=e851aec4-00c2-4795-87e6-n6f2652552b7";DBCLIENT=sqlserver;DB_CONNECTION_PROPERTIES=AGXXXXXXX,1031;DATABASE=MY_MAP_DB;USER=MapUser_MY_MAP_DB;AUTHENTICATION_MODE=DBMS. Table name is MY_MAP_DB.MAPUSER_MY_MAP_DB.%tbl_map_1. Please verify the data exists on the server. System/PublishingTools.GPServer
SEVERE Apr 3, 2018, 9:47:48 AM DoAfterSDExtraction failed. System/PublishingTools.GPServer
SEVERE Apr 3, 2018, 9:47:48 AM SwizzleService failed. System/PublishingTools.GPServer
WARNING Apr 3, 2018, 9:47:34 AM Unable to process request. json Rest
WARNING Apr 3, 2018, 9:47:20 AM Unable to process request. json Rest
SEVERE Apr 3, 2018, 9:47:06 AM Error executing tool. PublishServiceDefinition Job ID: j72e65h16b9c2402e910sep8cfc6c152n System/PublishingTools.GPServer
SEVERE Apr 3, 2018, 9:47:06 AM Error executing tool. PublishServiceDefinition Job ID: j72e65h16b9c2402e910sep8cfc6c152n : 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). System/PublishingTools.GPServer
SEVERE Apr 3, 2018, 9:47:06 AM Failed to create the service.: Updating the server connection string for layer MY_MAP_DB.MAPUSER_MY_MAP_DB.%tbl_map_AGV_1 failed. Attempted connection string was ENCRYPTED_PASSWORD=MY_ENCRYPTED_PASSWORD;SERVER=AGXXXXXXX;INSTANCE="DSID=e851aec4-00c2-4795-87e6-n6f2652552b7";DBCLIENT=sqlserver;DB_CONNECTION_PROPERTIES=AGXXXXXXX,1031;DATABASE=MY_MAP_DB;USER=MapUser_MY_MAP_DB;AUTHENTICATION_MODE=DBMS. Table name is MY_MAP_DB.MAPUSER_MY_MAP_DB.%tbl_map_1. Please verify the data exists on the server. System/PublishingTools.GPServer
SEVERE Apr 3, 2018, 9:47:06 AM DoAfterSDExtraction failed. System/PublishingTools.GPServer
SEVERE Apr 3, 2018, 9:47:06 AM SwizzleService failed. System/PublishingTools.GPServer
WARNING Apr 3, 2018, 9:43:58 AM Unable to process request. json Rest
WARNING Apr 3, 2018, 9:42:43 AM Unable to process request. json Rest
WARNING Apr 3, 2018, 9:41:09 AM Unable to process request. json Rest
WARNING Apr 3, 2018, 9:39:52 AM Unable to process request. json Rest
WARNING Apr 3, 2018, 9:39:24 AM Unable to process request. json Rest
WARNING Apr 3, 2018, 9:36:08 AM Unable to process request. json Rest
SEVERE Apr 3, 2018, 9:29:14 AM Error executing tool. PublishServiceDefinition Job ID: jc5be0a81lb174eca8e2820fx0eda233k System/PublishingTools.GPServer
SEVERE Apr 3, 2018, 9:29:14 AM Error executing tool. PublishServiceDefinition Job ID: jc5be0a81lb174eca8e2820fx0eda233k : 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). System/PublishingTools.GPServer
SEVERE Apr 3, 2018, 9:29:14 AM Failed to create the service.: Updating the server connection string for layer MY_MAP_DB.MAPUSER_MY_MAP_DB.%tbl_map_AGV_1 failed. Attempted connection string was ENCRYPTED_PASSWORD=MY_ENCRYPTED_PASSWORD;SERVER=AGXXXXXXX;INSTANCE="DSID=e851aec4-00c2-4795-87e6-n6f2652552b7";DBCLIENT=sqlserver;DB_CONNECTION_PROPERTIES=AGXXXXXXX,1031;DATABASE=MY_MAP_DB;USER=MapUser_MY_MAP_DB;AUTHENTICATION_MODE=DBMS. Table name is MY_MAP_DB.MAPUSER_MY_MAP_DB.%tbl_map_1. Please verify the data exists on the server. System/PublishingTools.GPServer
SEVERE Apr 3, 2018, 9:29:14 AM DoAfterSDExtraction failed. System/PublishingTools.GPServer
SEVERE Apr 3, 2018, 9:29:14 AM SwizzleService failed. System/PublishingTools.GPServer

Also, the GP error output:

Executing: UploadServiceDefinition "C:\Users\me\AppData\Local\ESRI\Desktop10.6\Staging\arcgis on gisserver_6443 (admin)\TEST_SERVICE_3.sd" "C:\Users\me\AppData\Roaming\Esri\Desktop10.6\ArcCatalog\arcgis on gisserver_6443 (admin)" # # FROM_SERVICE_DEFINITION # STARTED USE_DEFINITION NO_SHARE_ONLINE PRIVATE NO_SHARE_ORGANIZATION #
Start Time: Tue Apr 3 09:47:04 2018
Submitted.
Executing...
Failed.
ERROR: the server's publishing job didn't succeed.
Failed to execute (UploadServiceDefinition).
Failed at Tue Apr 3 09:47:06 2018 (Elapsed Time: 2.29 seconds)

And, some info on my set-up:

• ArcGIS Server 10.6
• ArcGIS Server Web Adaptor 10.6
• Windows Server 2012 r2
• MS SQL Server 2012 r2
• 64 gb memory (usually, about 30 free)
• CPU cycles look good

Ever since upgrading to 10.6, the stability I enjoyed with 10.3 has gone down the tube - it's very frustrating... from McAfee conflicts to difficulties in publishing and more! I hope Esri patches this, soon! As I understand, some very deep things under the hood changed in 10.6; maybe it's rearing its ugly head now.

[ChrisSmith7]

Oh, something I forgot to add - I tried publishing using fully a qualified domain as well:

• arcgis on gis001_6443 (admin)
• arcgis on gis002.mydummydomain.com (admin)

George Thompson, any ideas? Adding - Enterprise Architect

[George_Thompson]

I have not tested at 10.6 and am unsure. It looks like in the logs that the PublishingGP Service did not restart? Is that accurate?

[ChrisSmith7]

I was able to recycle the PublishingGP service - it's up and running, and I've confirmed by deploying a couple of small, non-SQL services (pushing data directly to the server).

[JonathanQuinn]

I wonder if the % sign in the table name MY_MAP_DB.MAPUSER_MY_MAP_DB.%tbl_map_AGV_1 is a problem:

Failed to create the service.: Updating the server connection string for layer MY_MAP_DB.MAPUSER_MY_MAP_DB.%tbl_map_AGV_1 failed. Attempted connection string was ENCRYPTED_PASSWORD=MY_ENCRYPTED_PASSWORD;SERVER=AG31XXXXX;INSTANCE="DSID=e851aec4-00c2-4795-87e6-n6f2652552b7";DBCLIENT=sqlserver;DB_CONNECTION_PROPERTIES=AG31XXXXX,1031;DATABASE=MY_MAP_DB;USER=MapUser_MY_MAP_DB;AUTHENTICATION_MODE=DBMS. Table name is MY_MAP_DB.MAPUSER_MY_MAP_DB.%tbl_map_1. Please verify the data exists on the server. System/PublishingTools.GPServer

Does it work if it isn't an XY Event layer?

[ChrisSmith7]

Those were added by ArcGIS when dragging over from ArcCatalog. Adding within ArcMap, the % sign is not present, but the issue still occurs. I can still publish hitting the SQL cluster directly with % signs as well - that doesn't appear to be the issue.

What are your recommendation for trying something other than an XY Event Layer? I tried as a query layer, but, while it completes successfully, ArcMap doesn't want to add the object to the TOC, so I am unable to attempt a service creation. Unfortunately, I must use XY event layer as certain architectural decisions have been made precluding the usage of native spatial datatypes in SQL (for now).

[ChrisSmith7]

I found another thread with similar issues, particularly with XY event layers:

Unable to publish map service with data in enterprise database using Server 10.4 

And some patches for earlier versions (I'm using 10.6, however):

BUG-000096292: When the database view is joined to a definition que.. 

ArcGIS Server 10.4.1 Publishing Patch 2 

ArcGIS for Server Publishing Patch for 10.4.0 & 10.4.1  

Seems this has been an issue before... I have an open ticket with Esri and will communicate any resolutions with the group. Since some fundamentals have changed under the hood in 10.6, I'm wondering if this fix was missed porting to the new version. It's a theory, at least!

Also, for those who are unaware, here's the Microsoft write-up on group listeners:

Availability Group Listeners

You can provide client connectivity to the database of a given availability group by creating an availability group listener. An availability group listener is a virtual network name (VNN) to which clients can connect in order to access a database in a primary or secondary replica of an Always On availability group. An availability group listener enables a client to connect to an availability replica without knowing the name of the physical instance of SQL Server to which the client is connecting. The client connection string does not need to be modified to connect to the current location of the current primary replica.

An availability group listener consists of a Domain Name System (DNS) listener name, listener port designation, and one or more IP addresses. Only the TCP protocol is supported by availability group listener. The DNS name of the listener must also be unique in the domain and in NetBIOS. When you create a new availability group listener it becomes a resource in a cluster with an associated virtual network name (VNN), virtual IP (VIP), and availability group dependency. A client uses DNS to resolve the VNN into multiple IP addresses and then tries to connect to each address, until a connection request succeeds or until the connection requests time out.

If read-only routing is configured for one or more readable secondary replicas, read-intent client connections to the primary replica are redirected to a readable secondary replica. Also, if the primary replica goes offline on one instance of SQL Server, and a new primary replica comes online on another instance of SQL Server, the availability group listener enables clients to connect to the new primary replica.

For essential information about availability group listeners, see Create or Configure an Availability Group Listener (SQL Server).

[ChrisSmith7 · Accepted solution]

So, after a bit of troubleshooting with Esri, we found a couple of issues at play:

1) Publishing tool Ex was not started

2) The entry into the dsconnections.lst file was not present for the database registered with the listener name

Regarding Publishing Ex, I believe ArcGIS Server timed-out on recycling this service, which I did not catch, in my own troubleshooting - but it did compound efforts to resolve before realizing it was spooled-down.

The issue with dsconnections.lst is a little more nebulous. After upgrading from 10.3 to 10.6, a quirk must have manifested resulting in database registrations not fully "sticking" e.g. a connection string was added in one dsconnections list (there are two) but not the other, or double adding in one, causing "already registered" messages.

The Esri rep and I ended-up manually deleting some entries in dsconnections.lst as a form of recovery, and after recycling the ArcGIS Server windows service, it seems database registering began working again, thus allowing the publishing of services again.

I do not believe the available group listeners had anything to do with it, it just happened to be coincidental. For anyone experiencing this issue, I would recommend de-registering your databases in Manager, recycling the ArcGIS Server windows service, recycling the Publishing and Publishing Ex map services, and then re-registering your db connections. This *should* help!