Remote access not allowed for Windows NT user activated by SETUSER

Discussion:

(too old to reply)

2009-09-22 17:57:02 UTC

Hi

Source server : SQL server 2000 SP4
Destination server: SQL server 2005 sp2

I have setup linked servers. On the source server I have few tables from
which
I need to update a table on destination server. I have to setup a job to do
this everynight.

I have created a job on the source server, and the domain user has rights on
the tables on bothe source and destination server. The job fails with the
error :

Executed as user: SQLMAIL.
Remote access not allowed for Windows NT user activated by SETUSER.
[SQLSTATE 42000] (Error 7410). The step failed.

How to overcome this ?? Help!

Checked this article.
ttp://support.microsoft.com/kb/811031#appliesto
Applied Method 1 "Make the system administrator the owner of the job.) and
ade the owner system administrator, but same error message.

dbdba
ontario, canada

2009-09-22 20:32:01 UTC

Permalink

I am getting this error message

Executed as user: NT AUTHORITY\SYSTEM. Login failed for user
'domainname\servername$'. [SQLSTATE 28000] (Error 18456). The step failed.
--
ontario, canada

Post by db
Hi
Source server : SQL server 2000 SP4
Destination server: SQL server 2005 sp2
I have setup linked servers. On the source server I have few tables from
which
I need to update a table on destination server. I have to setup a job to do
this everynight.
I have created a job on the source server, and the domain user has rights on
the tables on bothe source and destination server. The job fails with the
Executed as user: SQLMAIL.
Remote access not allowed for Windows NT user activated by SETUSER.
[SQLSTATE 42000] (Error 7410). The step failed.
How to overcome this ?? Help!
Checked this article.
ttp://support.microsoft.com/kb/811031#appliesto
Applied Method 1 "Make the system administrator the owner of the job.) and
ade the owner system administrator, but same error message.
dbdba
ontario, canada

Erland Sommarskog

2009-09-22 21:43:08 UTC

Permalink

Post by db
I am getting this error message
Executed as user: NT AUTHORITY\SYSTEM. Login failed for user
'domainname\servername$'. [SQLSTATE 28000] (Error 18456). The step failed.

So what is the service account for SQL Server Agent? Seems like its
Local System. Try using a domain account instead.

--
Erland Sommarskog, SQL Server MVP, ***@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

2009-09-23 13:22:02 UTC

Permalink

Hi Erland

Sql server agent is running under "Local System account". Its a sql server
2000 production server with multiple jobs and management plan running on it.
If I change it to run under a domain account, Will it afftect any existing
jobs or management plans.
thanks for help
--
ontario, canada

Post by Erland Sommarskog

Post by db
I am getting this error message
Executed as user: NT AUTHORITY\SYSTEM. Login failed for user
'domainname\servername$'. [SQLSTATE 28000] (Error 18456). The step failed.

So what is the service account for SQL Server Agent? Seems like its
Local System. Try using a domain account instead.
--
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

2009-09-23 13:54:01 UTC

Permalink

I am getting following message on the destination 2005 server.

Login failed for user 'domain\sourceservername$'. [CLIENT: ipaddress]
Error: 18456, Severity: 14, State: 11.
--
ontario, canada

Post by db
Hi Erland
Sql server agent is running under "Local System account". Its a sql server
2000 production server with multiple jobs and management plan running on it.
If I change it to run under a domain account, Will it afftect any existing
jobs or management plans.
thanks for help
--
ontario, canada

Post by Erland Sommarskog

Post by db
I am getting this error message
Executed as user: NT AUTHORITY\SYSTEM. Login failed for user
'domainname\servername$'. [SQLSTATE 28000] (Error 18456). The step failed.

Russell Fields

2009-09-23 15:34:21 UTC

Permalink

The state of the login error tells you what is wrong. For example:
Error: 18456, Severity: 14, State: 11.
Valid login but server access failure; Default Database access failure. Or
Initial database connection failure in connection string.

Some links that discuss this:
http://sqldbpool.wordpress.com/2009/04/24/error-18456-severity-14-login-failed-states/
http://blogs.msdn.com/sql_protocols/archive/2006/02/21/536201.aspx
http://www.eraofdata.com/blog/2009/01/loginfailures/

Other thoughts:

1. It would be helpful if you scripted out your linked server definitions.
Right click on the linked server in SSMS Object Explore and choose Script
Linked Server As... You will need to change the names of a few things, but
that is fine. It may well be that the definition of your linked server
login might be the root of the problem.

2. To troubleshoot you should look at the login definitions on both servers
and check default databases, server roles, and database permissions. Also,
are both servers running under the same administrator account or different
accounts?

3. If I understand correctly, you are --pushing-- data from SQL 2000 to SQL
2005. Is that correct? Have you considered --pulling-- the data from SQL
2000 into SQL 2005 instead. In that case the linked server and the job
would run on the 2005 server instead of the 2000 server.

RLF

Post by db
I am getting following message on the destination 2005 server.
Login failed for user 'domain\sourceservername$'. [CLIENT: ipaddress]
Error: 18456, Severity: 14, State: 11.
--
ontario, canada

