Solved

SQL server job failed

Posted on 2009-05-13
9
1,592 Views
Last Modified: 2013-11-10
Hi,

I have recently started using SQL Server Package Confg's I have a Package that has multiple Execute Packages Task and was running fine with the Confg until yesterday.... But Now it gives Error as below. The Same Connection is being used by 6 Packages but only 3 of them fail.... and when i execute the package from BIDS or MSDB they run without  any errors.... Can anyone please help me

 Source: "OraOLEDB"  Hresult: 0x80004005  Description: "ORA-12638: Credential retrieval failed".  End Error  Error: 2009-05-13 20:18:45.28     Code: 0xC020801C     Source: Copy data Task [3096]     Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.  The AcquireConnection method call to the connection manager "SQL-Oracle" 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: 2009-05-13 20:18:45.28     Code: 0xC004701A     Source: Copy data Task SSIS.Pipeline     Description: component "Extract Data" (3096) failed the pre-execute phase and returned error code 0xC020801C.  End Error  Error: 2009-05-13 20:18:45.81     Code: 0xC0047062     Source: Imports latest configuration files  (Oracle) [1]     Description: System.Data.OleDb.OleDbException: ORA-12638: Credential retrieval failed     at System.Data.OleDb.OleDbConnectionInternal..ctor(OleDbConnectionString constr, OleDbConnection connection)     at System.Data.OleDb.OleDbConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject)     at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup)     at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)     at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)     at System.Data.OleDb.OleDbConnection.Open()     at Microsoft.SqlServer.Dts.Runtime.ManagedHelper.GetManagedConnection(String assemblyQualifiedName, String connStr, Object transaction)     at Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSConnectionManager100.AcquireConnection(Object pTransaction)     at Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter.AcquireConnections(Object transaction)     at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostAcquireConnections(IDTSManagedComponentWrapper100 wrapper, Object transaction)  End Error  Error: 2009-05-13 20:18:45.81     Code: 0xC0047017     Source: Imports latest configuration files SSIS.Pipeline     Description: component "(Oracle)" (1) failed validation and returned error code 0x80004005.  End Error  Error: 2009-05-13 20:18:45.81     Code: 0xC004700C     Source: Imports latest configuration files from XXX into XXXX table SSIS.Pipeline     Description: One or more component failed validation.  End Error  Error: 2009-05-13 20:18:45.81     Code: 0xC0024107     Source: Imports latest configuration files from XXX into XXXX table      Description: There were errors during task validation.  End Error  Error: 2009-05-13 20:18:46.09     Code: 0xC0047062     Source: Import data from XXYY to  YYYY (Oracle) [1]     Description: System.Data.OleDb.OleDbException: ORA-12638: Credential retrieval failed     at System.Data.OleDb.OleDbConnectionInternal..ctor(OleDbConnectionString constr, OleDbConnection connection)     at System.Data.OleDb.OleDbConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject)     at System.Data.ProviderBase.DbConnect...  The package execution fa...  The step failed.
0
Comment
Question by:danny1620
  • 4
  • 3
  • 2
9 Comments
 
LVL 13

Expert Comment

by:St3veMax
ID: 24379547
When you run the package; it runs under your credentials.

Do the 6 packages run simultaniously (sp) ? If so; there may be issues with the number of concurrent connections.

HTH
0
 

Author Comment

by:danny1620
ID: 24379608
Nope they run one after the other... they are setup to run On Completion...
Either I run the Package from BIDS?MSDB/SQL Agent  they take the configurations from the Config File Table Isn't It?.... I am an ADMIN and have full access to all 3 ..
0
 
LVL 13

Accepted Solution

by:
St3veMax earned 250 total points
ID: 24379739
When they are scheduled; I believe they use the current user context to access the data; so could be worth a check there that nothing's happened at the source in relation to permissions.
0
 

Author Comment

by:danny1620
ID: 24379998
As I said.. there is no Problem bcz I have 6 package that use the same Connection and only 3 of those fail...So, it not the problem with Permissions
0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 250 total points
ID: 24380789
>>So, it not the problem with Permissions<<
Unfortunately that is the case.  The error message is pretty explicit.  What is happening is that the startup account for the SQL Server Agent service does not have the appropriate permissions to all the resources it requires or you are using a mapped drive.
0
 

Author Comment

by:danny1620
ID: 24381334
I guess, you are right... and this started  from the day after i changed my network password... I have noticed the same when i tried to change the account settings on the SQL Server Agent .... What i dont understand is why is it giving me such a wierd error.... It is not allowing me to change the A/c settings for the SQL Server Agent.. The Package is running only when it uses my Credentials...  Any Suggestions on what to do...
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24384394
You are going to have to find some way to update the startup account used by the SQL Server Agent.  That does require restarting the service.
0
 

Author Comment

by:danny1620
ID: 24385583
You mean restart the SQL server ... If you meant that .. I have actualy tried and thats when I came to know that its the Problem with permissions on the A/c .. I was actually unable to make it use Network Services/Local services A/C ..So i had to manually add those two A/c's to the Prog Files/MSSQL after which i was able to restart SQL Server with Network Services A/c but when i use the same for SQL Agent  and then run the job it fails....  Dont know what to do..???
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24387132
>>You mean restart the SQL server ... If you meant that <<
No.  I was referring to the SQL Server Agent.  And no, when modifying the account you do not have to restart the server, just the SQL Server Agent service.
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now