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:
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
Several questions come to mind:
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.
>>> 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.
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.
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.