CmdExec jobs run in non-admin Windows accounts

I am attempting to tighten the security of our MSSQL environment in a number
of ways and one of these is to run scheduled CmdExec jobs via a non-admin
Windows account. Alas, I cannot get this to work correctly.
I created a credential "abc_job" based on a non-admin Windows domain account
"dom01\abc" in a global group.
I assigned permission for "log on as a batch job" to the global group.
I created a proxy "abc_prox", assigned it to the credential and set it to
active for CmdExec.
I gave access to the proxy for a MSSQL account "xyz" (non sysadmin).
I created a test job and gave ownership of the job to the "xyz" MSSQL account.
The job only has one step and the step runs as proxy "abc_prox".
The job is one line: g:\hjk\test.bat > g:\hjk\test.out
The batch file consists of 5 lines:
chdir /D g:\hjk
whoami
net time
ping gh234test
dir
When I start the job, only the chdir and dir commands succeed. The other 3
lines return "access is denied". However when I remote term to the SQL
Server machine as "dom01\abc" and run the batch file in a command prompt
window it succeeds.
What am I missing?forgot to mention, I also assigned the "xyz" MSSQL account to the
SQLAgentUserRole in the msdb database.

CmdExec jobs run in non-admin Windows accounts

I am attempting to tighten the security of our MSSQL environment in a number
of ways and one of these is to run scheduled CmdExec jobs via a non-admin
Windows account. Alas, I cannot get this to work correctly.
I created a credential "abc_job" based on a non-admin Windows domain account
"dom01\abc" in a global group.
I assigned permission for "log on as a batch job" to the global group.
I created a proxy "abc_prox", assigned it to the credential and set it to
active for CmdExec.
I gave access to the proxy for a MSSQL account "xyz" (non sysadmin).
I created a test job and gave ownership of the job to the "xyz" MSSQL accoun
t.
The job only has one step and the step runs as proxy "abc_prox".
The job is one line: g:\hjk\test.bat > g:\hjk\test.out
The batch file consists of 5 lines:
chdir /D g:\hjk
whoami
net time
ping gh234test
dir
When I start the job, only the chdir and dir commands succeed. The other 3
lines return "access is denied". However when I remote term to the SQL
Server machine as "dom01\abc" and run the batch file in a command prompt
window it succeeds.
What am I missing?forgot to mention, I also assigned the "xyz" MSSQL account to the
SQLAgentUserRole in the msdb database.

CmdExec jobs run in non-admin Windows accounts

I am attempting to tighten the security of our MSSQL environment in a number
of ways and one of these is to run scheduled CmdExec jobs via a non-admin
Windows account. Alas, I cannot get this to work correctly.
I created a credential "abc_job" based on a non-admin Windows domain account
"dom01\abc" in a global group.
I assigned permission for "log on as a batch job" to the global group.
I created a proxy "abc_prox", assigned it to the credential and set it to
active for CmdExec.
I gave access to the proxy for a MSSQL account "xyz" (non sysadmin).
I created a test job and gave ownership of the job to the "xyz" MSSQL account.
The job only has one step and the step runs as proxy "abc_prox".
The job is one line: g:\hjk\test.bat > g:\hjk\test.out
The batch file consists of 5 lines:
chdir /D g:\hjk
whoami
net time
ping gh234test
dir
When I start the job, only the chdir and dir commands succeed. The other 3
lines return "access is denied". However when I remote term to the SQL
Server machine as "dom01\abc" and run the batch file in a command prompt
window it succeeds.
What am I missing?
forgot to mention, I also assigned the "xyz" MSSQL account to the
SQLAgentUserRole in the msdb database.

CmdExec Jobs

Hello - I am trying to set up a job through sql server agent. The job sets
the osqluser and osqlpassword, then calls a bunch of batch files. When I ru
n
it in dos, it works great, but in the sql job, it does nothing. Does the
syntax need to be different or something'
SET uname=Uname
SET pword=Pword
osql -Ssrvname -i inputfile -n > d:\outputfile.txt
Thanks!
MitchYou can only execute one command in a CmdExec job step. Having several job s
teps will probably not
cut it as I guess that the SET settings won't be saved between job step exec
utions. Do the commands
in a bat file instead and schedule this bat file.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Mitch" <Mitch@.discussions.microsoft.com> wrote in message
news:7848BB7E-494B-4F6D-B3A7-B5C2E71C4912@.microsoft.com...
> Hello - I am trying to set up a job through sql server agent. The job set
s
> the osqluser and osqlpassword, then calls a bunch of batch files. When I
run
> it in dos, it works great, but in the sql job, it does nothing. Does the
> syntax need to be different or something'
> SET uname=Uname
> SET pword=Pword
> osql -Ssrvname -i inputfile -n > d:\outputfile.txt
> Thanks!
> Mitch

