Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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.

share|improve this question
    
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

4 Answers 4

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

share|improve this answer

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.

share|improve this answer

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.

share|improve this answer

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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