text: ArcSDESQLExecute compatible with OPENQUERY? - Esri Community
  ArcSDESQLExecute compatible with OPENQUERY?
  by PhilLarkin1
  ‎11-27-2018 09:55 AM
  Summary: Running ArcSDESQLExecute fails when using OPENQUERY.
  Error: Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.(18456)
  System: SQL Server 2008r2 (Enterprise Database - "DB1") and 2016 SP2 (Non-Enterprise Database connected with OPENQUERY - "DB2")
  Driver: ODBC Driver 13 for SQL Server
  Python: Pro 2.2.2 instance (3.6.5)
  Connection Files Created with Catalog 10.3.1
  Executing a simple query with ArcSDESQLExecute is successful. When OPENQUERY is used it appears credentials are not passed to the joined server/database. I've tested this with several users that are added as Logins to the Servers and Databases. This query runs properly in SQL Server Management Studio.
  Other SQL Log Errors:
  There is already an object named '##SDE_8868_184399_DB1' in the database.
  Invalid object name 'DB1.dbo.SDE_branches'. (208)
  Statement(s) could not be prepared. (8180)
  Invalid object name 'DB1.PSLARKIN.SDE_logfiles'. (208) <- Will reference my user name regardless of which OS user is set in the SDE connection file.
  SELECT gis_ACO_NUM , PID_NUM FROM
  LTRIM(RTRIM(ACO_NUM)) ACO_NUM
  ,LTRIM(RTRIM(PID_NUM)) PID_NUM
  FROM DB1.DB1SCHEMA.SEGPOINTS
  WHERE ( SUBSTRING(PID_NUM,6,1) = '.' AND LEN(PID_NUM) = 10 ) OR ( SUBSTRING(PID_NUM,6,1) = '.' AND LEN(PID_NUM) = 11 AND SUBSTRING(PID_NUM,11,1) NOT LIKE '%[^a-zA-Z]%' )
  FROM DB1.DB1SCHEMA.SEGHISTORYPOINT
  ) AS segPoints
  --section above the join will run correctly with ArcSDESQLExecute. Section below will not.
  CASE WHEN SUBSTRING(seg_merge_nbr,3,1) = '-' AND SUBSTRING(seg_merge_nbr,1,2) IN ('97','98','99') THEN '19' + REPLACE(seg_merge_nbr,'-','')
  WHEN SUBSTRING(seg_merge_nbr,3,1) = '-' AND SUBSTRING(seg_merge_nbr,1,2) IN ('00') THEN '20' + REPLACE(seg_merge_nbr,'-','')
  ELSE seg_merge_nbr
  END gis_ACO_NUM
  ,parent_parcel
  FROM OPENQUERY(DB2Server,'SELECT seg_merge_nbr,parent_parcel,seg_status_cd FROM DB2Server.DB2.DB2Schema.seg_merge')
  where seg_status_cd = 'DONE'
  ) AS asc_seg
  ON ACO_NUM = gis_ACO_NUM AND PID_NUM = parent_parcel
  WHERE gis_ACO_NUM is not null‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍
  ‎11-27-2018 10:26 AM
  What are your connection properties in the SDE connection file?
  When using the SDE connection file, what does CURRENT_USER and SYSTEM_USER return through ArcSDESQLExecute?
  What are the settings for the security page of the linked server?
  Hi Joshua- Thanks for the reply. Have you used OPENQUERY in this context?
  Connection File Properties: I'm connecting as Operating System User.
  Execute: These attributes are not available. Could you share example syntax. Maybe im not calling them right. They aren't described on the help page.
  Security: SQL Server and Windows Authentication
  I'm going to try a connection file created with Pro.
  ‎11-27-2018 11:11 AM
  >>> sde_conn = # path to SDE connection file
  >>> conn = arcpy.ArcSDESQLExecute(sde_conn)
  >>> conn.execute('SELECT CURRENT_USER')
  u'dbo'
  >>> conn.execute('SELECT SYSTEM_USER')
  u'Domain\\username'
  >>> ‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍
  Regarding security, I am talking specifically about the security settings for the linked server setup in SQL Server.
  SQL Server: How to List the Full Properties of all Linked Servers – Justin Cooney
  Oh, SSMS functions. Right. They are returning my user, and user with domain as prefix (Domain\user).
  Like magic, it is now running properly. I have no idea why. I'll come back to this thread if the problem occurs again.
  ‎11-27-2018 12:34 PM
  Glad it is working for you. I will mark it as "assumed answer" since the question/issue has been resolved for now.
  The error is still occurring intermittently. Please remove the 'Assumed Answered' mark.
  ‎11-27-2018 02:59 PM
  Unfortunately, there isn't an easy way to do that, but we can keep discussing it here anyways.
  So, how is the linked server security set up? And, since it works part of the time, any ideas of what is changing over time.
  I guess Assumed Answered is legit because OPENQUERY does work! I just have no idea why im getting this intermittent error!