Problem running cmd file from SQL Agent

Question

Problem running cmd file from SQL Agent

Stueyd

Ten Centuries

Points: 1315
More actions
March 22, 2017 at 8:07 pm

#336251

Hi Gang,
We've having some storage issues and in order to find the root cause i need to have a job triggered when an error occurs that runs a cmd file.
Simple enough via SQL Agent and alerts.
My problem is that the cmd file is using PLINK.exe (command line Putty) to connect to a remote system to run some commands.
The cmd file works no problem from the command line but when I attempt to run it from a SQLAgent job it runs without reporting an error but actually does nothing as it reports in the output file:
"Host does not exist. Unable to open connection"
The format of the command i'm executing is:
PATH\plink.exe [putty profile containing logon credentials, etc] [commands]
As this is a non-production system I temporarily gave the SQL Agent user local admin to see if this was quick fix. It wasn't.
Any suggestions as what is required to get this to work? Remember it works as intended when run from the command prompt.
Many thanks.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Alan Burstein SSC Guru Points: 61136 More actions · Answer 1

The cmd file works no problem from the command line

You are running the file locally or on the remote system? I don't know anything about Putty and, in your example syntax there is no reference to a remote machine.

Host does not exist. Unable to open connection

That's the kind of error you get when:
1. You're putting in the wrong host name
2. You're putting in the correct host name but you're having DNS issues (temporarily using the host's IP address is a good way to test this)
3. You're putting in the correct host name, it's DNS name is being resolved correctly, but a port is not open
4. The SQL Agent account you're using does not have access to a system (sometimes people set the error that way for security: this vs letting hackers know they've found a live system but don't have access).

Also note that, local admin rights doesn't give an account access to a remote system that it doesn't otherwise have access to.

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

Stueyd Ten Centuries Points: 1315 More actions · Answer 2

Hi Alan,

The CMD file works perfectly when run from the local SQL Server, only failing when run through the SQL Agent.
I found the problem. The putty profile I created that contains all the connectivity and logon credentials to the remote server is saved as a registry entry for the user it's created as.
So when it ran under the SQL Agent it was looking for a putty profile that didn't exist in the agents user context.
Just setup a proxy for my logon where the profile was created and now it works ok.

Thanks!