0

My maintenance plans broke all of a sudden, after a Windows update. My backup routines cannot get access to the server any longer. I have tried with different user accounts, changing the connections in the "Manage Connection" window, in the maintenance plan but nothing has worked!

Executed as user: NT\SQLSERVERAGENT.

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

Jayasurya Satheesh
  • 7,826
  • 3
  • 22
  • 39
André Snede
  • 9,899
  • 7
  • 43
  • 67

1 Answers1

0

The problem was that a colleague of mine had renamed the servers HostName. Apparently that screws up the Maintenance plans, among other things.

The "Local Server Connection" when managing the connections in the Maintenance Plan designer, was set to .\MSSQLSERVER,1433. Which apparently didn't work after changing the servers Hostname.

I fixed the problem by deleting the Maintenance plans and making new ones, checking that the "Local Server Connection" was correctly set as "."

André Snede
  • 9,899
  • 7
  • 43
  • 67
  • 1
    The only difference between ".\MSSQLSERVER,1433" and "." is that the first one is constrained to use tcp\ip while the second can use Shared Memory as well, so your problem is related to TCP\IP, maybe your update turned on windows firewall that was turned off previously? – sepupic Jan 15 '18 at 14:12
  • @sepupic I have other servers connecting to the server as well, so I don't think the firewall has kicked in. Maybe the naming of the server? – André Snede Jan 15 '18 at 20:46
  • Did you check that tcp\ip is enabled for this server? Other servers can use named pipes – sepupic Jan 16 '18 at 06:38
  • Actually Named Pipes are disabled, and TCP/IP is enabled. I just talked to my colleague and he told me he renamed the servers hostname, that's what screwed it up. Apparently maintenance plans cannot handle that. – André Snede Jan 16 '18 at 10:14
  • In both cases you used "." as hostname, so this part was not changed – sepupic Jan 16 '18 at 10:16
  • @sepupic I agree, but what I could tell from the other forums I looked at, the SQL server doesn't handle hostname changes well. Especially not with maintenancen plans. Even though it shows as ".\MSSQLSERVER", behind the scenes it has a reference to the old hostname (from my understanding), therefor you have to recreate the maintenance plans. And I just wanted to share the solution to the problem I spend 4 hours trying to solve. – André Snede Jan 16 '18 at 11:12
  • ".\MSSQLSERVER" is NOT a hostname, moreover, in the connection string like ".\MSSQLSERVER,1433" , "\MSSQLSERVER" is simply ignored because there is EXPLICIT port number. The host was "." and is still "." – sepupic Jan 16 '18 at 11:16
  • @sepupic I am aware of that. As I said, even though it shows as .\MSSQLSERVER, apparently there is some reference behind the scenes to the OLD hostname. I understand what you are saying, but I am not explaining myself well enough apparently. The maintenance plans, can break if you change hostname. This can be fixed by recreating them, as the only other way to fix the existing ones is to run a big script: https://dba.stackexchange.com/questions/55090/how-to-changes-or-update-local-server-connection-in-maintenance-plan-job – André Snede Jan 16 '18 at 12:50