CMDexec job hanging in SQL server

Question

Post reply

CMDexec job hanging in SQL server

Liviuc

SSC-Addicted

Points: 410
More actions
June 29, 2010 at 8:09 am

#141695

Hi
I have SQL 2005 and in jobs scheduler I did scheduled on job to run daily. Job is simple , it has one step of type: Operating system (CmdExec) where it call an .exe file. If I run that command in manual mode it runs fine without anny issue ...but when job is scheduled to run or even if I start it manually it will hang forever without any error recoded by SQL server or Windows XP (system op host).
I am not sure if there are any ways to control or set environment to get rid of this issue.
Any help will be appreciated.
Liviu

Viewing 15 posts - 1 through 15 (of 15 total)

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

Elliott Whitlow SSC Guru Points: 102296 More actions · Answer 1

What does this EXE do? Does SQL run as a user or as localsystem (or equivalent).

CEWII

Lowell SSC Guru Points: 323504 More actions · Answer 2

could your application be raising a pop-up/error message window? so when it is run from cmdexec, maybe with a misspelled parameter or something, it seems to be running forever because the app is waiting for someone to click "OK"?

you have to be carefull calling apps from the command line, because there's no interaction allowed.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Liviuc SSC-Addicted Points: 410 More actions · Answer 3

.exe runs and copy some files from one location to another. SQL server runs like local system.

Thank you

Lowell SSC Guru Points: 323504 More actions · Answer 4

details? the account cmdexec/xp_cmdShell runs under is local system , you said, right? that account might not have rights to a local folder(ie C:\Documents and settings\SomeUser) or to a \\UNC path(because only domain\Registered Users have loggedin, so cannot use a UNC), and your applications raises an error.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Liviuc SSC-Addicted Points: 410 More actions · Answer 5

It does have access to the .exe. Issue is that ..it does not rise any error. It is just hanging...

Lowell SSC Guru Points: 323504 More actions · Answer 6

i guess you missed my point; take a look at this thread, where someone had a very similar issue to yours.

http://www.sqlservercentral.com/Forums/Topic944084-392-1.aspx#bm944497

my point is that even though local system can access "c:\temp\temp.exe", the file operations the executable attempts fails due to security(you never said the actual paths , which is critical to the diagnosis) , and your app might be raising an exception window or some other window, which makes the app seem to "hang" from the cmd window.

if the application sis waiting user input(to click an OK button that cannot be accessed), the app never returns and thus there is no error code.

follow the instructions on that thread and change the account being used, and see if that fixes the issue.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Liviuc SSC-Addicted Points: 410 More actions · Answer 7

I start SQL server using Windows auth mode and user has admin rights.

The path c:\temp\tmp.exe is accessible because if I replace this with command:

del c:\tmp\mytest.txt job will delete mytest.txt file. So si proof thet folder of .exe is reachable by SQL server job.

Moreover once job start to run I can found in Task Manager tmp.exe running and having memroy allocated ....around 4 MB......but is hanging ...never goes to the end.

I repeat during running process the .exe is impossible to wait for any other input parameter or to wait for any input user. It doesn't pop up any window plus as i said if I open an cmd windos and run same command it will be executed perfectly.

Thx for your time

Lowell SSC Guru Points: 323504 More actions · Answer 8

it's not access to the exe.

once again, the point i'm making is the exe trying to use the security context nt authority\system for example to access files or file shares.

it has no access to one or more of those paths.

it raises an error that you do not see when running it manually.

try the things i suggested...read the other thread. change the local account to your own. it will work then with the same security as you going to the command prompt.

run this command(from the other thread i directed you to)

for a detailed example:

DECLARE @Results table(

ID int identity(1,1) NOT NULL,

TheOutput varchar(1000))

insert into @Results (TheOutput)

exec master..xp_cmdshell 'whoami' --nt authority\system for example

--change this to the foloders your application was trying to access...NULL means no access

insert into @Results (TheOutput)

exec master..xp_cmdshell 'cd %userprofile%' --NULL because nt authority\system is not a user...command fails.

select * from @Results

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Elliott Whitlow SSC Guru Points: 102296 More actions · Answer 9

I am going to agree with Lowell, start with those suggestions.

I am also thinking his original suggestion is a possibility, if for any reason the exe throws out a question to the user like "Are You Sure(Y/N)?" the process will hang. I reserve judgement on this one.

You also mention that "I start SQL server using Windows auth mode and user has admin rights." I think this missed a major point, that is SQL Security, NOT OS security. Your SQL Server should be logging in as a User, this resolves MANY issues when trying to interact with the local filesystem and remote systems.

I also want to point out that because YOU can run it from the command line with no problem it screams that this is very likely a security issue. SQL does not have ALL the security needed to do what it needs to do.

How about this, change the SQL and SQL Agent login to YOUR account information and see what happens. I am 99% certain that it will work.

CEWII

Liviuc SSC-Addicted Points: 410 More actions · Answer 10

Guys thank you very much for all your input ..maybe I am doing something wrong here...because I can't get it fixed. I changed settings for SQL server and SQL server Agent in Windows Services to use "this account" in log on screen....and account used has admin permissions.

Job still hanging without any error recorded bu SQL server job error log os SQL server error log or Windows error log.

I red that other had similar issue and it was "fixed" by stopping and restarting service ...but in my case that is not working either...

Elliott Whitlow SSC Guru Points: 102296 More actions · Answer 11

You can find out what security context of SQL by doing this:

EXEC master.dbo.xp_cmdshell 'SET'

Near the bottom you should see:

USERDOMAIN=SQLsLoginDomain

USERNAME=SQLsUserName

If you see neither of those then you might not have gotten the user info set correctly.

CEWII

Liviuc SSC-Addicted Points: 410 More actions · Answer 12

Lowell, Elliot thank you very much.

I got it up and running.

Thanks a lot.

Liviu

Elliott Whitlow SSC Guru Points: 102296 More actions · Answer 13

Great, good to hear it, can you tell us what it was?

CEWII

Liviuc SSC-Addicted Points: 410 More actions · Answer 14

Changed SQL server agent to log on as "this account " and SQL Server to use "this account" too.

I restarted both services and it looks ok now.

As per your suggestion .

Thanks again