Allow SQL Server to run Python 3

[MichaelHerr]

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.

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

[KellyAlfaro_Haugen]

I am having a similar issue but I get a different message "RuntimeError: The Product License has not been initialized." 

**We use SQL Agent Jobs and not Scheduled Tasks because we are not just running python. We have multiple tasks running that include TSQL, PowerShell, .NET, etc all in a precise order that would be a nightmare to try and control, and verify completion, outside of SQL.**

Doing some research on what other users have done I found that the issue was I needed to log into the server as the "Service Account" being used by SQL Server and "Open ArcPro" as that user to create the appropriate Local/Roaming accounts on the Server for that "User" account. The only problem is we are using gMSA's (group managed service accounts) to run these Jobs. So there is no physical way to log onto the server as that account. 

As I could not log into the server as the gMSA but could see a user folder, with Esri AppData folders, I got to thinking maybe there was a way I could hack it. I wanted to force ArcPro to grab a Concurrent User license (I know we'll have to change this when Named User licensing is forced upon us all) and  default to the correct settings. My logic was that by setting this up to default to the correct type of account (in our case Concurrent use on a License Server) I could circumvent the problem it was having trying to connect to the product license.  

Diving a little deeper I found that I could alter the Registry Key (regedit) for HKEY_LOCAL_MACHINE\SOFTWARE\ESRI\ArcGISPro\Licensing using the correctly set-up registry Key for my admin account HKEY_CURRENT_USER\SOFTWARE\ESRI\ArcGISPro\Licensing to create a default setting that would force all users to connect to the Concurrent License Server. 

I then updated the HKEY_USERS for all users that had a Software\ESRI\ArcGISPro\Licensing folder by manually entering the correct "Data" values for the Floating License Server. (There are instructions available on how to delete these but I was being abundantly cautions). 

After making the changes to the Registry Keys I tried running my SQL Agent Jobs again, with the gMSA user, and everything ran correctly. It appears that the problem is solved. 

I am going out on a limb here but I believe that once we do change to Name User Accounts I may be able to switch the registry key to connect users to the appropriate portal using user name. gMSAs are in AD so I should be able to configure that user in the Portal and it should be able to connect and pull a license to run the SQL Agent Jobs. I will report back if you are interested.