October 9, 2008 at 5:23 pm
I had a massive outage a couple weeks ago on SQL 2005 x64 sp2 running on 64 bit Clustered SQL 2005 sp2 on Windows 2003 R2 x64 sp2.
Long story short, I had to use the ClusterRecovery.exe utility to restore a checkpoint registry to get the 3 instances of SQL I have on the server up and running.
After a long and frustrating day I got the 3 instances up and running and the SQL Server Browser service working, so the clients could connect and all the apps were working. It's been a few weeks now and the only issue seems to be something related to SQL's associated .exe's not being able to establish connectivity to the server. The Maintenance Plans (using sqlmaint.exe) and Database Mail (using DatabaseMail90.exe) no longer work.
The error I get from the failed Maintenance Plans (and I have created new, very simple plans for testing/verifying the failure) is:
Message
Executed as user: AMC\mssql. ... Version 9.00.3042.00 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 4:49:16 PM Error: 2008-10-09 16:49:33.48 Code: 0xC00291EC Source: {230CB310-C98D-44D1-A40D-280DE97DE0EB} 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: 2008-10-09 16:49:33.48 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: 2008-10-09 16:49:48.06 Code: 0xC0024104 Source: Back Up Database Task ... The package execution fa... The step failed.
I know that the account has the right permissions (the maintenance plans ran fine before the outtage), it is an admin on the box and the SQL Agent runs under that account and other jobs all work fine (I have manually scripted the backups until I can get this issue resolved).
I have restarted all services and rebooted boxes numerous times and the error remains. If I create a maintenance plan with a connection ot another server, I can see the databases on the other server while creating the plan, but get the same error.
If I go to another server and create a maintenance plan to run against the one that's failing, that works fine, so it looks like the issue is confined to just the SQL cluster and the .exe's for SQL that run on that server, anyone have any insight on what might be causing this??
Thanks,
Chris
November 19, 2008 at 7:57 am
Hi,
I'm having the same issue on a cluster and I think it has to do with the fact that you cannot connect to a cluster using 'localhost'. Try logging onto your server and connecting with ssms using localhost and this same error will happen. There is also a connection in the maint plan that cannot be deleted called local host.
I fixed this by logging onto the sever as sqlagent and creating the job. I suspect I could have fixed it by opening the SSIS changing the ProtectionLevel property to ServerStorage.
October 22, 2010 at 10:01 am
Hi All,
I am also facing the same issue, can anybody help on this.
October 22, 2010 at 10:28 am
Hi All,
I am transfering the maintenance plans and its related job (to run this maintenance plan) from SQL 2005 server (64-bit) to SQL server 2005 (32-bit). version is same and SP3 applied at both servers. I transered the maintenance plan successfully on destination server. But when i am trying to run that job then i am getting following error:-
executed as user: DMN1\srvDSDBDevSQLMSSQL. ...4035.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 8:38:42 AM Error: 2010-10-22 08:38:43.51 Code: 0xC00291EC Source: {4F885F28-8649-4E1D-9373-B43889D2BC82} 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: 2010-10-22 08:38:43.51 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: 2010-10-22 08:38:43.58 Code: 0xC0024104 Source: Back Up Database (Full) ... The package execution fa... The step failed.
i opened maintenance plan and saw its manage connection then its showing the source server name in its local connection; which i think should me destination server name.
Any help on this greatly appreciated.
November 24, 2010 at 6:47 am
Hi All,
I had the same issue with a server that was a clone of an existing server.
It had the same symptoms, the jobs failing with "Failed to acquire connection "Local server connection". Connection may not be configured correctly".
When I opened the Maintenance plan and viewed the "Manage connections", it replied with the name of the old server. And as the SQL agent is running with the local account, it can not connect to the original server.
Workaround (in my case) was to recreate the maintenance plans again. I verified the connections and now the "Local Server Connection" was pointing to the correct server. After that it worked fine at my side.
So it all comes down to the SQL Agent not being able to connect to the database.
In my case I wasn't lucky because the server didn't had the integration services installed, else I could have followed the instructions from Microsoft: http://support.microsoft.com/kb/918760
Another was is to export the Maintenance task to an XML file, change the Connection settings and import the XML again.
Greetings,
Vramby
December 20, 2010 at 2:14 am
Thanks for the maintenance plan export/edit/re-import suggestion Peter. Fixed the problem for me with minimal effort. Happy Christmas!
January 18, 2011 at 8:25 am
Hi,
just to add to this thread, I have recently had the same error message as above, however the problem was caused by the login that ran the SQL Server Agent service has a default database that I had taken offline. Once I changed it to master, the maintenance plans worked.
Damn SQL Server and it's "generic error messages!" 😉
qh
February 7, 2012 at 4:44 am
Hi,
I know this thread is many years ago but hoping someone came up with the solution to this problem as i am facing the same issue. Ie after a cluster issue (our offshore support team deleted our sql cluster resources!!!), I have problems with .exe on my sql instance. Ie the maintenance plans and databasemail no longer work.
What was the solution to this issue?
I have tried recreating the maintplans, exporting them, recreating database mail profiles, all to no avail.
any advice will be appreciated.
Thanks
Lyn
February 7, 2012 at 9:19 am
The solution to my problem, years ago, was that the Registry for both machines was jacked up, specifically in the WOW branches, extra keys had been added and by removing those it started working again. I had a good cluster at our DR site and was able to identify a difference between the good registry keys and the bad)
Having spent over 100 hours on the phone with Microsoft Support (which was a bargain at $250) I would strongly recommend spending a little time trying to fix, and if you can't then rebuild it from scratch, it's not worth spending weeks trying to fix like I did (but I'm stubborn)
April 15, 2014 at 5:05 pm
I had this problem also but because the server was virtual and still had the connection details for the server it was cloned from in the maintenance plan connection details. Removed it and readded with the local server and all fine.
November 7, 2022 at 8:50 am
Similar problem with my db, after upgrade to sql2017. The maintenance plan failing with the same error as mentioned in the above posts. My daily backup schedule refuse to work, executed as SQL Server agent service account.
Tried everything, read a lot of web sites but no success.
In the end, God help me to this solution:
The problem in my case, was the "Log on the server" check as "Use Windows Authentication".
After I select the "Sql Server Authentication", and use "sa" user, the backup job was executed with success.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply