none
SQL Server does not allow remote connections. (

    Question

  • Hi,

    Attempting to open a new database engine from a Windows 2003 R2 server (x64) which is

    running SQL Server 2005 to my desktop PC  (XP Pro) which is running SQL 2005 developer edition.

     

    I’ve opened up TCP/IP and named pipes and enabled all protocols within surface area configuration and server

    configuration manager but get this…

     

    An error has occurred while establishing a connection to the server.  When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: TCP Provider, error: 0 - A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond.) (Microsoft SQL Server, Error: 10060)

     

    Is this a red-herring type message? I ask because the text states “this failure may be caused” and as far as I can see I’ve open up my

    PC to all connections.

     

    Thanks in advance

     

    Dave

    Friday, December 08, 2006 1:09 PM

Answers

  • Make sure to restart the MSSQLSERVER service after enabling the TCP/IP and named pipes.

    If you did so and its not still not working, try the following steps:

    • Make sure your sql service is running, use either "net start" or "sc query <InstanceName>" or run services.msc, check status of the server; If server start fail, go to ERRORLOG to see what happened there, fix the problem and restart server.
    • You might explicitly use "np:"prefix which ask for connect through named pipe. However, client can not connect to server through the pipe name that specified.Double check the server is started and listening on named pipe if you enabled Named Pipe. One way is that see the ERRORLOG of the server, search follow keywords:

    Server named pipe provider is ready to accept connection on [ \\.\pipe\sql\query ] or [\\.\pipe\mssql$<InstanceName>\sql\query]

    Notice that "sql\query" is the default pipe name, so you need to know server is listening on which pipe name. eg: if you specify server pipe name is "sql\query1", then you would see in the errorlog that server listening on [ \\.\pipe\sql\query1 ], and go to SQL Server Configuration Manager, click client Named Pipe properties, see whether the pipe name is same with the one server listening on.

    • You might specify named pipe protocol in connection string, but did not enable named pipe on the server, check ERRORLOG.
    •  You might use FQDN/IPAddress/LoopbackIP to connect to the server when only shared memory was enabled, you can change to <machinename> to resolve this.
    • You might explictly specify "lpc:" prefix in your connection string, but shared memory was not enabled. To resolve this, either remove the prefix as long as named pipe or tcp was enabled or enable shared memory.

    If nothing of this works try refering to this KB URL: http://support.microsoft.com/default.aspx?scid=kb;EN-US;914277

    I hope this helps

    Friday, December 08, 2006 1:35 PM

All replies

  • Make sure to restart the MSSQLSERVER service after enabling the TCP/IP and named pipes.

    If you did so and its not still not working, try the following steps:

    • Make sure your sql service is running, use either "net start" or "sc query <InstanceName>" or run services.msc, check status of the server; If server start fail, go to ERRORLOG to see what happened there, fix the problem and restart server.
    • You might explicitly use "np:"prefix which ask for connect through named pipe. However, client can not connect to server through the pipe name that specified.Double check the server is started and listening on named pipe if you enabled Named Pipe. One way is that see the ERRORLOG of the server, search follow keywords:

    Server named pipe provider is ready to accept connection on [ \\.\pipe\sql\query ] or [\\.\pipe\mssql$<InstanceName>\sql\query]

    Notice that "sql\query" is the default pipe name, so you need to know server is listening on which pipe name. eg: if you specify server pipe name is "sql\query1", then you would see in the errorlog that server listening on [ \\.\pipe\sql\query1 ], and go to SQL Server Configuration Manager, click client Named Pipe properties, see whether the pipe name is same with the one server listening on.

    • You might specify named pipe protocol in connection string, but did not enable named pipe on the server, check ERRORLOG.
    •  You might use FQDN/IPAddress/LoopbackIP to connect to the server when only shared memory was enabled, you can change to <machinename> to resolve this.
    • You might explictly specify "lpc:" prefix in your connection string, but shared memory was not enabled. To resolve this, either remove the prefix as long as named pipe or tcp was enabled or enable shared memory.

    If nothing of this works try refering to this KB URL: http://support.microsoft.com/default.aspx?scid=kb;EN-US;914277

    I hope this helps

    Friday, December 08, 2006 1:35 PM
  • It did indeed, thanks.  I neglected to restart the sql server service.

    Cheers
    Dave
    Friday, December 08, 2006 1:41 PM
  • That didn't work for me.  I finally entered the following in Server Manager Studio Express to connect to local instance:
    - right click on sqlexpress instance, select Properties
    - edited server name from mymachine\sqlexpress to .\sqlexpress
    - click OK to save
    - double click on instance to connect

    version of...
    Microsoft SQL Server Management Studio Express                        9.00.3042.00
    Microsoft Data Access Components (MDAC)                        2000.085.1117.00 (xpsp_sp2_rtm.040803-2158)
    Microsoft MSXML                        2.6 3.0 4.0 5.0 6.0
    Microsoft Internet Explorer                        6.0.2900.2180
    Microsoft .NET Framework                        2.0.50727.42
    Operating System                        5.1.2600
    Saturday, April 14, 2007 6:02 PM
  • You will have to enable remote connections on your SQL Server (See the Screencast on my site for more information) and start SQL Browser as this service is responsible for redirecting incoming requests to the dynamically assigned port of SQL Server Express.

    Jens K. Suessmeyer.

    ---
    http://www.sqlserver2005.de
    ---
    Sunday, April 15, 2007 6:58 PM
    Moderator
  • I have done all of this and I still get the error that says:

    An error has occurred while establishing a connection to the server.  When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: TCP Provider, error: 0 - A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond.) (Microsoft SQL Server, Error: 10060)

     

    Here's the thing: this is a developer edition, being installed on my development box (a pretty much state of the art desktop with XP Media edition)

    I am not trying to connect remotely--the server instance is on the same box! I have removed and reinstalled SQL server twice, and I still can't connect to the instance, which is the default instance. TCP/IP, shared memroy and named pipes are all enabled. SQL Express is installed by something else, probably MS accounting, and I disabled that too. Nada.

     

    Please help--my development work is at a standstill.

     

    Lisa Morgan

    Saturday, May 12, 2007 4:32 PM
  • YOu have a default instance running. Are you running on the default port of 1433 ? Do you have a virus scanner / OneCare or any firewall on the system preventing to reach the TCP port ? If yes, try to shutdown these software components (just for the sake of trying it without them). if this works, you will have to configure the appropiate rules to let the traffic fo through to the SQL Server instance.

    Jens K. Suessmeyer.

    ---
    http://www.sqlserver2005.de
    ---
    Monday, May 14, 2007 6:23 AM
    Moderator
  • I had this problem too, and have learned a lot trying to solve it !

    But in the end it was just a bit fat DOH!

     

    When I ran aspnet_regsql.exe the server field defaulted to my computer name, ie. my-machine.

    In actual fact, my SQL Server instances are named my-machine\netsdk and my-machine\sqlexpress.

    Once I set it to one of these, I was able to select or add a database.

    What a red herring that message was ! 

    Monday, June 04, 2007 2:54 AM
  • Hi,

     

    One stop solution for this problem, refer to the link http://support.microsoft.com/kb/914277

     

    This is due to the firewall blocking your incoming connection for SQL Server and SQLBrowser. You need to create 2 exceptions for these applications in your local firewall.

    Thursday, April 24, 2008 8:15 AM
  • Hi Rajesh,

     

    It was really good information. It help me resolve the issue.

    Thursday, June 17, 2010 10:33 AM