SQL Server Failed to Open Loopback Connection – What?

I could not read my error log on one of my local SQL Servers, when I executed the following code:

EXEC sp_readerrorlog

I received the below:

Msg 22004, Level 16, State 1, Line 2 Failed to open loopback connection. Please see event log for more information. Msg 22004, Level 16, State 1, Line 2 Error log location not found.

When trying to read the error log via SSMS (SQL Server Management Studio) I would get this.

loopback.JPG

I then started to dig around and look at the physical log files (for this instance files located at C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Log) to see if it would give me any clues. I came across this message which told me everything that was wrong!

2017-10-25 10:46:02.48 Logon       Could not connect because the maximum number of ‘5’ user connections has already been reached. The system administrator can use sp_configure to increase the maximum value. The connection has been closed. [CLIENT: ]

I then queried sys.configurations.

SELECT * FROM sys.configurations WHERE name = 'user connections'

value5

SERIOUSLY?    IT WASN’T ME! I then used sensible values and restarted SQL Server.

USE master
GO
EXEC sp_configure 'user connections', '0';
RECONFIGURE WITH OVERRIDE;  

SELECT * FROM sys.configurations WHERE name = 'user connections'

newvalue.JPG

No loopback message anymore.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s