Job owned by a non-sysadmin fails to run

Hi,
The preblem has been already discussed but none of answers help me.
I have a SQL Server 2000 SP4. Users that are not sysadmins create jobs for
SQL Server Agent. These jobs consist of a single CmdExec step.
As advised in many posts I created a Proxy SQL Server Agent account
(sqlproxy) but this did not help me, the jobs still fail to run. This
account is a windows account. I made this account belong to the sysadmins
role of SQL Server.
Both SQL Server and SQL Server Agent run under a special account
(sqlservice).
I added the account sqlservice to Administrators as advised in the article
http://support.microsoft.com/kb/833559 and even added to Administrators the
account sqlproxy, although the article states I did not have to.
The message I get in EventLog is like below:
-- message start
SQL Server Scheduled Job '<job name>' (0xEFC686299E5B9249957CC5FCF5C782C4) -
Status: Failed - Invoked on: 2006-12-18 12:06:06 - Message: The job failed.
The Job was invoked by User <domain>\<user>. The last step to run was step
1 (<step name> ).
-- message end
The command of the CmdExec step runs fine if I login as user as well as
sqlproxy.
I set the output file in advanced properties of the CmdExec step but did not
see in that file anything. Looks like the job does not start at all.
When I add user acocunt to the group Administrators the job runs
successfully, but this is definitely not an option.
I would appreciate any help as I run out of ideas already.Try changing the owner of the job to SA or another sysadmin.
Ivan Gerken wrote:
> Hi,
> The preblem has been already discussed but none of answers help me.
> I have a SQL Server 2000 SP4. Users that are not sysadmins create jobs for
> SQL Server Agent. These jobs consist of a single CmdExec step.
> As advised in many posts I created a Proxy SQL Server Agent account
> (sqlproxy) but this did not help me, the jobs still fail to run. This
> account is a windows account. I made this account belong to the sysadmins
> role of SQL Server.
> Both SQL Server and SQL Server Agent run under a special account
> (sqlservice).
> I added the account sqlservice to Administrators as advised in the article
> http://support.microsoft.com/kb/833559 and even added to Administrators th
e
> account sqlproxy, although the article states I did not have to.
> The message I get in EventLog is like below:
> -- message start
> SQL Server Scheduled Job '<job name>' (0xEFC686299E5B9249957CC5FCF5C782C4)
-
> Status: Failed - Invoked on: 2006-12-18 12:06:06 - Message: The job failed
.
> The Job was invoked by User <domain>\<user>. The last step to run was ste
p
> 1 (<step name> ).
> -- message end
> The command of the CmdExec step runs fine if I login as user as well as
> sqlproxy.
> I set the output file in advanced properties of the CmdExec step but did n
ot
> see in that file anything. Looks like the job does not start at all.
> When I add user acocunt to the group Administrators the job runs
> successfully, but this is definitely not an option.
> I would appreciate any help as I run out of ideas already.|||This quick solution is not an option.
Jobs are created from an external application (Business Desk of Commerce
Server 2002). A created job is owned by the user logged in to the
application (using win integrated authentication).
I definitely don't want to give users administrative privileges.
"PSPDBA" <DissendiumDBA@.gmail.com> wrote in message
news:1166704870.838450.230270@.f1g2000cwa.googlegroups.com...
> Try changing the owner of the job to SA or another sysadmin.
>|||For debugging, try temporarily changing the job owner to 'sa' as PSPDBA
suggested. If the job still fails, then the problem is related to running
as a job rather than a security issue.
Exactly what does the CmdExec step do? Are mapped drives accessed?
Hope this helps.
Dan Guzman
SQL Server MVP
"Ivan Gerken" <testivan@.waterproof.nl> wrote in message
news:uE$3GeRJHHA.420@.TK2MSFTNGP06.phx.gbl...
> This quick solution is not an option.
> Jobs are created from an external application (Business Desk of Commerce
> Server 2002). A created job is owned by the user logged in to the
> application (using win integrated authentication).
> I definitely don't want to give users administrative privileges.
> "PSPDBA" <DissendiumDBA@.gmail.com> wrote in message
> news:1166704870.838450.230270@.f1g2000cwa.googlegroups.com...
>|||Looks like I have a problem with CmdExec jobs in general.
I changed the step command to "dir c:\temp" and it ran fine when owned by an
admin but failed when owned by a user. In case of being owned by a user even
the output file was not created. The folder c:\temp has "full control"
permission granted to everyone.
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:42FF0C9C-4AFC-42AF-9464-0931A1DDE13B@.microsoft.com...
> I'm starting to run out of ideas. Do you have any CmdExec job steps that
> successfully run as non-sysadmin users or is it just dmlrun.exe that has
> the problem?
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP|||Lets make sure I have the relevant details right since so much has been
discussed in this thread:
- SQL Server service and SQL Server Agent service run under the same
account
- The account is a member of the local administrators group
- xp_cmdshell runs fine when involed by non-sysadmins
- CmdExec jobs fail for jobs owned by non-sysadmins
What I find strange is that xp_cmdshell works but CmdExec doesn't. I can
see how this might be the case if you used different service accounts and
the SQL Agent service account lacked the advanced user rights (e.g. 'act as
part of the operating system' and 'replace a process-level token') that are
needed to switch security context to the proxy account.
Can you double-check to ensure the same service account is used for SQL
Server and SQL Server Agent services? If you have made changes to service
account security, have you since restarted the service? In some cases, a
server restart in needed in order for security changes to fully take affect.
Happy Holidays
Dan Guzman
SQL Server MVP
"Ivan Gerken" <testivan@.waterproof.nl> wrote in message
news:%239rDNPAKHHA.2236@.TK2MSFTNGP02.phx.gbl...
> Looks like I have a problem with CmdExec jobs in general.
> I changed the step command to "dir c:\temp" and it ran fine when owned by
> an admin but failed when owned by a user. In case of being owned by a user
> even the output file was not created. The folder c:\temp has "full
> control" permission granted to everyone.
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:42FF0C9C-4AFC-42AF-9464-0931A1DDE13B@.microsoft.com...
>|||- SQL Server service and SQL Server Agent service run under the same
account
Yes, referred to earlier as sqlservice. However, the services MSSEARCH,
MSSQLServerADHelper, MSSQLServerOLAPService run under Local System (I think
it hardly matters but just in case).
- The account is a member of the local administrators group
Yes, plus OLAP Administrators and Users.
- xp_cmdshell runs fine when involed by non-sysadmins
Yes. User account is a member of Users and Remote Desktop Users.
- CmdExec jobs fail for jobs owned by non-sysadmins
Yes, even after restarting both MSSQLSERVER and SQLSERVERAGENT.
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:A7AC10BD-AE8F-4C96-ADE3-1F1603A38D9C@.microsoft.com...
> Lets make sure I have the relevant details right since so much has been
> discussed in this thread:
> - SQL Server service and SQL Server Agent service run under the same
> account
> - The account is a member of the local administrators group
> - xp_cmdshell runs fine when involed by non-sysadmins
> - CmdExec jobs fail for jobs owned by non-sysadmins
> What I find strange is that xp_cmdshell works but CmdExec doesn't. I can
> see how this might be the case if you used different service accounts and
> the SQL Agent service account lacked the advanced user rights (e.g. 'act
> as part of the operating system' and 'replace a process-level token') that
> are needed to switch security context to the proxy account.
> Can you double-check to ensure the same service account is used for SQL
> Server and SQL Server Agent services? If you have made changes to service
> account security, have you since restarted the service? In some cases, a
> server restart in needed in order for security changes to fully take
> affect.
> --
> Happy Holidays
> Dan Guzman
> SQL Server MVP|||> Yes, even after restarting both MSSQLSERVER and SQLSERVERAGENT.
Have you restarted the server since you added the sqlservice account to the
local Administrator's group? Although not normally required, I've seen
occasions where a restart was needed to pickup the group membership change.
BTW, are there any related messages in the SQL Agent log files?
Hope this helps.
Dan Guzman
SQL Server MVP
"Ivan Gerken" <testivan@.waterproof.nl> wrote in message
news:uVs$ZSQKHHA.2232@.TK2MSFTNGP02.phx.gbl...
>- SQL Server service and SQL Server Agent service run under the same
>account
> Yes, referred to earlier as sqlservice. However, the services MSSEARCH,
> MSSQLServerADHelper, MSSQLServerOLAPService run under Local System (I
> think it hardly matters but just in case).
> - The account is a member of the local administrators group
> Yes, plus OLAP Administrators and Users.
> - xp_cmdshell runs fine when involed by non-sysadmins
> Yes. User account is a member of Users and Remote Desktop Users.
> - CmdExec jobs fail for jobs owned by non-sysadmins
> Yes, even after restarting both MSSQLSERVER and SQLSERVERAGENT.
>
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:A7AC10BD-AE8F-4C96-ADE3-1F1603A38D9C@.microsoft.com...
>|||Dan,
Many thanks for a great hint! I checked SQL Agent log and found there the
following message:
[136] Job <job name> reported: Warning: cannot write logfile
c:\temp\dmout.txt. Error 5 : Access is denied
Then, after I have cleared the "Output file" box the job executed
successfully. So the problem seems to be solved.
However, I find this error very odd because full control is granted to
everyone on c:\temp
Many thanks for your help!
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:C7F5CB7B-970A-4EFF-885C-772ED21ECF0A@.microsoft.com...
> Have you restarted the server since you added the sqlservice account to
> the local Administrator's group? Although not normally required, I've
> seen occasions where a restart was needed to pickup the group membership
> change.
> BTW, are there any related messages in the SQL Agent log files?
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Ivan Gerken" <testivan@.waterproof.nl> wrote in message
> news:uVs$ZSQKHHA.2232@.TK2MSFTNGP02.phx.gbl...
>|||I'm glad you were able to get it sorted out. I'm sorry I didn't suggest
checking the log earlier.

> However, I find this error very odd because full control is granted to
> everyone on c:\temp
The error 5 can be caused by the file being used by another process or that
the read-only attribute is set.
Hope this helps.
Dan Guzman
SQL Server MVP
"Ivan Gerken" <testivan@.waterproof.nl> wrote in message
news:eLhoAacKHHA.3424@.TK2MSFTNGP02.phx.gbl...
> Dan,
> Many thanks for a great hint! I checked SQL Agent log and found there the
> following message:
> [136] Job <job name> reported: Warning: cannot write logfile
> c:\temp\dmout.txt. Error 5 : Access is denied
> Then, after I have cleared the "Output file" box the job executed
> successfully. So the problem seems to be solved.
> However, I find this error very odd because full control is granted to
> everyone on c:\temp
> Many thanks for your help!
>
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:C7F5CB7B-970A-4EFF-885C-772ED21ECF0A@.microsoft.com...
>

jdbc permissions

seems precious few answers in the jdbc group...
as I'm trying to connect Tomcat 5 to MSDE, according to the help system, I
have to give the jdbc driver some read/write perms, but as the java is
actually a servlet, there is no codebase being used.
Anyone got any ideas on where I can set the permissions?Make sure you give sql permissions to the sql user you are
using in your servlet to connect to sql server. There is
no concept of giving read/write permissions to a jdbc
driver.
Let us know if you need more clarification on this or how
to do it.
hth.
>--Original Message--
>seems precious few answers in the jdbc group...
>as I'm trying to connect Tomcat 5 to MSDE, according to
the help system, I
>have to give the jdbc driver some read/write perms, but
as the java is
>actually a servlet, there is no codebase being used.
>Anyone got any ideas on where I can set the permissions?
>.
>|||<anonymous@.discussions.microsoft.com> wrote in news:1493501c3fa41
$8cbdc0d0$a301280a@.phx.gbl:
well, this is the page from the helpfile thats caused me to ask:
''
Using the SQL Server 2000 Driver for JDBC on a Java 2 Platform
----
--
When using the SQL Server 2000 Driver for JDBC on a Java 2 Platform with
the standard security manager enabled, you must give the driver some
additional permissions. Refer to your Java 2 Platform documentation for
more information about the Java 2 Platform security model and
permissions.
You can run an application on a Java 2 Platform with the standard
security manager using:
"java -Djava.security.manager application_class_name"
where application_class_name is the class name of the application.
Web browser applets running in the Java 2 plug-in are always running in a
Java Virtual Machine with the standard security manager enabled. To
enable the necessary permissions, you must add them to the security
policy file of the Java 2 Platform. This security policy file can be
found in the jre/lib/security subdirectory of the Java 2 Platform
installation directory.
To use JDBC data sources, all code bases must have the following
permissions:
// permissions granted to all domains
grant {
// DataSource access
permission java.util.PropertyPermission "java.naming.*", "read,write";
// Adjust the server host specification for your environment
permission java.net.SocketPermission "*.microsoft.com:0-65535",
"connect";
};
To use insensitive scrollable cursors, and perform client-side sorting of
some DatabaseMetaData ResultSets, all code bases must have access to
temporary files. If access to temporary files is not available, the
driver may throw an exception indicating that it is unable to set up a
static cursor cache.
For JDK 1.1 environments, access to "current working directory" must be
granted.
For Java 2 environments, access to the temporary directory specified by
the VM configuration must be granted.
The following is an example of permissions being granted for the C:\TEMP
directory:
// permissions granted to all domains
grant {
// Permission to create and delete temporary files.
// Adjust the temporary directory for your environment.
permission java.io.FilePermission "C:\\TEMP\\-", "read,write,delete";
};
'''
> Make sure you give sql permissions to the sql user you are
> using in your servlet to connect to sql server. There is
> no concept of giving read/write permissions to a jdbc
> driver.
> Let us know if you need more clarification on this or how
> to do it.
>
via the tomcat log, its failing to load the jdbc driver, but whether its
authentication or unable to find the driver, I'm not sure
> hth.
>>--Original Message--
>>seems precious few answers in the jdbc group...
>>as I'm trying to connect Tomcat 5 to MSDE, according to
> the help system, I
>>have to give the jdbc driver some read/write perms, but
> as the java is
>>actually a servlet, there is no codebase being used.
>>Anyone got any ideas on where I can set the permissions?
>>.
>|||I apologige for it. There is nothing special you need to
do on sql server side. I will post more if I can find more
information on this.
Thanks.
>--Original Message--
><anonymous@.discussions.microsoft.com> wrote in
news:1493501c3fa41
>$8cbdc0d0$a301280a@.phx.gbl:
>well, this is the page from the helpfile thats caused me
to ask:
>''
>Using the SQL Server 2000 Driver for JDBC on a Java 2
Platform
>----
--
>--
>When using the SQL Server 2000 Driver for JDBC on a Java
2 Platform with
>the standard security manager enabled, you must give the
driver some
>additional permissions. Refer to your Java 2 Platform
documentation for
>more information about the Java 2 Platform security model
and
>permissions.
>You can run an application on a Java 2 Platform with the
standard
>security manager using:
>"java -Djava.security.manager application_class_name"
>where application_class_name is the class name of the
application.
>Web browser applets running in the Java 2 plug-in are
always running in a
>Java Virtual Machine with the standard security manager
enabled. To
>enable the necessary permissions, you must add them to
the security
>policy file of the Java 2 Platform. This security policy
file can be
>found in the jre/lib/security subdirectory of the Java 2
Platform
>installation directory.
>To use JDBC data sources, all code bases must have the
following
>permissions:
>// permissions granted to all domains
>grant {
>// DataSource access
>permission
java.util.PropertyPermission "java.naming.*", "read,write";
>// Adjust the server host specification for your
environment
>permission java.net.SocketPermission "*.microsoft.com:0-
65535",
>"connect";
>};
>To use insensitive scrollable cursors, and perform client-
side sorting of
>some DatabaseMetaData ResultSets, all code bases must
have access to
>temporary files. If access to temporary files is not
available, the
>driver may throw an exception indicating that it is
unable to set up a
>static cursor cache.
>For JDK 1.1 environments, access to "current working
directory" must be
>granted.
>For Java 2 environments, access to the temporary
directory specified by
>the VM configuration must be granted.
>The following is an example of permissions being granted
for the C:\TEMP
>directory:
>// permissions granted to all domains
>grant {
>// Permission to create and delete temporary files.
>// Adjust the temporary directory for your environment.
>permission java.io.FilePermission "C:\\TEMP\\-
", "read,write,delete";
>};
>'''
>
>
>
>> Make sure you give sql permissions to the sql user you
are
>> using in your servlet to connect to sql server. There
is
>> no concept of giving read/write permissions to a jdbc
>> driver.
>> Let us know if you need more clarification on this or
how
>> to do it.
>via the tomcat log, its failing to load the jdbc driver,
but whether its
>authentication or unable to find the driver, I'm not sure
>
>> hth.
>>--Original Message--
>>seems precious few answers in the jdbc group...
>>as I'm trying to connect Tomcat 5 to MSDE, according to
>> the help system, I
>>have to give the jdbc driver some read/write perms, but
>> as the java is
>>actually a servlet, there is no codebase being used.
>>Anyone got any ideas on where I can set the permissions?
>>.
>>
>.
>