topic Re: Publishing Services from direct connection to SQL Server 2014 in ArcGIS Enterprise Questions

Publishing Services from direct connection to SQL Server 2014

Anonymous User — Thu, 08 Sep 2016 18:07:04 GMT

I have data loaded into a SQL Server 2014. I have validated the connection to the data source in ArcGIS Server 10.4.1.

I can add geographic data to an ArcMap Client and publish to Server.

When trying to view the data from the rest service page the geometry is not displayed and I receive the following error in the Server logs.

There is an error during the draw WISE_Decade_CBAS.DBO.deleteme (1.) DBMS table not found[42S02:[Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Invalid object name 'WISE_Decade_CBAS,(2.) WISLEG\SA-DEV-ARCGIS,(3.) deleteme',(4.) ].

We do not seem to have this same problem in our 10.3.1 environment. I do see the geometry in ArcMap!

I am also not seeing the same behavior in our SDE based environment.

Re: Publishing Services from direct connection to SQL Server 2014

JakeSkinner — Thu, 08 Sep 2016 18:23:42 GMT

Hi Tony,

I would guess that this is caused by some sort of permissions issue with the ArcGIS Server Account. Does the ArcGIS Server Account have read privileges to the data in your enterprise geodatabase?

I'm assuming data from a File Geodatabase publishes correctly, and you can view the data?

Re: Publishing Services from direct connection to SQL Server 2014

Anonymous User — Thu, 08 Sep 2016 18:32:40 GMT

yes, triple checked permissions with DB admin. This is what he sees when the account connects.

"Login succeeded for user 'WISLEG\sa-dev-arcgis'. Connection made using Windows authentication."

Re: Publishing Services from direct connection to SQL Server 2014

JakeSkinner — Thu, 08 Sep 2016 18:37:16 GMT

Does the WISLEG\sa-dev-arcgis user also have privileges to the data? For example, did you right-click on the feature class > Privileges and grant read access to this user?

Re: Publishing Services from direct connection to SQL Server 2014

Anonymous User — Thu, 08 Sep 2016 19:24:37 GMT

In Catalog?...does this need to happen? We have never done this before and we were able to publish without any issue.

Re: Publishing Services from direct connection to SQL Server 2014

JakeSkinner — Thu, 08 Sep 2016 19:47:11 GMT

If you are making a connection to your enterprise geodatabase using a Windows Authentication user, then the ArcGIS Server Account will need read privileges to the data to publish successfully. If you are connecting using Database Authentication, then the user will not.

Re: Publishing Services from direct connection to SQL Server 2014

Anonymous User — Thu, 08 Sep 2016 20:02:23 GMT

yes, checked this as well with the db admin. the account has read access to the whole database...rrrrrrrrrrrrr

Re: Publishing Services from direct connection to SQL Server 2014

Anonymous User — Sat, 10 Sep 2016 05:09:16 GMT

Hello,

Are you able to validate database connection from server side using same connection from ArcGIS server manager,

Please check like below

Re: Publishing Services from direct connection to SQL Server 2014

JoshuaBixby — Sat, 10 Sep 2016 15:14:27 GMT

Is your ArcGIS for Server 10.3.1 environment using the same SQL Server instance or a different, newer SQL Server instance? Also, have you installed the ArcGIS for Server Publishing Patch? On the surface, the patch doesn't seem directly related to your issue, but installing the patch does improve publishing performance overall with 10.4/10.4.1.

Re: Publishing Services from direct connection to SQL Server 2014

Anonymous User — Mon, 12 Sep 2016 14:16:23 GMT

Yes. We can do this!

We found the only way to publish is to run ArcMap as the service account (wisleg\sa-dev-arcgis) and publish. This is our development environment so permissions are not an issue, my operator and user account are db admins. This is just strange.

Re: Publishing Services from direct connection to SQL Server 2014

Anonymous User — Mon, 12 Sep 2016 14:17:05 GMT

Yes, the patch has been installed and we are using the same dev instance of SQL.

Re: Publishing Services from direct connection to SQL Server 2014

ScottFierro2 — Wed, 14 Sep 2016 14:27:31 GMT

So probably a wasted point but didn't notice it anywhere in comments yet. Have you registered the SDE connection with the Server Data Stores?

Was trying to follow the usernames above but we have seen interesting headaches before on new installations. During the initial set-up of the new 10.4.1 SDE in SQL the account that you use IS the owner of the database. However, it's been our experience that this account is best left for that sole purpose. Any remaining accounts are assigned permissions via SQL that are set AS dbo. Trying to avoid getting to far into the weeds but I'd suggest creating a new schema within SQL and assign the Windows account access to the schema as appropriate. This may help better explain the scenario when reading the dbo specific info but essentially its tied to the users default schema along with other SQL side granted permissions https://msdn.microsoft.com/en-us/library/bb669061(v=vs.110).aspx

Last item, the above addresses the issues with being able to ensure the account can properly execute any of the functions within the database (i.e. ESRI stored procedures).

Re: Publishing Services from direct connection to SQL Server 2014

YOAVRAPPAPORT1 — Thu, 15 Sep 2016 15:22:05 GMT

I also have SQL Views (tables) and I would like to "join" these to features (both poly and points) in a Geodatabase using a unique Identifier, and then publish to ArcGIS Server. The problem is that the SQL Views are updated daily. Is there a way to automate this process and update the REST on a regular basis (one a day for example). This is similar to some posts i've seen by jskinner-esristaff‌ any leads are much appreciated.

Re: Publishing Services from direct connection to SQL Server 2014

MattNicol — Mon, 27 Feb 2017 21:27:56 GMT

I just ran into this in 10.5. I put more detailed instructions here:

https://community.esri.com/thread/189576-there-is-an-error-during-the-draw-layer-name1-dbms-table-not-found42s02microsoftodbc-driver-11-for-sql-serversql-serverinvalid-object-name-db-name2-service-account3-viewtable4

To summarize though, remove the database name from the query in your layer in ArcMap.

Layer Properties > Source > Change Query

In my case, the query was: select <columns> from <database>.<schema>.<table>

Removing the <database> qualifier resolved it.

Re: Publishing Services from direct connection to SQL Server 2014

ChristopherJohnson1 — Mon, 23 Oct 2017 12:57:10 GMT

Thanks for this post, Matt!