How come my SQL Server Job won't run?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jeff Stroope - 07 Jan 2008 22:59 GMT

Hi - I just created a job in SQL Server using Management Studio. This is the
first time I've tried to build a job. The job failed to execute at the
scheduled time and it fails when I start it manually. Looking at the Job
History, the error message says, "Executed as user:NT AUTHORITY\SYSTEM. Login
failed for user 'domain\mycomputer$.[SQLSTATE 28000] (Error 18456). The step
failed."

I tried to pick a different user from the Run as drop down in the Job step
properties window, but the drop down is empty. Does anyone know what might
be going on here?

Signature

Thanks,

Jeff

Reply to this Message

Ben Nevarez - 08 Jan 2008 01:02 GMT

Hi Jeff,

What is your job supposed to do? On the job history expand the job steps and
look for additional error messages every step. If applicable also check the
SQL Server error log.

You can also try the Advanced page on the Job Step section and specify an
output file. Then run the job again and see if you can find some additional
error information there.

Perhaps you do not need to change the 'Run as' section until you know what
the problem is.

Hope this helps,

Ben Nevarez
Senior Database Administrator

> Hi - I just created a job in SQL Server using Management Studio. This is the
> first time I've tried to build a job. The job failed to execute at the
[quoted text clipped - 6 lines]
> properties window, but the drop down is empty. Does anyone know what might
> be going on here?

Reply to this Message

Jeff Stroope - 08 Jan 2008 17:29 GMT

Hi Ben - the job is supposed to run a stored procedure. The job only has one
step, which is "execute sp_storedProcedure". I'll try the output file and
see what happens.

Signature

Thanks,

Jeff

> Hi Jeff,
>
[quoted text clipped - 24 lines]
> > properties window, but the drop down is empty. Does anyone know what might
> > be going on here?

Reply to this Message

Russell Fields - 08 Jan 2008 13:43 GMT

Jeff,

I just ran into someone getting the same error message. Are you by any
change running something like the following TSQL step?
exec xp_cmdshell 'bcp xxx.dbo.yyy out xxx.fil -T'

In the other case, it seems that shelling out and then logging back in as a
Trusted Connection raised the error.

In their case, changing the SQL Server service from running as Local System
to running as a domain login resolved the problem. (Of course, you need to
have or create a domain account with the proper permissions.)

RLF

> Hi - I just created a job in SQL Server using Management Studio. This is
> the
[quoted text clipped - 10 lines]
> might
> be going on here?

Reply to this Message

Jeff Stroope - 08 Jan 2008 17:30 GMT

Thanks Russell - no, I'm just trying execute a stored procedure that queries
some tables in a database on a linked server and then populates tables on the
job server.

Signature

Thanks,

Jeff

> Jeff,
>
[quoted text clipped - 25 lines]
> > might
> > be going on here?

Reply to this Message

Russell Fields - 08 Jan 2008 17:57 GMT

Jeff,

OK, but you still have an execution context issue since you are going to
another server. A couple more questions:

1. Who is the job owner of the SQL Agent job? Your login? The 'sa'
account? NT AUTHORITY\SYSTEM? A domain login? A SQL Server login?

2. How do you have your security set on the linked server? Check the
security tab of the linked server definition. Local login impersonation?
The login's current security context? A specific login?

You might be able to manipulate the linked server definition to get this to
work for you. But, as is, I suspect that the local system account is at the
root of the problem for the job you have defined.

RLF

> Thanks Russell - no, I'm just trying execute a stored procedure that
> queries
[quoted text clipped - 37 lines]
>> > might
>> > be going on here?

Reply to this Message

Praveen - 28 Feb 2008 18:31 GMT

Hi,

Did you find the solution for this. Even I am facing similar problem.
I am trying to configure a linked server with the following settings

sp_addlinkedserver 'Linked Server Name', 'SQL Server'

sp_addlinkedsrvlogin @rmtsrvname = 'Linked Server Name'
, @useself = 'FALSE'
, @locallogin = 'Domain\LoginName'
, @rmtuser = 'DatabaseUserName'
, @rmtpassword = 'DatabaseUSerPassword'

If I run the query against this linked server I get the perfect result.
Now I have scheduled a Job which runs on sa account. It always throws the
error:
Executed as user: NT AUTHORITY\SYSTEM. Login failed for user
'DOMAIN\MACHINENAME'. [SQLSTATE 28000] (Error 18456).

Let me know on what I am doing wrong.

Reply to this Message

Russell Fields - 28 Feb 2008 19:04 GMT

Praveen,

What you have said is, if the linked server is accessed by login
'Domain\LoginName' then log in remotely as 'DatabaseUserName' using
'DatabaseUSerPassword'. If the linked server is accessed by any other
login, then that login's security context.

If I understood correctly, you said that the job runs as 'sa', which is a
local SQL Server login and has no domain rights. You messages refer to NT
AUTHORITY\SYSTEM and 'DOMAIN\MACHINENAME', to which you do not seem to have
granted any special rights to the 'Linked Server Name' server.

Also, it appears that the SQL Server service is running as the local system
account, but a best practice is to run the SQL Server service as a domain
account and not as local system. You can see that a domain account would be
handier for accessing anything not on the local server. (If I properly
understood your setup.)

Perhaps you could add:

sp_addlinkedsrvlogin @rmtsrvname = 'Linked Server Name'
, @useself = 'FALSE'
, @locallogin = 'sa'
, @rmtuser = 'DatabaseUserName'
, @rmtpassword = 'DatabaseUSerPassword'

FWIW,
RLF

> Hi,
>
[quoted text clipped - 16 lines]
>
> Let me know on what I am doing wrong.

Reply to this Message

Praveen - 28 Feb 2008 20:49 GMT

Russell,

Now I have removed all the logins and had defaulted to DatabaseUserName and
DatabaseUserPassword for all the logins.

What I did was [this is in SQL Server 2005]
1. Right click Linked Servers -> New Linked Server
2. General tab:
Linked Server: Name of the SQL Server. Let us call this as 'Linked Server'
Server type: SQL Server
3. Security tab:
No Local Server Logins were added. Add selected the option "Be made using
this security context" in the list of options. Remote Login and Password are
DatabaseUserName and DatabaseUserPassword.

This setup I believe that regardless of the access account from the source
machine [machine from which i am querying the linked server] it always trys
to connect as DatabaseUserName to the linked server.
This works perfectly fine on any query made to the linked server. I can
access table objects, sysobjects of Linked Server from the Source Machine.

The same queries if I make them in the Stored Procedure and schedule to
execute the SP as a Job doesn't works.

Executed as user: NT AUTHORITY\SYSTEM. Server 'LinkedServerName is not
configured for RPC. [SQLSTATE 42000] (Error 7411). The step failed.

The difference between direct query and the job is that in direct query is
through the Window authentication mode, which is "Domain\UserName" account
whereas through job in Source machine sa account.

With the setup of Linked server I believe it should always go through the
DatabaseUserName and DatabasePassword credentials.

What is the wrong here?

Reply to this Message

Russell Fields - 29 Feb 2008 00:41 GMT

Praveen,

Yes, with a hardwired remote Login and Password I would expect all access
to be through that login. What now? Well, you are now getting the message:
Server 'LinkedServerName is not configured for RPC.

Please look at the Linked Server Properties, the Server Options tab. Make
sure the RPC settings are TRUE.

RLF

> Russell,
>
[quoted text clipped - 36 lines]
>
> What is the wrong here?

Reply to this Message