0

Here is the script:

SELECT @@VERSION
GO
EXEC sys.xp_readerrorlog;
GO

Here is the result:

Microsoft SQL Server 2014 - 12.0.4213.0 (X64) 
    Jun  9 2015 12:06:16 
    Copyright (c) Microsoft Corporation
    Enterprise Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)


(1 row(s) affected)

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.

------------------------------------------------------------------------------------
=== Event Log Error info
------------------------------------------------------------------------------------
The description for Event ID 17052 from source MSSQLSERVER cannot be found. 
Either the component that raises this event is not installed on your local computer or the installation is corrupted. You can install or repair the component on the local computer.
If the event originated on another computer, the display information had to be saved with the event.
The following information was included with the event: 
Severity: 16 Error:10061, OS: 10061 [Microsoft][SQL Server Native Client 11.0]TCP Provider: 
No connection could be made because the target machine actively refused it.
the message resource is present but the message is not found in the string/message table

The problem occurs ONLY when I switch default port to another value.
When return its value back to 1433 everything goes back to normal.

During the execution could capture following waits:

wait_type: MSQL_XP  
last_wait_type: PREEMPTIVE_OS_GETPROCADDRESS

Can see it only on my SQL2014 on Win8.1 VM. Error persists even when run query locally as local admin enter image description here

So, how the port number can be related to that error?


UPDATE: Tried following locally:

sqlcmd -E -S "localhost,1453" -Q "EXEC sys.xp_readerrorlog;"
sqlcmd -E -S "127.0.0.1,1453" -Q "EXEC sys.xp_readerrorlog;"

Get the same error message.

In addition to SQL 2014 installed SQL 2016.
With SS2016 behavior is little different: It produces an error in ANY case, even if I set SQL port to 1433. I guess because it is "Named" instance.

So far see only that extra evidence within the ERRORLOG file that SQL Server loads xpstar.dll during the request (I assume it is normal):

Attempting to load library 'xpstar.dll' into memory. 
This is an informational message only. No user action is required.
Using 'xpstar.dll' version '2015.130.1601' to execute extended stored procedure 'xp_readerrorlog'. 
This is an informational message only; no user action is required.
9
  • Can you run ANY query against that ip and port number. From your error message, it may not even be related to xp_readerrorlog - "No connection could be made because the target machine actively refused it." – Scott Hodgin - Retired Sep 7 '16 at 11:51
  • When you change the port, did you restart sql server ? I believe that might be the case. Since it cannot find the loopback address 127.0.0.1 check your host file C:\WINDOWS\system32\drivers\etc\hosts to see if you have a loopback connection as 127.0.0.1 localhost server_name. – Kin Shah Sep 7 '16 at 14:50
  • @ScottHodgin Everything else looks fine. If you look at the query's result you'll see that SELECT @@VERSION run fine. – Slava Murygin Sep 7 '16 at 16:24
  • @Kin Yes, I did restart the server every time. Otherwise I wouldn't see the difference. Why 127.0.0.1 should matter? I'm querying server by IP and see the difference only when I change SQL listening port from default to something else – Slava Murygin Sep 7 '16 at 16:30
  • From the error message, seems problem with your loopback address. Try adding 127.0.0.1 localhost server_name or 127.0.0.1 localhost IP_ADDRESS to your host file. – Kin Shah Sep 7 '16 at 16:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.