Allow SQL Server to run Python 3

221
2
03-08-2019 07:51 AM
Status: Open
MichaelHerr
New Contributor II

We run Python 2 scripts from SQL Server Management Studio (SSMS) to integrate spatial and non spatial data replication. Tasks like using a SQL query to copy a table from a mainframe extract to SDE then use a geoprocessing tool in python to geocode it.

This can be done currently as a job in SSMS by running as CmdExec to execute the Python geoprocessing tool like this:

c:\Python27\ArcGIS10.2\python.exe \\roc-smb\gis\Scripts\Active\Red_NMX_Replication\GeocodeFireMain.py

However everything is moving to ArcPro and using this same workflow with Python 3 results in an error of  "import arcgisscripting  RuntimeError: Not signed into Portal.  Process Exit Code 1.  The step failed."

This happens even with ArcPro open, a user signed in, and a dedicated stand licence on the machine in question but we are still denied permission to access Python 3.

2 Comments
ThomasColson

I'll explain my down vote, because I hate down voting!

cmdexec is a huge security risk in SQL. Enabling it is like sending donations to ISIS. I can't imagine a possible scenario where cmdexec should be enabled in a production SQL. I'm afraid that ESRI will think this is a great idea, bake something in the next version of SDE that turns it on, and then when we upgrade to SDE version 20.3.99, the alarms go off in the dark IT cave. 

I do a lot of SDE data automation with Python, but I do it with Windows Task Scheduler, and it is working with Python 3 and Pro 2.1. 

JoshuaBixby

The merits of the idea aside, you are likely seeing this error because ArcGIS Pro has never been launched with the same credentials that are running SQL Server.  It doesn't matter if a user is logged into Pro if that user isn't the same as the account running SQL Server.