DTS scheduled job failing

Question

DTS scheduled job failing

jenniferhu46

SSCrazy

Points: 2990
More actions
January 15, 2004 at 10:43 am

#59064

I am in SS2000 and have a scheduled job which refresh our employee's data from a different server every 30 miniutes. I worked fine a week ago, now this job fails everytime it runs, however, I tried to run it manually in dts and it always runs succesfully!!!
The error message is :
DTSRun: Loading... DTSRun: Executing... DTSRun OnStart: DTSStep_DTSExecuteSQLTask_1 DTSRun OnStart: DTSStep_DTSDataPumpTask_3 DTSRun OnError: DTSStep_DTSDataPumpTask_3, Error = -2147217843 (80040E4D) Error string: Login failed for user 'AMERIKING\AMKBDC$'. Error source: Microsoft OLE DB Provider for SQL Server Help file: Help context: 0 Error Detail Records: Error: -2147217843 (80040E4D); Provider Error: 18456 (4818) Error string: Login failed for user 'AMERIKING\AMKBDC$'. Error source: Microsoft OLE DB Provider for SQL Server Help file: Help context: 0 DTSRun OnFinish: DTSStep_DTSDataPumpTask_3 DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_1 DTSRun OnStart: DTSStep_DTSDataPumpTask_1 DTSRun OnStart: DTSStep_DTSDataPumpTask_2 DTSRun OnError: DTSStep_DTSDataPumpTask_1, Error = -2147217843 (80040E4D) Error string: Login failed for user 'AMERIKING\AMKBDC$'. Error source: Microsoft OLE DB . The step failed.
Is it because the login problem? I can't find any user named "AMKBDC$', so what happend?
My boss handed over the problem to me today and just granded me the access to the server, but I don't know where I should start to check......and how can I fix the problem!
Thank you Thank you.

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

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

jenniferhu46 SSCrazy Points: 2990 More actions · Answer 1

I found the Agent log said "Unable to access the password for the 'SQLAgentCmdExec' proxy account", so I went to the Server and reset SQLAgentCmdExec permission by clicked "Rest Proxy Account".

However, the job is still failing.

Robert Davis One Orange Chip Points: 28027 More actions · Answer 2

Perhaps your permissions have changed somehow recently?

Make sure that the owner of the job has SA permissions.

My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url]

nchittenden@ffg-hil.com Valued Member Points: 61 More actions · Answer 3

As the package runs fine straight from DTS, go to services and find the logon for SQLServerAgent and ensure the logon has sufficient rights.

jenniferhu46 SSCrazy Points: 2990 More actions · Answer 4

We are guessing that after a major corruption on Monday, the server appears to have forgotten the password for the account, the sql service will not start unless we can find the password or create a new account. We also found all other servers are working fine with that service account.

Since we don't have the documentation of the password, so can you tell me if there is a way to find out the password, OR how to create/change the account and won't cause a problem?

Thank you.

Robert Davis One Orange Chip Points: 28027 More actions · Answer 5

Do you have any account that works and has SA privelages?

My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url]

jenniferhu46 SSCrazy Points: 2990 More actions · Answer 6

I checked all logins in that server, there's no account works and has sa privelages. I use sp_helplogins, it gave me error message said : cannot insert the value NULL into column 'DefDBName', table 'tempdb.dbo.#tb2_PlainLogins

How can I create another new account and has SA privelages for this job?

Thanks.

Robert Davis One Orange Chip Points: 28027 More actions · Answer 7

Try using sp_password to change the sa account's password by using NULL for the old password. If that doesn't work, try changing yours that way.

Otherwise, the MS solution for changing the SA password when you don't have the correct old one is to reinstall.

My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url]