Post by Erland Sommarskog

Post by db
I am getting this error message
Executed as user: NT AUTHORITY\SYSTEM. Login failed for user
'domainname\servername$'. [SQLSTATE 28000] (Error 18456). The step failed.

So what is the service account for SQL Server Agent? Seems like its
Local System. Try using a domain account instead.
--
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

2009-09-25 15:11:02 UTC

Permalink

Hi Russel

When I try to pull data from 2000 to 2005 from 2005 I get error message :
==================
Executed as user: domainname\SYSTEM. ... Error: 2009-09-24 Description:
SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code:
0x80040E4D. An OLE DB record is available. Source: "Microsoft SQL Native
Client" Hresult: 0x80040E4D Description: "Communication link failure". An
OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult:
0x80040E4D Description: "TCP Provider: An existing connection was forcibly
closed by the remote host. ". An OLE DB record is available. Source:
"Microsoft SQL Native Client" Hresult: 0x80040E4D Description: "Login
failed for user 'domainname\servername$'.". End Error Error: 2009-09-24
21:10:02.56 Code: 0xC020801C Source: D... The package execution
fa... The step failed.
================

When I try to push data from 2000 server , I get this on the 2005 error log:

Error: 18456, Severity: 14, State: 11.
===================

Sql server agent on 2000 server is running under "local system".
Sql server agent on 2005 server is running under "local system".

On 2005 (sql server integration services is running under "network services"
and sql server active directory helper is disabled)
=================

I have checked all the permissions and they look fine.
--
ontario, canada

Post by db
Error: 18456, Severity: 14, State: 11.
Valid login but server access failure; Default Database access failure. Or
Initial database connection failure in connection string.
http://sqldbpool.wordpress.com/2009/04/24/error-18456-severity-14-login-failed-states/
http://blogs.msdn.com/sql_protocols/archive/2006/02/21/536201.aspx
http://www.eraofdata.com/blog/2009/01/loginfailures/
1. It would be helpful if you scripted out your linked server definitions.
Right click on the linked server in SSMS Object Explore and choose Script
Linked Server As... You will need to change the names of a few things, but
that is fine. It may well be that the definition of your linked server
login might be the root of the problem.
2. To troubleshoot you should look at the login definitions on both servers
and check default databases, server roles, and database permissions. Also,
are both servers running under the same administrator account or different
accounts?
3. If I understand correctly, you are --pushing-- data from SQL 2000 to SQL
2005. Is that correct? Have you considered --pulling-- the data from SQL
2000 into SQL 2005 instead. In that case the linked server and the job
would run on the 2005 server instead of the 2000 server.
RLF

Post by Erland Sommarskog

Post by db
I am getting this error message
Executed as user: NT AUTHORITY\SYSTEM. Login failed for user
'domainname\servername$'. [SQLSTATE 28000] (Error 18456). The step failed.

So what is the service account for SQL Server Agent? Seems like its
Local System. Try using a domain account instead.
--
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

2009-09-25 16:13:04 UTC

Permalink

I also get this error message while trying to run a job from 2005 (Copy table
from 2000 server to 2005 server).

SQLServerAgent Error: Request to stop job jobname (from User
domain\username) refused because the job is not currently running. (Microsoft
SQL Server, Error: 22022)
--
ontario, canada

Post by db
Hi Russel
==================
0x80040E4D. An OLE DB record is available. Source: "Microsoft SQL Native
Client" Hresult: 0x80040E4D Description: "Communication link failure". An
0x80040E4D Description: "TCP Provider: An existing connection was forcibly
"Microsoft SQL Native Client" Hresult: 0x80040E4D Description: "Login
failed for user 'domainname\servername$'.". End Error Error: 2009-09-24
21:10:02.56 Code: 0xC020801C Source: D... The package execution
fa... The step failed.
================
Error: 18456, Severity: 14, State: 11.
===================
Sql server agent on 2000 server is running under "local system".
Sql server agent on 2005 server is running under "local system".
On 2005 (sql server integration services is running under "network services"
and sql server active directory helper is disabled)
=================
I have checked all the permissions and they look fine.
--
ontario, canada

Post by Erland Sommarskog

Post by db
I am getting this error message
Executed as user: NT AUTHORITY\SYSTEM. Login failed for user
'domainname\servername$'. [SQLSTATE 28000] (Error 18456). The step
failed.

So what is the service account for SQL Server Agent? Seems like its
Local System. Try using a domain account instead.
--
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Erland Sommarskog

2009-09-25 21:23:13 UTC

Permalink

Post by db
Error: 18456, Severity: 14, State: 11.

And Russell explained to you that this means that the you are trying
to connect to database that does not exist. Which means that either
the connection string or the linked server definition is incorrect.
Did you check this?

Post by db
Sql server agent on 2000 server is running under "local system".
Sql server agent on 2005 server is running under "local system".
On 2005 (sql server integration services is running under "network
services" and sql server active directory helper is disabled)

Local System or Network Service usually spells trouble. Switch to
using a domain account instead.