I've got a recently installed sql 2005 database server (an upgrade from 2000 on the same machine), I'm trying to get maintenance plans set up to reindex etc but they are constantly failing. This is the message I get, I presume some permission for some user somewhere is wrong but not really sure where I should be looking.

Executed as user: 194709-DB2\SYSTEM. ... 9.00.4035.00 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 12:45:34 Error: 2009-07-20 12:46:05.42
Code: 0xC00291EC Source: {BDA7B017-98D2-41A4-864E-49A5A9B39855} Execute SQL Task Description: Failed to acquire connection "Local server connection". Connection may not be configured correctly or you may not have the right permissions on this connection. End Error Warning: 2009-07-20 12:46:05.42 Code: 0x80019002 Source: OnPreExecute
Description: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors. End Warning Error: 2009-07-20 12:46:05.44 Code: 0xC0024104
Source: Reorganize Index Task
De... The package execution fa... The step failed.

  • When you installed sql 2005, did it create a new instance or did it use the same default instance name? – jinsungy Jul 20 '09 at 16:14
  • I didn't actually do the installation, but it's running under an instance name of MSSQL2005 – Robin Jul 21 '09 at 9:29
up vote 3 down vote accepted

Creating the jobs through sa from my machine connecting to the remote SQL Server instance doesn't work. However if I RDP onto the machine and use my Windows credentials to set up the jobs up it works.

I didn't try it, but connecting to the sql server instance through Management Studio, using windows credentials would probably have worked as well.

Check the connection within the package to make sure it is connecting to the correct server. I've had a different scenario (instance fail-over) produce the same error message - i.e. on the packages running on the secondary server were trying to run against the original server name.

  • This is our problem, but how do I fix it? – kralco626 Jul 21 '15 at 17:18

Another suggestion, open up your maintenance plan and select the connection from "Manage Connections..." (on the toolbar) and make sure you have given the correct credentials.

It will failed even you configured it properly through the agent account itself. Sometimes in production scenarios many other jobs are also running which acquire the same connection on the same server. Due to this both jobs collide and will fail sometimes...

Your Answer

 

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.