Re: Remote access not allowed for Windows NT user activated by SET

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

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


"Russell Fields" wrote:

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

"db" <db@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:D8DC973B-C5C2-4A92-A51D-BD6E041A87D4@xxxxxxxxxxxxxxxx
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


"db" wrote:

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


"Erland Sommarskog" wrote:

db (db@xxxxxxxxxxxxxxxxxxxxxxxxx) writes:
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, esquel@xxxxxxxxxxxxx

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




.

Relevant Pages

  • Re: SQL account rights
    ... Please advice what is the best, suitable rights rather than domain admin ... issues, such as a server that might have IIS running on the same machine, ... applicable to SQL 2000 environment, ... files, or backups, make sure that the service account has Full ...
    (microsoft.public.sqlserver.security)
  • RE: MP Install issue
    ... Where in the installation are you talking about specifying the account rather ... > MPDB ERROR - CONNECTION PARAMETERS ... > SQL Server Name: servername ... > with a trusted SQL Server connection. ...
    (microsoft.public.sms.setup)
  • Re: SQL Express Fails with Hardware Error
    ... The LocalSystem account is a built-in account, ... which the SQL Service runs. ... MCSE, CCEA, Microsoft MVP - Terminal Server ... Minimum Hardware Requirement (Warning) ...
    (microsoft.public.sqlserver.setup)
  • Re: SCCM with a remote SQL instance problems (IT IS NOT A WARNING)
    ... PreReq check is not a WARNING it is a FAILURE. ... account the run the SQL Server Service on the server, Domain Memberships, AD ...
    (microsoft.public.sms.installer)
  • Re: Distributed Query - Linked Servers?
    ... In the case of SQL style accounts, it doesn't seem very secure to me, because the password will be transmitted over the network. ... it should also be able to work by setting up a credential and proxy account on Server1. ... In this case it could work with NTLM authentication. ... we just need to verify that the 2 sql server instances are under sql server and windows authentication mode and the sql account you use has the permission on the 2 sql server instances. ...
    (microsoft.public.sqlserver.server)