cmdexec in SQL agent Jobs

If I do xp_cmdexec 'dir' it displays the directory:
C:\Program Files\Common Files\System\Mapi\1033\NT
on a different sql server the same command shows the directory:
C:\WINNT\system32
How do you set the directory which xp_cmdexec uses?My guess is that it uses the PATH environment variable. You should be able to check that from a DOS
box:
SET
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"AR" <remove_rittingAThot_mail_DOT_C_O_M> wrote in message
news:uRZPa2erDHA.3504@.TK2MSFTNGP11.phx.gbl...
> If I do xp_cmdexec 'dir' it displays the directory:
> C:\Program Files\Common Files\System\Mapi\1033\NT
> on a different sql server the same command shows the directory:
> C:\WINNT\system32
> How do you set the directory which xp_cmdexec uses?
>
>

CmdExec and Environment

Unfortunately several of my sql users need tot run cmdexe jobs
(dts-packages). To keep them from totally destroying my server I
configured a proxy account with not to much rights on the files system.
For each virtual server I created a separate temp dir for the proxy user.
when a cmdexec job is run the %temp% %tmp% point to the tempdir which
belong to the cluster service account. Now I noticed a Environment
REG_MULTI_SZ key for the sql and sqlagent services (\hkey local
machine\...\/services\mssql$instance). This key contains a full set of
environment vars, but is not wel documented (no hits on google).
Is this, a standard reg key for services, or something special for sql
and sqlagent or something special for a clustered sql and sqlagent
service? I want to temper with the %temp% and %tmp%.
Hans
Hans de Bruin wrote:
> Unfortunately several of my sql users need tot run cmdexe jobs
> (dts-packages). To keep them from totally destroying my server I
> configured a proxy account with not to much rights on the files system.
> For each virtual server I created a separate temp dir for the proxy user.
> when a cmdexec job is run the %temp% %tmp% point to the tempdir which
> belong to the cluster service account. Now I noticed a Environment
> REG_MULTI_SZ key for the sql and sqlagent services (\hkey local
> machine\...\/services\mssql$instance). This key contains a full set of
> environment vars, but is not wel documented (no hits on google).
> Is this, a standard reg key for services, or something special for sql
> and sqlagent or something special for a clustered sql and sqlagent
> service? I want to temper with the %temp% and %tmp%.
>
This is something special for clustering. Just before the sqlserver or
sqlserver agent service starts ResrcMon writes the envrionment to this
key. Any manual made changes are overwriten.
When DTSRun can't write to the \documents and
setting\clusterserviceaccount\local\temp it tries to write in
c:\winows\temp.
Hans
|||When using the Proxy Account both CmdExec and xp_cmdshell call the Win32 API
LogonUser
http://msdn.microsoft.com/library/de.../logonuser.asp
As dwLogonType it uses LOGON32_LOGON_BATCH
As dwLogonProvider is uses LOGON32_PROVIDER_DEFAULT
After which they call the Win32 API CreateProcessAsUser
http://msdn.microsoft.com/library/de...cessasuser.asp
However CreateProcessAsUser does not load the specified user's profile into
the HKEY_USERS registry key. Therefore, to access the information in the
HKEY_CURRENT_USER registry key and if the lpEnvironment parameter is NULL,
the new process inherits the environment of the calling process.
CreateProcessAsUser does not automatically modify the environment block to
include environment variables specific to the user represented by hToken.
For example, the USERNAME and USERDOMAIN variables are inherited from the
calling process if lpEnvironment is NULL. It is your responsibility to
prepare the environment block for the new process and specify it in
lpEnvironment.
So what you get is the enviroment of the service account.
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2005 All rights reserved.
"Hans de Bruin" <invalid@.invalid> wrote in message
news:1120486047.358052@.aquila.amc.uva.nl...
> Hans de Bruin wrote:
> This is something special for clustering. Just before the sqlserver or
> sqlserver agent service starts ResrcMon writes the envrionment to this
> key. Any manual made changes are overwriten.
> When DTSRun can't write to the \documents and
> setting\clusterserviceaccount\local\temp it tries to write in
> c:\winows\temp.
> --
> Hans

