Problems running Python script from a SQL Server job

[DarinaTchountcheva]

Hello guys,

I have a python script which geocodes data from a SQL Server database. The script uses an OLE DB connection to connect to the data, and the connection uses a SQL Server user  with a password. I have created a SQL Server Agent job to run the script.

When the OLE DB connection points to a database on the same server where the Python script is, the SQL Server job starts the Python script and executes it successfully. No problems here.

When the OLE DB connection points to a database on a different server (not where the Python script is), the job starts the Python script (I have a log file and it shows that the first few statements are executed) and then when it has to connect to the database just hangs and does nothing. No error. Just hangs. If I run the same Python script from the IDLE, it runs successfully.

I believe that the problem comes from the account running the script, which in this case is the account running the SQL Sever job. But I am not sure what I need to do to make this work.

Any idea is greatly appreciated!

Thank you!

Darina

[MathewCoyle · Accepted solution]

Make sure the account you are running it with has domain privileges to connect to another server. And that the target database allows this user to connect.

[DarinaTchountcheva]

Mathew,

Thank you for your reply!

After looking at the log files of the SQL Server where the data is, we figured out that the Python script is trying to login to SQL Server using the SQL Server Agent account of the server where the job is running. And it was failing.

For testing we added that account in the admin group on the server with the data, and added it as a login to SQL Server, but we didn't give it the appropriate permissions. Now, finally Python gave an error saying that the dataset doesn't exist.

Why is it trying to connect using this account, when the .odc file is specifying a different user (sql server user with a password using a SQL Server authentication)?

And now I know that when it is succeeding running through the Python IDLE, it is using my account to connect to the database. And I have admin rights on both servers. That's why it was succeeding.

The question now is: How to set up and use in Python script an .odc connection which uses a SQL Server user account with password?

I did it through ArcCatalog, selected "Allow saving password", provided password, and then copied the .odc file to a folder where the script can access it. But it obviously is using Windows authentication. What am I doing wrong?

[MathewCoyle]

It depends on your environment, but I would think you would need to run the actual script through task scheduler as your domain/server admin account with password, then define the credentials to the database in the script/connection file.

[DarinaTchountcheva]

Mathew,

Thanks for the tips!

I haven't done a lot of Python but if in the Python script the database connections is done with an .odc file configured to use an account with SQL Server authentication, it shouldn't use Windows Authentication with the account running the script.

I am either missing something, or it just doesn't work for some reason.
I will bug ESRI support on this one, and will post back the results. 😉

[MathewCoyle]

The Windows Authentication is required to execute the script. Because of securities you cannot anonymously execute a potentially damaging script/program without authentication. This is supplied automatically when you are logged in as you are already authenticated, but not when the user is logged out and it is executing through a scheduled task.

[RebeccaRothbard]

Hello Darina...I am attempting to do the exact same thing as you described in your initial post PLUS I anticipate having issues with authentication and logging into the SQL server. Would you mind sharing your functioning script with me?

Thank you.

Hello guys,

I have a python script which geocodes data from a SQL Server database. The script uses an OLE DB connection to connect to the data, and the connection uses a SQL Server user  with a password. I have created a SQL Server Agent job to run the script.

When the OLE DB connection points to a database on the same server where the Python script is, the SQL Server job starts the Python script and executes it successfully. No problems here.

When the OLE DB connection points to a database on a different server (not where the Python script is), the job starts the Python script (I have a log file and it shows that the first few statements are executed) and then when it has to connect to the database just hangs and does nothing. No error. Just hangs. If I run the same Python script from the IDLE, it runs successfully.

I believe that the problem comes from the account running the script, which in this case is the account running the SQL Sever job. But I am not sure what I need to do to make this work.

Any idea is greatly appreciated!

Thank you!

Darina