SQL Server Agent – Troubleshooting Logon Issues to MyServer

jobssql serversql-server-agent

I'm struggling to get our SQL Server Agent to run a maintenance plan, which used to work in the past. (This is on SQL Server 2008 R2.)

The original problem was that the SQL Server Agent would not start, because it tried to connect to, let's call it "MyServer," which resulted in what appeared to be a network name resolution error. We then changed the alias from "MyServer" to "." (SSMS > SQL Server Agent > right-click Properties > Connection > Alias local host server). This fixed it and we can now start the agent.

The second and current problem is that when I execute a maintenance plan, then the log for the SQL Agent (not the log for the maintenance plan) shows a similar error, that it cannot log on to the server "MyServer." (See below for the exact error.) I updated the maintenance plan's connection to use the IP directly (in a desperate attempt), and I checked the job's user password, but it did not change anything. I think it fails before it gets to this point.

Why does it still want to use "MyServer?" Is there another place where I should set the alias, or clear a wrong alias?

Side Notes:

This is a test server, so there is not much control over who changed what, which is probably how things broke in the first place.

Current error: [382] Logon to server 'MyServer' failed (ConnLogJobHistory)

Error for the original problem (now fixed): [000] Unable to connect to server 'MyServer'; SQLServerAgent cannot start

I already tried a server rename from "MyServer" to "MyServer.abc.def" (where "abc.def" is the DNS suffix), but the error message still shows a failure to log on to "MyServer" (without the suffix). The plan's data source is ".", so no server name mix-up there. I think the problem occurs before this point.

Best Answer

Here are some thoughts to help investigate.

Have you tried just removing the alias local host information?

enter image description here

What is the value for HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\VERSION.INSTANCE_NAME\SQLServerAgent,

What are MyServer's SQL Server Logs saying?

enter image description here

Did you make sure you don't have any aliases in your hosts file.

C:\Windows\System32\drivers\etc\hosts

# localhost name resolution is handled within DNS itself.
#   127.0.0.1       localhost
#   ::1             localhost

Any named aliases in SQL Server Configuration Manager?

enter image description here

Related Question