topic Re: ArcSDESQLExecute compatible with OPENQUERY? in Python Questions

ArcSDESQLExecute compatible with OPENQUERY?

PhilLarkin1 — Sat, 11 Dec 2021 12:28:47 GMT

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

Details:

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.

Query:

SELECT gis_ACO_NUM , PID_NUM FROM 
     (SELECT 
               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]%' )     
          
          UNION

          SELECT
               LTRIM(RTRIM(ACO_NUM)) ACO_NUM
               ,LTRIM(RTRIM(PID_NUM)) PID_NUM
          FROM DB1.DB1SCHEMA.SEGHISTORYPOINT
          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]%' )     
     ) AS segPoints
     --section above the join will run correctly with ArcSDESQLExecute. Section below will not.
     LEFT JOIN 
               (SELECT DISTINCT
                         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‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Re: ArcSDESQLExecute compatible with OPENQUERY?

JoshuaBixby — Tue, 27 Nov 2018 18:26:37 GMT

Several questions come to mind:

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?

Re: ArcSDESQLExecute compatible with OPENQUERY?

JoshuaBixby — Tue, 27 Nov 2018 18:26:57 GMT

Sharing with Geodatabase‌

Re: ArcSDESQLExecute compatible with OPENQUERY?

PhilLarkin1 — Tue, 27 Nov 2018 19:06:06 GMT

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.

Re: ArcSDESQLExecute compatible with OPENQUERY?

JoshuaBixby — Sat, 11 Dec 2021 12:28:50 GMT

>>> 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

Re: ArcSDESQLExecute compatible with OPENQUERY?

PhilLarkin1 — Tue, 27 Nov 2018 19:51:56 GMT

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.

Thanks for your help.

Re: ArcSDESQLExecute compatible with OPENQUERY?

JoshuaBixby — Tue, 27 Nov 2018 20:34:00 GMT

Glad it is working for you. I will mark it as "assumed answer" since the question/issue has been resolved for now.

Re: ArcSDESQLExecute compatible with OPENQUERY?

PhilLarkin1 — Tue, 27 Nov 2018 20:50:53 GMT

The error is still occurring intermittently. Please remove the 'Assumed Answered' mark.

Re: ArcSDESQLExecute compatible with OPENQUERY?

JoshuaBixby — Tue, 27 Nov 2018 22:59:34 GMT

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.

Re: ArcSDESQLExecute compatible with OPENQUERY?

PhilLarkin1 — Tue, 27 Nov 2018 23:59:28 GMT

I guess Assumed Answered is legit because OPENQUERY does work! I just have no idea why im getting this intermittent error!