Home / server / Questions / 42933
Accepted
Robin
Asked: 2009-07-21 03:54:36 +0800 CST

SQL Server Maintenance Plan failing - Failed to acquire connection "Local server connection"

  • 772

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.

sql-server-2005 security maintenance

4 Answers

  1. Best Answer

    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.

    • 3

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

    • 1

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

    • 0

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

    • -1