Cannot view ErrorLog or use SQLAgent on multi Instance Server

An issue was reported with one of my SQL Servers that required it to be rebooted.

After the reboot, I logged on to SQL Server Management Studio (SSMS) to check the SQL ErrorLog to ensure that all was well.

Unfortunately, I was met with an error when I tried to open the ErrorLog:

“Failed to retrieve data for this request.”

The bottom part of the error was more worrying – “A severe error occurred on the current command. The results, if any, should be discarded”.

While slightly concerned, I started the troubleshooting process by locating the ErrorLog file on the disk and opening it.

The location of the ErrorLog can be found in SQL Server Configuration Manager, as mentioned in a previous blog post.

After finding that the ErrorLog exists and was not reporting any issues, I went back to SSMS and used a command that returns the contents of the ErrorLog – i.e. EXEC sp_readErrorLog

This returned another Error:

Failed to open loopback connection. Please see event log for more information.

I checked the Windows EventLog and there was an interesting entry – it was complaining that the Service Account that the SQL Server was running under did not have permissions to login.

This Server had multiple SQL Server Instances installed, so my Google searches had a field day, making all sorts of suggestions:

Server running out of connections – Checked, and it was set to 0 connections, the default

User access not being granted (not logical – the SQL Server could be seen running)

Pinal Dave had a blog post with a few suggestions, eventually pointing to a bug with the SQL Server ODBC driver.

Turns out that the solution was a bit simpler.

An Alias had been set up for the SQL Server which pointed to the port number of a secondary SQL Server Instance.

E.g. There was an alias for my SQL Server called “CAMELIDAE” that pointed connections to the “CAMELIDAE\ALPACA” SQL Server Instance.

This caused the strange behaviour within SQL Server Management Studio where general SQL Server details were being displayed correctly, other than the SQL Server ErrorLog and SQL Agent.

This gives a clue into how SSMS accesses SQL Server – clearly a different Data Access method is used, which is then caught by the Alias and re-directed.

If this error brought you here, check for SQL Server Aliases (also in SQL Server Configuration Manager) just to see if anyone has set one up.

Hopefully this will help you to save a little time and pain.