December 3, 2009 at 10:24 am
I have a job of type "Operating System (CmdExec)" that resides on a different server than where SQL Server Agent resides. The agent runs as sqladmin, and the other server has "Full Control" setup for the sqladmin account.
When my job runs, it fails everytime saying:
Executed as user: TESTDMZ\SQLAdmin. The process could not be created for step 1 of job 0x254438AE9E0C594EB1A265FCB63F649B (reason: Access is denied). The step failed.
What am I missing here?
December 3, 2009 at 11:48 am
So you have SQL and SQL Agent on server A and you call an OS command on Server B and you get an Access Denied.
Ok, how are you calling it on B? Is it just a file there? If you are trying to execute it ON Server B what method are you trying to use? Does the user that SQL Agent login to windows as have access to the location on B.
It seems like we need to know a lot more information to tell..
CEWII
December 3, 2009 at 12:39 pm
I'm not sure if I follow yours completely....but I'll try again.
I have two servers, ServerA, and ServerB.
ServerA holds SQL Server Agent
ServerB holds asp.net application code
The job on ServerA calls a bat file that resides on ServerB.
The bat file does a "nlb stop", then changes the database in the web.config connection string, then does an "nlb start".
I just got done talking with a Server/Active Directory guy here at my work, and we found out that 1) Sql Server Agent wasn't able to call/execute a program on a different server (I would think this should be possible) and 2) If it were able to call/execute that job on ServerB, it will run the job from ServerA. That would then cause the bat file to try and do my "nlb stop" on ServerA which is the SQL Server, which is not load balanced, so it wouldn't work anyway.
Thanks for trying to help, but I'm going to have to try and write an "agent" service to run on ServerB, then I'll get back to this question.
December 3, 2009 at 12:41 pm
Elliott W (12/3/2009)
So you have SQL and SQL Agent on server A and you call an OS command on Server B and you get an Access Denied.Ok, how are you calling it on B? Is it just a file there? If you are trying to execute it ON Server B what method are you trying to use? Does the user that SQL Agent login to windows as have access to the location on B.
It seems like we need to know a lot more information to tell..
CEWII
To answer your question, the agent runs as "SqlAdmin" on ServerA, and the bat file on ServerB was given full control to SqlAdmin user.
December 3, 2009 at 1:24 pm
gregory.anderson (12/3/2009)
I'm not sure if I follow yours completely....but I'll try again.I have two servers, ServerA, and ServerB.
ServerA holds SQL Server Agent
ServerB holds asp.net application code
The job on ServerA calls a bat file that resides on ServerB.
The bat file does a "nlb stop", then changes the database in the web.config connection string, then does an "nlb start".
I just got done talking with a Server/Active Directory guy here at my work, and we found out that 1) Sql Server Agent wasn't able to call/execute a program on a different server (I would think this should be possible) and 2) If it were able to call/execute that job on ServerB, it will run the job from ServerA. That would then cause the bat file to try and do my "nlb stop" on ServerA which is the SQL Server, which is not load balanced, so it wouldn't work anyway.
Ok, is SqlAdmin a domain user or a local user? If it is a domain user then we'll probably have to look further. Is TestDMZ a domain or a workgroup?
As far as the other stuff:
It can call a script FROM another server but that is not the same as running it there. You *might* be able to accomplish that with PsExec from sysinternals.
CEWII
December 3, 2009 at 1:32 pm
Elliott W (12/3/2009)
gregory.anderson (12/3/2009)
I'm not sure if I follow yours completely....but I'll try again.I have two servers, ServerA, and ServerB.
ServerA holds SQL Server Agent
ServerB holds asp.net application code
The job on ServerA calls a bat file that resides on ServerB.
The bat file does a "nlb stop", then changes the database in the web.config connection string, then does an "nlb start".
I just got done talking with a Server/Active Directory guy here at my work, and we found out that 1) Sql Server Agent wasn't able to call/execute a program on a different server (I would think this should be possible) and 2) If it were able to call/execute that job on ServerB, it will run the job from ServerA. That would then cause the bat file to try and do my "nlb stop" on ServerA which is the SQL Server, which is not load balanced, so it wouldn't work anyway.
Ok, is SqlAdmin a domain user or a local user? If it is a domain user then we'll probably have to look further. Is TestDMZ a domain or a workgroup?
As far as the other stuff:
It can call a script FROM another server but that is not the same as running it there. You *might* be able to accomplish that with PsExec from sysinternals.
CEWII
SqlAdmin is a domain administrator. testdmz is the name of the domain. So testdmz\sqladmin is the windows active directory user.
On an unrelated question, your sig talks about that scheduling software; is that your company/company you work for? Or just a product that you use and like?
December 3, 2009 at 1:38 pm
When you reference the bat files is it on a default share C$ or such or one you created yourself? Check the permissions on the share, administrators don't bypass that checking.
It is a company that I own but I have another full-time job. It isn't so much scheduling as the ability to look at your SQL Agent job history in a meaningful way.. I like to look at it on an outlook style calendar so I can see what else was running at the same time and any failed jobs. I am currently working on version 1.4 which will have some nice useability enhancements that are especially nice for servers with LOTS of jobs to sort through..
CEWII
December 3, 2009 at 1:53 pm
Elliott W (12/3/2009)
When you reference the bat files is it on a default share C$ or such or one you created yourself? Check the permissions on the share, administrators don't bypass that checking.It is a company that I own but I have another full-time job. It isn't so much scheduling as the ability to look at your SQL Agent job history in a meaningful way.. I like to look at it on an outlook style calendar so I can see what else was running at the same time and any failed jobs. I am currently working on version 1.4 which will have some nice useability enhancements that are especially nice for servers with LOTS of jobs to sort through..
CEWII
In regards to my real question...I'm accessing the bat file on ServerB like this "\\serverA\c$\temp\go.bat" parm1 parm2
I made sure the testdmz\sqladmin has permissions on the temp folder, along with the bat file. Still can't get it...
About your company...that's something that I've wanted [to write] for a long time. I was actually in the very early stages of getting the queries configured correctly to be able to do that.
Would love to do contract work for you:-D.
Do you have any trial versions, or anything like that so I can test it out before purchasing?
December 3, 2009 at 2:13 pm
I have a thought..
Login as the SQLAdmin user either through Remote Desktop or on the console itself and see if you can get to that location. If it doesn't let you then it might make sense to add a new share where you control the permissions and such. If it does let you then make sure that SQL Agent and SQL are really running as that user by checking both the service manager and the task manager. The reason I say both is that it can be changed in service mananger but that doesn't go into effect until the SQL server is restarted, I'm just playing paranoid there.. A share you control might be the best bet anyways..
There is a demo version on the website that it good for 15 days. I can also give additional time if necessary. I still can't believe that MS doesn't provide something like that..
CEWII
December 3, 2009 at 2:20 pm
Elliott W (12/3/2009)
I have a thought..Login as the SQLAdmin user either through Remote Desktop or on the console itself and see if you can get to that location. If it doesn't let you then it might make sense to add a new share where you control the permissions and such. If it does let you then make sure that SQL Agent and SQL are really running as that user by checking both the service manager and the task manager. The reason I say both is that it can be changed in service mananger but that doesn't go into effect until the SQL server is restarted, I'm just playing paranoid there.. A share you control might be the best bet anyways..
There is a demo version on the website that it good for 15 days. I can also give additional time if necessary. I still can't believe that MS doesn't provide something like that..
CEWII
I'm not able to login as SqlAdmin (server team won't let my team know the password).
I have been able to execute the program from ServerA doing a "Start->Run", but it executes from ServerA instead of ServerB...but at least I can get it to run.
About your other program, can I send you comments about it? Personal/Work email address?
December 3, 2009 at 2:47 pm
The problem with doing start|run as YOU from Server A is that it doesn't prove much.. All you have proven is YOU can do it.. If they won't let you know the password then have them do it with you over their shoulder, that way everybody can get what they want..
You can send comments to:
sjhv-comments at novaconceptsltd dot com
and I would love to hear some. If there is something that doesn't work as you think it should I would love to hear it or a feature request.. I have a list of features I'm working on now..
CEWII
December 3, 2009 at 2:50 pm
Elliott W (12/3/2009)
The problem with doing start|run as YOU from Server A is that it doesn't prove much.. All you have proven is YOU can do it.. If they won't let you know the password then have them do it with you over their shoulder, that way everybody can get what they want..You can send comments to:
sjhv-comments at novaconceptsltd dot com
and I would love to hear some. If there is something that doesn't work as you think it should I would love to hear it or a feature request.. I have a list of features I'm working on now..
CEWII
BTW - I've sent 2 or 3 emails about suggestions, great tool! Now I need to talk the boss into buying it. (been sending them to the support at novacon........)
December 3, 2009 at 3:19 pm
That is an ok email address as well. I was jsut reading those.. I will respond to them off hours..
Thanks.
CEWII
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply