Search code examples
pythonpowershellsql-server-agent

Running Powershell script (that runs other exe file) from SQL Server Agent

I am not sure why this part of Python script does not do the job when I run within SQL Server Agent.

This python script is from "ConvertToBAK.py".

for f in glob.glob(r'Z:\\TestPCC\\Yesterday\\*.SQB'):  
    os.system( f'SQBConverter "{f}" "{f[:-4]}.bak" {password}' )

So, I run Powershell to call this Python file (ConvertToBak.py).

When I run this Powershell script outside of SQL Server Agent, everything works perfectly.

I have following script for PowerShell.

$path = 'Z:\TestPCC'
$file = 'ConvertToBAK.py'

$cmd = $path+"\\"+$file  # This line of code will create the concatenate the path and file
Start-Process $cmd  # This line will execute the cmd

SQBCoverter is a third party (RedGate) app's exe file that basically converts SQB file format to BAK file format.

Using same Python code, the SQL Server Agent completes other tasks (like moving files from here to there) but it just skips/does not do this task.

Is there anything that I need to add to Powershell or would there be any permission issue with SQL Server Agent running a third party/external exe file (SQBConverter)?

BTW, my login for SQL Server Agent is my own account because with other system account, I was not able to perform some of the tasks that were in the Python code.

enter image description here

I also checked the permission issue of this particular exe file, and granted full permission for all users.

Solution

  • From what you are saying, it should be ok, but if want to be sure to launch your script with the appropriate user rights, you can also pass the according credentials directly to the Start-Process Cmdlet:

    #Use parameters to obtain username and password, avoid to hard-code them here.

$credentials = New-Object System.Management.Automation.PSCredential -ArgumentList @($username,(ConvertTo-SecureString -String $password -AsPlainText -Force))


Start-Process $cmd -Credential ($credentials)