CmdExe priveleges

SO here's the deal - I have a developer that needs to be
able to schedule jobs, but tha I do NOT want to give
System admin privelges to. She has DBO privelges on the
DBs she needs, and is a member of Bulk Insert and Process
ADmin server roles. She has the abiltiy to schedule jobsm
but if she creates one that uses CmdExe steps they fail
wit the message:
Executed as user: MCC_GENESIS\SQLSrv. The process could
not be created for step 1 of job
0xB793D66AE8016647AC64FFEB3EE2E202 (reason: A required
privilege is not held by the client). The step failed.
I have configured the SQL Agent to use a proxy account to
run scripts if the user is not SysAdmin, and I am using
the same domian account that starts the MSSQL service as
the proxy. The account is a DOMAIN admin (I know bad idea,
but that's what they gave me to work with.)
So here is the question - why do jobs fail when they are
owned by the debveloper, but run when owned by SA - both
are using the same account, (SA using account that starts
server, develped using it thorugh proxy)
Any advice would be greatly apprecaited!SQL Server Agent need a set of privileges in the OS to do these things. Search Books Online for
"level token" and you will find the section in BOL which describes these.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Craig Dols" <cdols.no-spam@.hotmail.com> wrote in message
news:0dca01c3a9fe$2f3ed4a0$a501280a@.phx.gbl...
> SO here's the deal - I have a developer that needs to be
> able to schedule jobs, but tha I do NOT want to give
> System admin privelges to. She has DBO privelges on the
> DBs she needs, and is a member of Bulk Insert and Process
> ADmin server roles. She has the abiltiy to schedule jobsm
> but if she creates one that uses CmdExe steps they fail
> wit the message:
> Executed as user: MCC_GENESIS\SQLSrv. The process could
> not be created for step 1 of job
> 0xB793D66AE8016647AC64FFEB3EE2E202 (reason: A required
> privilege is not held by the client). The step failed.
> I have configured the SQL Agent to use a proxy account to
> run scripts if the user is not SysAdmin, and I am using
> the same domian account that starts the MSSQL service as
> the proxy. The account is a DOMAIN admin (I know bad idea,
> but that's what they gave me to work with.)
> So here is the question - why do jobs fail when they are
> owned by the debveloper, but run when owned by SA - both
> are using the same account, (SA using account that starts
> server, develped using it thorugh proxy)
> Any advice would be greatly apprecaited!
>|||Found it - thanks for your help!
>--Original Message--
>SQL Server Agent need a set of privileges in the OS to do
these things. Search Books Online for
>"level token" and you will find the section in BOL which
describes these.
>--
>Tibor Karaszi, SQL Server MVP
>Archive at: http://groups.google.com/groups?
oi=djq&as_ugroup=microsoft.public.sqlserver
>
>"Craig Dols" <cdols.no-spam@.hotmail.com> wrote in message
>news:0dca01c3a9fe$2f3ed4a0$a501280a@.phx.gbl...
>> SO here's the deal - I have a developer that needs to be
>> able to schedule jobs, but tha I do NOT want to give
>> System admin privelges to. She has DBO privelges on the
>> DBs she needs, and is a member of Bulk Insert and
Process
>> ADmin server roles. She has the abiltiy to schedule
jobsm
>> but if she creates one that uses CmdExe steps they fail
>> wit the message:
>> Executed as user: MCC_GENESIS\SQLSrv. The process could
>> not be created for step 1 of job
>> 0xB793D66AE8016647AC64FFEB3EE2E202 (reason: A required
>> privilege is not held by the client). The step failed.
>> I have configured the SQL Agent to use a proxy account
to
>> run scripts if the user is not SysAdmin, and I am using
>> the same domian account that starts the MSSQL service as
>> the proxy. The account is a DOMAIN admin (I know bad
idea,
>> but that's what they gave me to work with.)
>> So here is the question - why do jobs fail when they are
>> owned by the debveloper, but run when owned by SA - both
>> are using the same account, (SA using account that
starts
>> server, develped using it thorugh proxy)
>> Any advice would be greatly apprecaited!
>
>.
>