locked
No connection could be made because the target machine actively refused it RRS feed

  • Question

  • 

    Hi, upon reviewing SQL Server 2014 (evaluation), I received "No connection ... actively refused it" error message when trying to connect remotely to the default instance using the SQL Server management studio on the same machine. All recommendations I found on internet were followed: Win 7 firewall turned off, remote access and tcp/ip enabled, any alias deleted, security for win + SQL Server is selected, all services are running, all tcp and udp ports of concern are opened, local IP address + gateway + dns server are fixed. Any help? Said.

    Wednesday, April 22, 2015 9:04 PM

Answers

  • Hi Erland, I would like you to share with me what I have discovered. Someone locally advised the use of fixed public IP address rather than using dynamic Public IP. What a surprise, I was able to connect remotely, even without router port forwarding. Thank you for your advice. Said. 
    Friday, May 8, 2015 3:40 PM
  • Great to hear that it worked out finally!

    This is the problem with assisting with these connectivty problems over a distance. There are plenty of things that can go wrong, and without access and knowledge of the environment, it's quite tedious to come up with suggestions. For instance, there is quite a difference between a corporate environment and having a handful machines at home.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Friday, May 8, 2015 9:47 PM

All replies

  • Are you able to telnet onto the port number that sql server is running on?

    Please mark the answer as helpful if i have answered your query. Thanks and Regards, Kartar Rana

    Thursday, April 23, 2015 1:04 AM
  • Hello,

    Please review the SQL Server ErrorLog to get more information about this; for this you should try to connect locally or open ErrorLog file in a text editor.

    Are the server and the remote machine within the same domain and is the use account to connect a domain account? Or are you using a Windows Workgroup? If so then you should use SQL Server Login instead of Windows authentication, which is not supported in a Workgroup, only with a domain.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Thursday, April 23, 2015 8:58 AM
  • Can you copy the full error code/ screen-cap for the error your getting?

    Error log will also be helpful. Also let me know whether it is default instance, named instance, clustered or standalone etc  and whether the problem is regular/frequent or rare


    Good Luck! Please Mark This As Answer if it solved your issue. Please Vote This As Helpful if it helps to solve your issue

    Thursday, April 23, 2015 9:02 AM
  • Hi, 'telnet' is not recognized on my machine. I used 'netstat -anb' instead. Local connection is established for the default instance 'MSSQLSERVER' via fixed port 1433. 'sqlbrowser.exe' is listening on TCP 2382 and UDP 1434. ''sqlsevr.exe' is listening on TCP 65000.
    Thursday, April 23, 2015 9:19 PM
  • That error often indicates that you are connecting to the wrong port where some completely different program is listening and rejects you when the protocol is not the expected one.

    In the file
    C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Log\ERRORLOG
    (the exact path may vary), you should see a line like this:

    2015-04-23 21:12:11.37 spid15s     A self-generated certificate was successfully loaded for encryption.
    2015-04-23 21:12:11.37 spid15s     Server is listening on [ 'any' <ipv6> 19508].
    2015-04-23 21:12:11.37 spid15s     Server is listening on [ 'any' <ipv4> 19508].

    19508 is the the port number in this example. What does it say in your case?


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Thursday, April 23, 2015 9:19 PM
  • Try specifying SERVERNAME,65000 when you connect.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Thursday, April 23, 2015 9:28 PM
  • 
    Hi, ErrorLog reports the following: DAC support is not enabled in this version of SQL Server, SQL Server network interface library could not register SNP, the server broker protocol transport is disabled, the database mirroring protocol transport is disabled, service broker manager has shutdown, SQL Server is terminating in response to a 'stop' request from servive control manager, SQL trace was stopped due to service shutdown. I'm logging to server on same machine in workgroup using 'public IP address\MSSQLSERVER,1433', and sql authentication. No problem in local connection. Said. 
    Thursday, April 23, 2015 10:34 PM
  • Hi, for ErrorLog please refer to the inserted image. SQL Server is a default instance. I have never succeeded in remote connecting. Said.
    Thursday, April 23, 2015 11:00 PM
  • You said its SQL evaluation 2014 but this seems like SQL server 2008 express edition. I think you are mixed up.

    You instance should be name: SAID2\sqlexpress

    Did you try with this. Also make sure instance is up and running as it seems SQL services was stopped looking at this log.

    If this is not the correct errorlog(again paste correct errorlor here). I suppose you have multiple instances make sure browser service is running. 

    Friday, April 24, 2015 2:27 AM
  • There is a stop/ termination event at 23:52, are you sure that services are actually running. Are you seeing any System/ Application events in event viewer pertaining to SQL Server, if yes, then can you share them here

    Good Luck! Please Mark This As Answer if it solved your issue. Please Vote This As Helpful if it helps to solve your issue

    Friday, April 24, 2015 4:42 AM
  • Hi, It says exactly the same as you mentioned, but for port 1433. Said.
    Friday, April 24, 2015 6:46 PM
  • Hi, I tried 6500 with no success. Said.
    Friday, April 24, 2015 6:48 PM
  • <https://social.msdn.microsoft.com/Forums/getfile/649171>Hi, for ErrorLog please refer to the inserted image. SQL Server is a default instance. I have never succeeded in remote connecting. Said.

    As others have pointed out, this log is from an instance running SQL 2008 SP1 and you said SQL 2014 in your initial post.

    Did you mix up the versions, or did you take the errorlog from the wrong machine?

    From the errorlog you posted, everything looks normal, but we don't know if we are looking at the right one.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Friday, April 24, 2015 9:32 PM
  • Hi Manu, yes I'm sure all services are running. Event Viewer reports error for: Report Server Windoes Servive (MSSQLSERVER), and MSSQLSERVER (default instance). It also reports information the same as reported in the ErrorLog previously attached to you. What effect has the following entries in the ErrorLog: DAC support is not available, SQL Server network library could not register SNP, the server broker protocol transport and the database mirroring protocol transport are disabled. Said.
    Friday, April 24, 2015 10:41 PM
  • Hi Dave, you are wright, I already have SQL 2008 and have installed SQL 2014 in an attempt to solve the remote connection problem. The local string 'SAID2\SQLSERVER' is of no problem. The problem is to connect remotely, with SAID2 replaced by public IP address. Every thing is running. Herewith please find the ErrorLog.Said. 
    Friday, April 24, 2015 11:12 PM
  • You are saying SAID2\SQLSERVER? You should say only SAID2, since you have a default instance.

    What effect has the following entries in the ErrorLog: DAC support is not available, SQL Server network library could not register SNP, the server broker protocol transport and the database mirroring protocol transport are disabled

    They are benign. I was a little suspicious about the SNP thing, but I checked my own installation, and I have the same message. And mine is working.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Saturday, April 25, 2015 9:36 AM
  • Hi Erland,  yes I have both SQL Server 2008 and SQL Server 2014. They can live together with no problem. I installed the latter in an attempt to solve the remote connection problem. The ErrorLog file I attached relates to my default instance 'MSSQLSERVER'. Anyway, as for SNP, it seems you are running your SQL Server locally. I have no problem running SQL Server locally too. Said.
    Sunday, April 26, 2015 2:00 AM
  • So there are two instances on your machine, and you cannot connect to any of them remotely? Do you get the same error for both instances?


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Sunday, April 26, 2015 7:17 PM
  • Hi Erland, yes same error. I share you the worry about SNP. You said you have same log for SNP, with successful connectiom. Do you run your SQL Server remotely? Said. 
    Sunday, April 26, 2015 8:58 PM
  • Occasionally, I run it remotely yes.

    Could you please post the same part of the errorlog for the SQL 2014 instance?

    Can you also run the ouput of "netstat -a" with both instances running?


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Sunday, April 26, 2015 9:23 PM
  • Hi Erland, in the occasions you remotely run SQL Server successfully, do you have the SNP error entry in ErrorLog file? I'm inserting 2 images for ErrorLog flle, one after Windows startup and one after SQL Server 2014 management studio startup. The latter is repeated about 3 times. In next message, I'm inserting 2 images of Netstat after SQL Server startup. Please see images down-up. Said.
    Monday, April 27, 2015 5:25 PM
  •                   Hi Erland, this is a contiuation to a previous message, as I'cannot insert all images in one message. Please find the 2 images of Netstat. Again, please see images down-up. Said.
    
    Monday, April 27, 2015 5:33 PM
  • You posted the log for the SQL 2008 instance again; we wanted to see the log for the SQL 2014 instance.

    Then again, second part of the SQL 2008 log was very interesting. Where do all these login failures come from? They look like a brute-force attack.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Monday, April 27, 2015 9:21 PM
  • Hi Erland, I have moved to another disk partition running Win 7 and SQL 2008 only with only one instance (SQLEXPRESS). Login results in same message. ErrorLog reports only part one previously sent to you. 'netstat -anb' reports the inserted images. Said.
    Thursday, April 30, 2015 8:01 PM
  • I see a number of AvastSvc.exe. Could you disable Avast and see what happens?


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Thursday, April 30, 2015 9:09 PM
  • Hi Erland, no effect even when Win firewall is turned off and antivirus is disabled. Please confirm your ability to connect remotely while you have SNP error entry in ErrorLog file. Said.
    Friday, May 1, 2015 8:52 PM
  • That does indeed work for me.

    Which edition of Avast do you have?

    While it may be extreme, you could even try uninstall it entirely. I have different issue with my AV/firewall, which prevents me from using FILESTREAM on SQL 2008, even if disable the AV/firewall product.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Friday, May 1, 2015 9:08 PM
  • Hi Erland, No effect even when Win firewall is turned off and Avast antivirus being uninstalled. Said.

    Saturday, May 2, 2015 7:10 PM
  • I'm afraid that without access to your environment, we are not able to help you. After all, we are making guesses in a perfectly dark room. Hopefully, you can find someone locally who can come around to help you.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Saturday, May 2, 2015 7:44 PM
  • can check cmd -> set l check dc for mssql

    Sunday, May 3, 2015 9:56 AM
  • Hi Erland, It was nice of you. Thank you for your trials to help. Best regards. Said.
    Sunday, May 3, 2015 2:43 PM
  • Hi Erland, I would like you to share with me what I have discovered. Someone locally advised the use of fixed public IP address rather than using dynamic Public IP. What a surprise, I was able to connect remotely, even without router port forwarding. Thank you for your advice. Said. 
    Friday, May 8, 2015 3:40 PM
  • Great to hear that it worked out finally!

    This is the problem with assisting with these connectivty problems over a distance. There are plenty of things that can go wrong, and without access and knowledge of the environment, it's quite tedious to come up with suggestions. For instance, there is quite a difference between a corporate environment and having a handful machines at home.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Friday, May 8, 2015 9:47 PM