Execute a SSIS Package using a SQL Job Failed

Question

Execute a SSIS Package using a SQL Job Failed

Paul Hernández

SSCarpal Tunnel

Points: 4961
More actions
August 27, 2013 at 4:08 am

#147869

Hi Folks,
I'm trying to execute a SSIS Package from a SQL Job in a server A.
The package is set to "do not save sensitive"
The package is stored in the Server B.
The Config DB is in the Server B.
I created an Account, let's say SSISAccount to run the jobs.
The server A and server B are both in the same domain, MyDomain
I created a Login for MyDomain\SSISAccount and mapped to the ConfigDB
I created an user direct in the ConfigDB and gave all of the permissions
Even when I did that, I´m always getting the error "Login failed for user MyDomain\SSISAccount ........"
What could be wrong?
I know the problem is the User running the package cannot acces to the Config DB in the other server using windows authentication, but I don't know how to solve it.
Any comment will be appreciated,
Kind Regards
Paul Hernández

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 1

Do you use a proxy in the SQL job to specify the user MyDomain\SSISAccount?

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Paul Hernández SSCarpal Tunnel Points: 4961 More actions · Answer 2

Hi Koen,

Thanks for your response

I´m not using any proxy account, instead I set the account for the Agent Service using the SQL Server Configuration Manager:

If I want to have access to resources in another server of the same domain, is a proxy account the only way?

Kind Regards

Paul Hernández

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 3

Can you post the entire error?

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Paul Hernández SSCarpal Tunnel Points: 4961 More actions · Answer 4

Hi Koen,

of course, here is the message from the Job History:

Message
Executed as user: DEFACTO-PRODUKT\SQLSrvNUESSISSQL1. ...r 64-bit Copyright (C) Microsoft Corporation 2010. All rights reserved. Started: 11:16:35 Error: 2013-08-28 11:16:40.80 Code: 0xC0202009 Source: KK_ATOS_KUNDE_RUECK Connection manager "SSISConfigs" 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 Server Native Client 10.0" Hresult: 0x80040E4D Description: "Login failed for user 'DEFACTO-PRODUKT\SQLSrvNUESSISSQL1'.". End Error Error: 2013-08-28 11:16:45.31 Code: 0xC0202009 Source: KK_ATOS_KUNDE_RUECK Connection manager "SSISConfigs" 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 Server Native Client 10.0" Hresult: 0x80040E4D Description: "Login failed for user 'DEFACTO-PRODUKT\SQLSrvNUESSISSQL1'.". End Error Error: 2013-08-28 11:16:49.82 Code: 0xC0202009 Source: KK_ATOS_KUNDE_RUECK Connection manager "SSISConfigs" 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 Server Native Client 10.0" Hresult: 0x80040E4D Description: "Login failed for user 'DEFACTO-PRODUKT\SQLSrvNUESSISSQL1'.". End Error Error: 2013-08-28 11:16:54.33 Code: 0xC0202009 Source: KK_ATOS_KUNDE_RUECK Connection manager "SSISConfigs" 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 Server Native Client 10.0" Hresult: 0x80040E4D Description: "Login failed for user 'DEFACTO-PRODUKT\SQLSrvNUESSISSQL1'.". End Error Error: 2013-08-28 11:16:58.84 Code: 0xC0202009 Source: KK_ATOS_KUNDE_RUECK Connection manager "SSISConfigs" 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 Server Native Client 10.0" Hresult: 0x80040E4D Description: "Login failed for user 'DEFACTO-PRODUKT\SQLSrvNUESSISSQL1'.". End Error Error: 2013-08-28 11:17:03.36 Code: 0xC0202009 Source: KK_ATOS_KUNDE_RUECK Connection manager "SSISConfigs" 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 Server Native Client 10.0" Hresult: 0x80040E4D Description: "Login failed for user 'DEFACTO-PRODUKT\SQLSrvNUESSISSQL1'.". End Error Error: 2013-08-28 11:17:06.62 Code: 0xC0202009 Source: KK_ATOS_KUNDE_RUECK Connection manager "DB2_DWHP" Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available. Source: "Microsoft DB2 OLE DB Provider" Hresult: 0x80040E14 Description: "The user does not have the authority to access the host resource. Check your authentication credentials or contact your system administrator.". End Error Error: 2013-08-28 11:17:06.62 Code: 0xC020801C Source: Transfer Kunde Aenderungen Insert Records Kunde Rueck [1876] Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "DB2_DWHP" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed. End Error Error: 2013-08-28 11:17:06.62 Code: 0xC0047017 Source: Transfer Kunde Aenderungen SSIS.Pipeline Description: component "Insert Records Kunde Rueck" (1876) failed validation and returned error code 0xC020801C. End Error Error: 2013-08-28 11:17:06.62 Code: 0xC004700C Source: Transfer Kunde Aenderungen SSIS.Pipeline Description: One or more component faile... The package execution fa... The step failed.

Paul Hernández

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 5

Can you try with a proxy (just to make sure)?

Another cause might be if the user you created in the SSISConfigs DB is not correctly mapped to the domain account.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Paul Hernández SSCarpal Tunnel Points: 4961 More actions · Answer 6

Hi,

if I create a credential with my user (with my user name and domain password) it works.

If I used the account, that was created by the admins it failed. The problem is I don't know the password of this windows account. I think this could be the problem. After I talk with the admins I will try again and give some feedback.

Paul Hernández

cafescott SSCarpal Tunnel Points: 4724 More actions · Answer 7

The typical problem with using a specific user account is that network admins often require a password change every X days. That would mean you have to change it on the server hosting your integration services for that interval. Unless you want to change that password repeatedly it is often a good idea to try to get the package to run using the SQL Agent Service Account.