Solved

SQL server job failed

Posted on 2009-05-13
9
1,595 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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 

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
 
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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
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

806 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