How come my SQL Server Job won't run?

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?
Thanks,
Jeff
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
"Jeff Stroope" wrote:

> 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?
> --
> Thanks,
> Jeff
|||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
"Jeff Stroope" <JeffStroope@.discussions.microsoft.com> wrote in message
news:D5B0F3ED-5538-4E89-8A97-E985003A981F@.microsoft.com...
> 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?
> --
> Thanks,
> Jeff
|||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.
Thanks,
Jeff
"Ben Nevarez" wrote:
[vbcol=seagreen]
> 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
>
> "Jeff Stroope" wrote:
|||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.
Thanks,
Jeff
"Russell Fields" wrote:

> 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
>
> "Jeff Stroope" <JeffStroope@.discussions.microsoft.com> wrote in message
> news:D5B0F3ED-5538-4E89-8A97-E985003A981F@.microsoft.com...
>
>
|||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
"Jeff Stroope" <JeffStroope@.discussions.microsoft.com> wrote in message
news:FA4BED9F-1877-4B01-ADA5-3F1B49027EC9@.microsoft.com...[vbcol=seagreen]
> 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.
> --
> Thanks,
> Jeff
>
> "Russell Fields" wrote:
|||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.
|||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
"Praveen" <Praveen@.discussions.microsoft.com> wrote in message
news:67D35848-9BB7-49E8-B128-B26E040F7D8E@.microsoft.com...
> 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.
>
|||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?
|||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
"Praveen" <Praveen@.discussions.microsoft.com> wrote in message
news:2C20479D-7A59-4910-8647-A334E8EF4A95@.microsoft.com...
> 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?
>