November 11, 2010 at 7:38 am
Hi,
I have created the SSIS package with windows authentication mode to retrieve the data from source server to destination server. Then i have created the job with this SSIS package with same windows account as owner of the job which i used to created the SSIS package.
But while running the job its giving the following details
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: "Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.". End Error Error: 2010-11-11 09:23:22.36 Code: 0xC020801C Source: Data Flow Task Source - Query [1] Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.
The AcquireConnection method call to the connection manager "SourceConnectionOLEDB" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.... The package execution fa... The step failed.
Information About the SSIS package:
Mode of authentication : windows
Storage Mode : File
Encription: Encrypt sesitive data with password.
Windows account is domain account and through the same account i can able to access the both source and destination server. that windows account has sysadmin rights on both server.
Can you let me know how to solve this issues ?
Thanks in Advance.
November 11, 2010 at 8:05 am
It's a multi-step AD authorization error.
When you're running it, are you running it automatically, are you running it manually from a workstation, or are you running it while using remote-desktop to the server?
If you want more data, Bing/Google "NT AUTHORITY\ANONYMOUS LOGIN" and "kerberos". Most likely, you won't need to do anything complex, but those will give you the data about how it works.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 12, 2010 at 4:43 am
GSquared (11/11/2010)
It's a multi-step AD authorization error.When you're running it, are you running it automatically, are you running it manually from a workstation, or are you running it while using remote-desktop to the server?
If you want more data, Bing/Google "NT AUTHORITY\ANONYMOUS LOGIN" and "kerberos". Most likely, you won't need to do anything complex, but those will give you the data about how it works.
Thanks for your reply.
i have verified with below query whether sql server trying to authenticate with kerberos but it did not .
select session_id,most_recent_session_id,text,auth_scheme,* from sys.dm_exec_connections
CROSS APPLY sys.dm_exec_sql_text(most_recent_sql_handle) AS sqltext
Auth Scheme shows as NTLM for the session in which i ran the SSIS package.
November 12, 2010 at 5:41 am
Hi,
Who is the Job owner?
If it is not sa then change it to sa & try!
Jobs runs under Sql Agent Service Account!
Regards,
November 12, 2010 at 9:09 am
Sounds like you are using the local system account to run the SQL agent but first lets check. Open the job from the SQL agent, rc properties, go to steps, edit and that should open a new window and in there you will see a 'Run as:' if that says SQL Server Agent Service Account it will be using the sql agent service account to authenticate. Taking a guess that your trying to import/export to a different server? At any case you will need to run the SQL agent service under an account which has permissions to read/write what ever is in the job steps.
November 12, 2010 at 11:09 am
Create a credential with the user account that created the package and then create an Agent SSIS proxy with that credential and then select the job step to run as the SSIS Proxy.
Thank You,
Best Regards,
SQLBuddy
November 12, 2010 at 12:06 pm
November 14, 2010 at 4:07 pm
xparmanand (11/12/2010)
Hi,Who is the Job owner?
If it is not sa then change it to sa & try!
Jobs runs under Sql Agent Service Account!
Regards,
Owner of the job has the sysadmin rights on the server.
November 14, 2010 at 4:12 pm
kyleheath33 (11/12/2010)
Sounds like you are using the local system account to run the SQL agent but first lets check. Open the job from the SQL agent, rc properties, go to steps, edit and that should open a new window and in there you will see a 'Run as:' if that says SQL Server Agent Service Account it will be using the sql agent service account to authenticate. Taking a guess that your trying to import/export to a different server? At any case you will need to run the SQL agent service under an account which has permissions to read/write what ever is in the job steps.
Yes. you are right. am using the ssis package to export record from another. In the job have set run as sql server agent services. Sql server agent services is running on the local systerm account.
After changing account of the database and agent services account as domain user account its running successfully.
November 14, 2010 at 4:13 pm
pavan_srirangam (11/12/2010)
Did you check that you are not using sql server authentication?
Yes i ensured that it using the windows authentication.
November 14, 2010 at 4:15 pm
sqlbuddy123 (11/12/2010)
Create a credential with the user account that created the package and then create an Agent SSIS proxy with that credential and then select the job step to run as the SSIS Proxy.Thank You,
Best Regards,
SQLBuddy
why do i want to create proxy account eventhough i am running the job with system admin account rights ?
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply