SQL Server 2005 Remote Connectivity Issue TroubleShoot


With shipping SQL Server 2005, we heard from customer feedback about suffering make successful remote connection against SQL 2k5, plus, if running SQL Server on XP/WIN2K3/VISTA behind firewall, such problem occurs more frequently and harder for customer to get clue of behind reason.

I. Background of remote connectivity issue.

The reason we saw the issue comes out w/ SQL 2k5 instead of SQL 2k is because two breaking change in SQL 2k5 compared to SQL 2000.

1) Network Protocols Off by Default: In order to make system secure, we limit connectivity when a user install SQL 2k5, thereby reducing surface area for attack. By default, SQL server (SQLEXPR, SQLDEV and EVAL SKUs), on installation will listen only on Shared memory and local-only Named Pipe. TCP and remote Named Pipe will be off-by-default. VIA will also be off-by-default; SQL server (SQLENT, STANDARD and Workgroup SKUs), on installation will listen only on Shared memory, local-only Named Pipe and TCP. Remote Named Pipe will be off-by-default. VIA will also be off-by-default.

Therefore, when you mae remote connection, you should make sure at least either Named Pipe or TCP are enabled in your remote SQL instance.

2) SQL Browser service: which is a replacement of SSRP system in SQL Server 2000, run as a Windows Service on installation of SQL 2k5. Upon startup, SQL Server Browser starts and claims UDP port 1434. When SQL Server 2000 and SQL Server 2005 clients request SQL Server resources, the client network library sends a UDP message to the server using port 1434. SQL Server Browser responds with the TCP/IP port or named pipe of the requested instance.

Therefore, you need to make sure SQL Browser is enabled and started when remote client ask for which tcp port or pipe name SQL Server is listening on. If your remote SQL Instance is a default instance, you do not necessary enable sql browser since client would always try default TCP port 1433 and pipe name \<remoterserver>pipesqlquery. But, if you have SQL 2k5 named instance installed or SQL 2000 and SQL 2k5 side by side installed, you must enable and start sqlbrowser.

II. Problem list:

By understanding background 1) and 2), I belive you can imagine issues you may face when make remote connection against SQL 2k5:

1) Fail to connect over TCP/IP or Named Pipe if  the request protocol was not enabled.

2) Fail to connect over TCP/IP or Named Pipe if Firewall enabled on the remote server and tcp port or “File and Printer Sharing”  is not added to the Firewall exception list.

3) Fail to connect to remote sql named instance if SQL Browser was not enabled or UDP port 1434 that browser listening on is added to the Firewall exception list.

III. Troubleshooting Tips:

Assumption: your SQL Server was installed on remote server and behind firewall; SQL Instance was started; and  your client app specify correct remote sql instance name.

On your server side:

[1] Enable remote named pipe or tcp: All programs | Microsoft SQL Server 2005| Configuration Tools | SQL Server Surface Area Configuration | Configuration for Services and Connections | Remote Connections, choose either enable TCP or Named Pipe or both.

[2] Sql Instance was restarted successfully, check Server ErrorLog, find which tcp port or pipe name server is listening on.

[3] netstat -ano | findstr <portnumber> if server enable TCP, and make sure server is listening on the correct port.

[4] go to services.msc, find service “SQL Server Browser”, enable it and restarted, also, go to SQL Server Configuration Manager, check properties for SQL Browser service, in Advanced tab, make sure it is active.

[5] Enable “Fire and Printer Sharing” in Firewall exception list.

[6] Add TCP port or sqlservr.exe to Firewall exception list, either add “..Binnsqlsevr.exe” or add port.

If your server was not started successfully by any reason, it is very helpful to collect info from server logs; also, you can get clear picture of protocols that server is listening on, for eg, if TCP was enabled, you should be able to see which port server is listening on, and if Np was enabled, you can make connection throgh the pipe name.

[7] Add Sql Browser service to Firewall exception list, you can either add program ” C:Program FilesMicrosoft SQL Server90Sharedsqlbrowser.exe” or add UDP port 1434.

[8] Make sure if your remote sql Server Instance is a default instance, it must listen on tcp port 1433 and pipe \.pipesqlquery.

On your Client Side:

[1] ping <remote server> return correct IP address of your remote server.

[2] telnet <remoteserver> <portnumber> works, <portnumber> is the port that your remote sql instance is listening on.

     telnet < ipaddress> <port> works

[3] \<remoteserver>, make sure you can access share of remote server.

[4] Go to SQL Server Configuration Manager | SQL Native Client Configuration | Client Protocols, make sure Named pipe or tcp is enabled; Click properties of Client Protocols, make Sure you see at least Np or tcp is in enabled protocols, plus, recommend put TCP on the top of the order.

[5] If you are using MDAC ( Driver={SQL Server} or SQLOLEDB.x) in your client application, in command line, tyep “cliconfg.exe”, also enable NP and TCP and put TCP on top of order.

[6] Use osql or sqlcmd to try ” osql /S<remoteserver> /E” or “osql /S<remoteserver><Instancename> /E” see whether connection works.

[7] Check your client connection string syntax: a. do you specify correct remote server name? b. do you specify correct instance name? if remote sql is default instance, you just need to specify remote server machine name, otherwise, you need to specify ” <remoteserver><instancename>” c. do you  spcify tcp port or pipe name in your connection string, if so, double check the port and pipe name are the one server is listening on, otherwise, remove it.

[8] If your client app connect to remote server using server alias name, a. if you are using MDAC, try “cliconfg.exe”, in “Alias” Tab, check whether you specify any alias in which the tcp port or the pipe name should be the one remote server is listening on; b. if you are using SQL Native Client, go to SQL Server Configuration Manager, check “Aliases” configuration.

If you are sure the network protocol configuration for remote connection are all correct by going through above checklist, and still face specific problem, pleas refer following blog that give troubleshooting tips based on concrete error message:

http://blogs.msdn.com/sql_protocols/archive/2005/10/22/483684.aspx

http://blogs.msdn.com/sql_protocols/archive/2005/10/29/486861.aspx

http://blogs.msdn.com/sql_protocols/archive/2005/12/22/506607.aspx

http://blogs.msdn.com/sql_protocols/archive/2006/03/23/558651.aspx

 

MING LU

SQL Server Protocols

Disclaimer: This posting is provided “AS IS” with no warranties, and confers no rights

 

 


Comments (82)

  1. Santosh says:

    Dear All,

    I am having problems in the connectivity from branch office to head office. I have a sql server 2000 installed on a Windows 2000 Enterprise server in head office.

    There is client server application sitting in branch office and i have correctly setup the connection parameters from the app itself. But it always fails to connect to this server.

    I believe this has something to do with SQL server port or something???

    Pls help

    Rgds

    Santosh

  2. Hi, Santosh

       Please provide your connection string and error message you came across, plus, follow up section III in the blog to see whether that resolve your issue. Double check whether you can telnet your sql port on header office, whether the port is blocked by the firewall.

    Good Luck!

    Ming.

  3. Named Pipes Provider, error 40 Could not open a connection to SQL says:

    Hi,

    I have a big problem, I have a website in a Windows 2003 and SQL Server

    2005, and the website usually goes weel, but some times and error appears,

    the error is "Named Pipes Provider, error: 40 – Could not open a connection

    to SQL Server".

    There is not installed a firewall and TCP and Pipes connection are enabled.

    Any ideas?

    Thanks in advance,

    Miguel

  4. Hi, Miguel

       Can you provide more detail info by answering following questions?

    http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=362498&SiteID=1

    Thanks!

    Ming.

  5. maks says:

    guys can’t you just make programs without bugs?? i did everything to allow remote connections and tomorrow it did not work. today i restarted server again and i did connected but after 15 minutes of work in management studio express i got disconnected and more than hour can’t connect again. is it some kind of a joke? if (onlinetime > 15) { disconnect(); blockallconnections(); } ??

  6. Matt Neerincx (MSFT) says:

    If you got disconnected mid-connection this is very unusual for SQL Server.  Check your ERRORLOG and see if someone else is kicking you off (look for KILL commands in ERRORLOG).

  7. David says:

    When I try to use telnet to monitor sql2005 connectivity I’m getting an error says 17836 severity 20, state 1, Length specified in network packet payload did not match number of bytes read; the connection has been closed. Plase contact the vendor of the client library. In sql 2000, the same test is ok.

    Thanks,

    David

  8. Matt Neerincx (MSFT) says:

    Yes, this is SQL Server 2005 reporting that you sent some invalid bytes to it in a request.  SQL Server 2000 did not have this level of error reporting.

    When you telnet to a SQL port, you are effectively acting like a client driver and starting the login process.  If you type some things into the telnet session and this is sent to the server, then SQL Server will read these bytes and attempt to process the bytes as an incoming login or prelogin packet and this will fail and report the error to the errorlog.

  9. Hi, David

       1) Is your SQL 2000 and SQL 2005 on the same box? If not, can you install SQL 2k on the sql 2k5 box, and do the same testing from the same client box? what happens?

       2) What is your client request? namely, what data operation you are doing?

       3) It’d better to provide more detail info about what is your testing? and Your testing environment, such as OS of client and server box, version, sku, protocol setting of your SQL 2005.

    Thanks!

    Ming.

  10. David says:

    Thanks Matt Neerincx, is there anyway to get around it?

    Ming,

    1)No, not on same box, and this can be reproduced anytime anywhere with any machine.

    2)the request is the cmd below:

    telnet machinename 1433

    quit

    quit

    Basically this will login to sql on port 1433, and exit out.

    3)I tested this on any sp level on sql 2000 ok, and sp1 and sp2 of sql 2005 getting an error.

    I know it’s probably better to use odbcping to monitor it, but the corp standard monitor server is an UNIX box, and I can not run odbcping on it.

    Thanks,

    David

  11. This error was most frequently hitted by our customers, and in this post, give a brief summary of troubleshooting

  12. Oleksiy says:

    I just want to add additional part here – issues with a non-default port 1433.

    If your SQL 2005 server has to listen on something different than 1433 which can be a security requirement in large corporations, then it becomes additional problem to get it to work properly.

    The sequence of the performed changes that I discovered:

    – Install SQL 2005

    – Run Management Studio for the first time, make sure all services are running

    – Install SQL 2005 SP1

    – Run Management Studio again, make sure everything still works

    – Change the default port in SQL Server Configuration Manager (Server Protocols, Native Client Protocols)

    – Add an alias to itself under Native Client with your non-default protocol:

    Alias Name: MYSERVERNAME

    Port No: your_port_number

    Protocol: TCP/IP

    Server: MYSERVERNAME

    – Restart all SQL services

    – Run Management Studio again, make sure it works this time and you can connect to the server.

    The similar alias need to be configured under the Native Client configuration to connect to remote SQL 2005 server which is listenning on non-1433 port.

    It looks like the SQL Browser service is responsible for the named instances but not for the non-default port configuraion. Each server running on non-1433 port need to have an alisas defining port number. This applies to the scenario with database replication between servers running on non-1433 port.

    Oleksiy

  13. Yogesh says:

    I am using MS SQL server 2000 with Widnows 2000 server, problem is sql engine is automaticaly disconnected where ever an event is generated.

    pls help

  14. abraham says:

    I found an alternative, you can use the following sintax,

    <server name>,<port number><instance name>

    You can se the por number on sql server log in the sql studio manager or the sql configuration manager.

  15. Carl says:

    Hi,

    I have a problem with a connection to database engine.  Evert time when I connect it, it always gives me this message:

    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: SQL Network Interfaces, error: 26 – Error Locating Server/Instance Specified) (.Net SqlClient Data Provider)

    For the server name, I put in <my servername><my instance>. I checked sqlbrowser and it’s running. I changed the remote connection in database engine to local and remote conncection, but it still doesn’t work.  

    Please help me out.  Give me a solution to this problem. Thanks so much for your help.

  16. MING LU says:

    Hi, Carl

       1) What is the error message in server ERRORLOG? You can also see it from system application event log. If there is problem that indicate sql server terminate client connection, that means your client request has problem, and it is out of connectivity scope, you need to check your client application.

       2) Are you connecting Express? If so, please doublec check the instance name is "sqlexpress" in your connection string.If not, make sure you specifiy the correct instance name.

       3) Open SQL Server Configuration Manager, click client protocols, check whether TCP/NP protocols are allowed for remote connection?

       4) Look at your server ERRORLOG, see whether your sql instance is listening on the NP and TCP port.

       5) On your client machine,do "new view \<remoteserver>" and "telnet <remoteserver> <tcpportthatserver listing on>" See whether that works?

    Good Luck!

    Ming.

  17. Carl says:

    Hi Ming,

    I checked the Errorlog and it shows:

    Server is listening on [ ::1 <ipv6> 49299].

    2007-05-20 18:09:28.92 Server      Server is listening on [ 127.0.0.1 <ipv4> 49299].

    2007-05-20 18:09:28.92 Server      Dedicated admin connection support was established for listening locally on port 49299.

    I also checked TCP/NP protocols are allowed for remote connection. I tried to telnet my remote server and it gies me this message:

    Could not open connection to the host, on port 23: Connection failed.

    I’m using SQL2005 Enterprise eval version. When I connect to database engine using <computer name>MSSQLSERVER, it gives me this message:

    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: SQL Network Interfaces, error: 25 – Connection string is not valid) (.Net SqlClient Data Provider)

    I tried to uninstall the program and reinstall it again, but still the same.  Could you please help me correct it?  Thanks so much.

  18. MING LU says:

    Hi, Carl

       You connection string seems incorrect. You are connecting to the default instance, so, you do not need to specify "MSSQLSERVER", just the <computer name> in your connection string.

    Good Luck!

    Ming.

  19. Carl says:

    Hi Ming,

    I tried it with just <computer name> and it gives me this message:

    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 – No connection could be made because the target machine actively refused it.) (.Net SqlClient Data Provider)

    How do I fix my connection string? Thanks for your help.

  20. Stony says:

    I have some problems with this remote connection thing. May be I should start with this question.

    Do you have to have sql server installed on the client machine before can make a remote connection to server?

  21. Saif says:

    Hi,

    I am having hard time with my VS-2005. Please get me out of this. I dont want to use MSSQL-2005 with VS-2005 so i uninstalled it and in place of this i installed MSSQL-2000.VS-2005 is not showing it in server explorer. Also When i provide the connection string an error message come like 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: SQL Network Interfaces, error: 25 – Connection string is not valid)".

    I have done lots of R&D but not successful.

  22. MING LU says:

    Can you provide the connection string to us for taking a look at.

  23. Radu says:

    In my situation, the things are pretty clear…I have installed on my machine SQL Server, and I try to connect to it, from my machine…and I get that error with "…does not support remote connections…". Well, I wonder why Microsoft, a multi-bilion dollars company, has this connectivity issue, when MYSQL, which is free, doesn’t have it…I only need SQL Server for school (we are forced to use Microsoft software 🙁 ), I don’t need security and stuff…but of course, 90 % of the people that use computers have Microsoft software installed on them – yet. So please, use Linux, BSD, Solaris or any other open-source software…you won’t have these issue…NEVER!

  24. Nancy says:

    I have a remote connectivity issue using an alias.  

    I can connect from a remote computer using the named instance (OPERACIONESDCDELFOS).  This instance is using the 1435 port.  I configured an alias for this instance.  It is named OPERACIONESDCALIAS:

    Alias Name: OPERACIONESDCALIAS

    Port No: 1435

    Protocol: TCP/IP

    Server: OPERACIONESDCDELFOS

    The connection using the alias can be established locally, but it doesn´t work from the remote computer.  The error is:

    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: SQL Network Interfaces, error: 26 – Error Locating Server/Instance Specified) (Microsoft SQL Server).

    I checked the network configuration and every ports are open in the server, and the remote computer can listen the 1435 port, because it can resolve the OPERACIONES/DELFOS connection and a telnet connection to the 1435 port.

    What should I check? Any Ideas?  Please help me!

    Thank you.

    Regards,

    Nancy

  25. Nancy,

       It might due to remote connection was not enabled. TODO:

    1) Start sqlbrowser service on remote machine.

    2) Add sqlbrowser.exe to Firewall exception list

    3) Double check "File and Printer Sharing" was enabled on remote server.

    4) Whether you typed correct remote machine name in your connection string?

    5) Whether your remote machine has the same instance installed?

    Good Luck!

    Ming.

  26. Nancy says:

    Hi Ming

    I still have problems.

    I installed a sniffer in the client computer and I noticed that, when I call the named instance with the original name, the client make a requisition to the 1434 UDP Port, and the 1434 UDP port answer saying that the conextion must be established using the 1435 TCP port.  The conexion using the 1435 port is estabilished and everything works fine.

    Otherwise, when I tried to connect using the alias, the client make the call to the 1434 UDP port but there is not answer from the server, I expected an error or something, but it just doesn´t answer and the timeout present the error locating the server.

    What can be happening? the sql browser server is active and started.  I don´t have a firewall in the server, and in the client it is disabled.

    I think the problem is with the sql browser services.  When I connect to the named instance, is it working?  is the 1434 UDP conection always answered by the sql browser service?  why it answer when I use the original name, but not when I use the alias?

    I understand that you suggest me to add the sqlbrowser.exe in the client firewall.  I mean to use the conection using an ODBC, so I guess that I only have to install the conectivity tools in the client and I don´t have a sql browser service.

    Thank you for your help… I hope you have some ideas.

    Regards,

    Nancy

  27. Nancy says:

    I explain what I want to do, because I´m thinking that it is not possible:

    I´m migrating SQL Server 2000 to SQL Server 2005 and I want to use an alias because there is an application in Visual Basic 6.0 that uses the SQL 2000 database, connected by an ODBC.  I want to define an alias in SQL 2005 with the name of the instance in SQL 2000.  I have done some test, and I found that is necessary to define the alias in the client too, instaling the SQL Server Native Connectivity.  Is it true?  is necessary to define the alias in the client?

    What I have in mind is to define the alias in the server, and every computer using the VB application could connect to SQL 2005 without making any change in the computer.

    Thank you for your help.

    Regards,

    Nancy

  28. MING LU says:

    Hi, Nancy

       1) SqlBrowser service is with SQL Server, not on the client. Make sure if you do not use alias, just "osql /S <remoteserver><instancename> /E" succeeds.

       2) Did you configure the alias on the client or on the server, you need to run "SQL Server Client Network Utility" on your client box, then specify the remote serve name(it should be like <remoteserver><instance> if you connect to a named instance(here is  and tcp port(1435), alias name.

      3) Try first "telnet <remoteservername> 1435" see whether it succeeds.

    Then retry connection.

    Good Luck!

    Ming.

  29. SSharma says:

    I get an error message when I try to connect a database to my web application :

    (I am running both SQL server and the web application on a local machine)

    I get the same error when I try to add a new SQL database in the server explorer.

    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 doesn’t allow remote connections (Provider : Named Pipes Provider,error : 40 – Could not open a connection to SQL Server )

    Please help!

    How do I configure SQL Server 2005 to connect the web application I am building using VS 2005 to the database?

    Why does this error occur even when I am not making a remote connection ?

  30. Balaji says:

    Hi,

    I get no error with establishing connection with remote server by the port 5508. My local one is 2000. I have changed this port from 1434 local.

    But, when i try to connect the databse, i find "No ITEM".

    Could you please let me know, where I have the blunder stuck.

    Thanks

    Balaji

  31. Hi, Balaji

       What is the exact error message when you connect locally to SQL 2000?

    Thanks!

    Ming.

  32. simon says:

    Hi

    Just wondering if you can help me. I have Cisco CSA picking up the following message.The process ‘C:Program FilesMicrosoft SQL Server90Sharedsqlbrowser.exe’ (as user NT AUTHORITYNETWORK SERVICE) attempted to accept a connection as a server on UDP port 1434 from 172.16.71.132 using interface WiredBroadcom NetXtreme 57xx Gigabit Controller. The operation would have been denied.

    I get this every 7 seconds from the same pc to another pc. Both used by our development team. I could open the firewall and stop the error but i would like to know why it trys every 7 seconds.

    Thanks

  33. Conection from Sql server 2005 to sql server 2000 says:

    Hi,

    I using SQL SERVER 2005 on my computer.

    I want to conection to DATABASE on other computer. That Computer using SQL SERVER 2000.

    I don’t conection. What I do something? Help me, please.

    Thank’s

  34. Helen Steve says:

    I am getting the same kind of error. I am stuck

  35. Agbons David says:

    We resolved remote connectivity issue by runnning a name instance of sql 2005 on port 1420 and the browser service on port 1434. The Challenge now is to implement replication. Replication request actual name of server – it does not allow name pipe, ip or "<server>,portinstance" format when trying to connect to the publisher. Please hhhhheeeeeelllllppppppp me!!!

    I have had long hours on this challenge.

    Regards.

  36. Keith says:

    Hi Ming

    i feel like i am going around in circles but the end is in sight.

    the problem is with remote connections. i have found a work around but it is not ideal. i am getting the network protocol: 26 error i am connecting to a named instance which is running on port 2005.

    if i run the connection string slightly differently so that is <server>,<port><named instance> the remote computers can connect, so the question is what have i got to change so that the connection string is <server><named instance>

    Now i have sqlbrowser running. i did notice that in the log i had the following

    2007-08-16 18:50:38.22 Server      Server is listening on [ ‘any’ <ipv4> 2005].

    2007-08-16 18:50:38.22 Server      Server local connection provider is ready to accept connection on [ \.pipeSQLLocalMS2005 ].

    2007-08-16 18:50:38.22 Server      Server named pipe provider is ready to accept connection on [ \.pipeMSSQL$MS2005sqlquery ].

    2007-08-16 18:50:38.24 Server      Server is listening on [ 127.0.0.1 <ipv4> 1631].

    2007-08-16 18:50:38.24 Server      Dedicated admin connection support was established for listening locally on port 1631.

    2007-08-16 18:50:38.28 Server      SQL Server is now ready for client connections. This is an informational message; no user action is required.

    which seems i think to say that the server maybe listening on different ports and get confused or am i missreading this

    anyway any help would be appreciated.

    keith

  37. Matt Neerincx (MSFT) says:

    This looks normal to me.  You main port is 2005 and your admin port is 1631.  I suspect your firewall is blocking UDP port 1434 and this causes dynamic lookup of the port to fail.  Open up UDP port 1434 and it should work.

  38. Matt Neerincx (MSFT) says:

    This looks normal to me.  You main port is 2005 and your admin port is 1631.  I suspect your firewall is blocking UDP port 1434 and this causes dynamic lookup of the port to fail.  Open up UDP port 1434 and it should work.

  39. keith says:

    hi matt

    We have the firewall switched off on the server so it cant be the firewall blocking anything.

    i have looked at all that. as i said if we can connect but we have to explicitly state the port even though sqlbrowser is working’

    keith

  40. Matt Neerincx (MSFT) says:

    Try starting sqlbrowser from command prompt like so:

    net stop sqlbrowser

    cd "C:Program FilesMicrosoft SQL Server90Shared"

    sqlbrowser -c

    This will show you in real time when incoming requests hit the browser.

    Next, open notepad on the same machine where SQLBrowser resides and add following script code:

    ‘ START SCRIPT

    set conn = createobject("adodb.connection")

    conn.open "provider=sqlncli;data source=.test;integrated security=sspi;"

    ‘ END SCRIPT

    Save this to c:testbrowser.vbs, then run:

    cscript c:testbrowser.vbs

    You should see the following in browser console mode:

    C:Program FilesMicrosoft SQL Server90Shared>sqlbrowser -c

    SQLBrowser: starting up in console mode

    SQLBrowser: starting up SSRP redirection service

    SQLBrowser is successfully listening on 0.0.0.0[1434]

    [3480]: Waiting for next request…

    [3480]: Received request…

    [3480]: Waiting for next request…

    [3480]: Received request…

    [3480]: Waiting for next request…

    [5092]: Waiting for next request…

    [4304]: Waiting for next request…

    [4144]: Waiting for next request…

    [3480]: Received request…

    CLNT_UCAST_INST w/refresh from 123.123.123.123[4234]

    CLNT_UCAST_INST from 123.123.123.123[4234]

    [3480]: Waiting for next request…

    [3480]: Received request…

    CLNT_UCAST_INST w/refresh from 123.123.123.123[4235]

    CLNT_UCAST_INST from 123.123.123.123[4235]

    [3480]: Waiting for next request…

    [3480]: Received request…

    CLNT_UCAST_INST w/refresh from 123.123.123.123[4236]

    CLNT_UCAST_INST from 123.123.123.123[4236]

    [3480]: Waiting for next request…

    [3480]: Received request…

    CLNT_UCAST_INST w/refresh from 123.123.123.123[4237]

    CLNT_UCAST_INST from 123.123.123.123[4237]

    [3480]: Waiting for next request…

    [3480]: Received request…

    CLNT_UCAST_INST w/refresh from 123.123.123.123[4238]

    CLNT_UCAST_INST from 123.123.123.123[4238]

    [3480]: Waiting for next request…

    [3480]: Received request…

    CLNT_UCAST_INST w/refresh from 123.123.123.123[4239]

    CLNT_UCAST_INST from 123.123.123.123[4239]

    [3480]: Waiting for next request…

    [3480]: Received request…

    CLNT_UCAST_INST w/refresh from 123.123.123.123[4240]

    CLNT_UCAST_INST from 123.123.123.123[4240]

    [3480]: Waiting for next request…

    [3480]: Received request…

    CLNT_UCAST_INST w/refresh from 123.123.123.123[4241]

    CLNT_UCAST_INST from 123.123.123.123[4241]

    [3480]: Waiting for next request…

    [3480]: Received request…

    CLNT_UCAST_INST w/refresh from 123.123.123.123[4242]

    CLNT_UCAST_INST from 123.123.123.123[4242]

    [3480]: Waiting for next request…

    This will confirm that sqlbrowser service is listening at least locally.

    Next on a remote machine, run the following script (change MySQLServer to your actual target server).

    ‘ START SCRIPT

    dim conn, serverName

    serverName = "MySQLServer"

    wscript.echo "Attempting to use SNAC driver first, which will send browser request even locally…"

    set conn = createobject("adodb.connection")

    conn.ConnectionTimeout = 5

    on error resume next

    conn.open "provider=sqlncli;data source=" & serverName & "test;integrated security=sspi;"

    wscript.echo err.description

    on error goto 0

    set conn = nothing

    wscript.echo "Attempting to use SQLOLEDB driver next, but it will not send browser request locally…"

    set conn = createobject("adodb.connection")

    conn.ConnectionTimeout = 5

    on error resume next

    conn.open "provider=sqloledb;data source=" & serverName & "test;integrated security=sspi;"

    on error goto 0

    wscript.echo err.description

    set conn = nothing

    ‘ END SCRIPT

    Examine console output from browser to see if it makes it through.  

  41. Michael says:

    Hi All, connecting to a server running Windows Server 2003 Enterprise. Have SQL Server 2005 running nicely. Can connect from an asp page (connecting string below) from one server, but not another… thoughts?

    String:

    mydb="PROVIDER=MSDASQL;DRIVER={SQL Server}; SERVER=<server>.<domain>.xyz;DATABASE=DemoSolomonApp60; UID=****;PWD=******;"

  42. Ramy says:

    i’m using sql server 2005 developer edition with VS2005 team suite when i’m trying to add connectiong using sql server database file it gives me error

    "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: SQL Network Interfaces, error: 25 – Connection string is not valid)".

    although i allowed all remote connection but this error still appear

  43. Try using connection string like this (set the port number, uid, pwd, and database name appropriately):

    strConnect = "driver={SQL Server}; server=x.x.x.x:1433; uid=usernamehere; pwd=guessme; database=databasenamehere"

    To nicely handle occasional SQL connectivity problems that can break this auto maker’s web applications and a backend leads daemon, I wrote a Visual Basic script to monitor processes and their SQL connectivity, and to restart processes, thereby increasing the systems’ fault tolerance.

    Here is a short description of what the function, below, does. It checks an output file for "SQLexception" errors generated by the Leads daemon. When the SQLexception text is found it then tests the SQL server for signs of life, and then restarts the Leads daemon.

    Code snippet from the script:

    Function TestLog(logfile)

    DIM myCounter, myLog

    myCounter = 0

    myLog = Slurp(logfile)

           TestLog = -1

    if instr(1, myLog, "SQLException", 1) Then

    ‘ kill all Leads daemon PIDs

    Call KillPIDs()

    ‘ copy log file to new name and delete old log file

    Set fso = CreateObject("Scripting.FileSystemObject")

    Set f2 = fso.GetFile(logfile)

    fs.WriteLine Now & " exception found in log file "

    MyNow = Replace(FormatDateTime(Date, vbShortDate), "/", "-") & "_" & Replace(FormatDateTime(Time,vbShortTime), ":",".")

    f2.Copy (logfile & "-" & MyNow & ".err")

    f2.Delete

    why = "’SQLException’ was encountered in file " & logfile & ". I will test the SQL connection every minute and when it is alive I will restart the leads daemon and I will send another email notification stating that the leads daemon was restarted. Otherwise, I’ll send a failure reminder every six hours."

    subj = "SQL Connection Error. Javamon3 killed Leads Daemon"

    Call SendMail(strToAddr, strFromAddr, why, subj)

    ‘ test SQL connection

    Set conn = CreateObject ("ADODB.Connection")

    conn.ConnectionTimeout = 20 ‘wait seconds

    myCounter = 0

    On Error Resume Next

    Err.Raise = 6 ‘ raise any error

    Do

    Err.Clear

    Conn.Open strConnect

    ‘ MsgBox "inside loop Error # " & CStr(Err.Number) & " " & Err.Description

    wscript.sleep (1000 * 60 * 1) ‘ wait 1 minute

    IF Err.Number = 0 Then

    Exit Do

    End If

    myCounter = myCounter + 1

    If myCounter MOD (60 * 6) = 0  Then  ‘ (6 * n) hours have passed

    why = "Leads Daemon has been dead for " & (myCounter/360)*6 & " hours."

    subj = "SQL Connection Error. Leads Daemon has been dead for " & (myCounter/360)*6 & " hours."

    Call SendMail(strToAddr, strFromAddr, why, subj)

    End If

    Loop Until Err.Number = 0

    Conn.Close

    subj = ""

    why = "The SQL connection was re-established. I will now restart the Leads daemon."

    Start(strCommand) ‘ start leads daemon

    TestLog = 1

    End IF

    END Function

    To debug and test this piece of code, I created an IPSEC policy, on my local computer, to block and unblock access to the SQL server. You can either use the command line version described here http://support.microsoft.com/kb/813878 or the GUI MMC version whose setup (for XP) is described here

    http://www.microsoft.com/resources/documentation/windows/xp/all/proddocs/en-us/ipsec_start_snap-in.mspx.

  44. Moff58 says:

    I have worked 3 days trying to connect a remote computer on my workgroup to SQL Server 2005 Express. I have tried dozens of things. I am desesperated. Is SQL Server 2005 Express working at all with workgroups ?

  45. Simon says:

    I just spent an hour trying to get my SQL 2005 Mgmt Studio to connect to the SQL 2000 box sat right across the desk… turned out to be the (on by default) XP Firewall running on the remote box. Thanks for this issue being mentioned somewhere above…

  46. Keith says:

    Hello,

    Could someone explain why I have to turn on File and Printer Sharing to allow a TCP connection to a named sql instance through the XP firewall?  I know it opens more ports in the firewall but why is this necessary?

    There are MS products that use SQL Express (Microsoft Office Accounting 2007)that do NOT require Sharing to be on in order to use the SQL instance remotely.  What are they doing differently?  How are they configuring their instance to permit remote TCP connections through the firewall?  Are Named Pipes required because of connecting to a named Instance?

    Exasperated.

  47. MING LU says:

    Hi, keith

       You do not need to turn on *File and Printer Sharing* if making TCP connection. Can you share your experience that without the sharing, you can not make remote tcp connection against a named instance?

    Thanks!

    Ming.

  48. Keith says:

    Ming,

    After seeing "Turn on File and Printer Sharing" in most of the troubleshooting guides in this blog, combined with my own issues connecting to SQL Server I thought I needed to use F&P Sharing.

    It turns out (I think) that all my connection problems (errors 0, 26, and 40) were due to Active Directory/Domain Controller timeouts when trying to locate and log in to a SQL instance.  

    I work remotely, and the machine hosting the SQL instance is on a virtual machine on my end of our VPN, but the domain controller is on the other end.  The roundtrip required for windows authentication seems to exceed the default login timeout, so I upped the timeout and my problems went away.

    Thanks for the interest in my problem, though!

    A different question: why don’t you need to use a port# when using ODBC to connect to a SQL instance with a static port behind a firewall?

    Keith

  49. Melvin Krom says:

    I have installed several instances on two SQL 2005 servers. Whenever I remote connect between the servers or remote connect from my 2k desktop express client, it fails ( time out the first time), if I try again the connection is made. What do I need to do to eliminate this time out error the first time I connect?

  50. ZUlfi says:

    Dear all,

    I am using the sql server 2005 in remote server, in which the surface are configuration is enabled for tcp/ip, named pipes. I cannot connect the remote server from one of the client machine  but i can pin the server from that client. The firewall of the client is disabled.

    I also able to connect remote server from another client in different physical location and network.

    Please through your thoughts….

    Thanks in advance.

  51. Venerando Villarino says:

    IN MY CASE ……..

    TCP/IP or Named Pipe are enabled.

    SQL Server Browser is active running

    Fire and Printer Sharing,sqlsevr.exe and sqlbrowser.exe are in  Firewall exception list

    browser using port 1433

    from error log:

    Server is listening on [ ‘any’ <ipv4> 1027].

    2007-11-07 19:42:53.20 Server      Server local connection provider is ready to accept connection on [ \.pipeSQLLocalSQLEXPRESS ].

    2007-11-07 19:42:53.20 Server      Server named pipe provider is ready to accept connection on [ \.pipeMSSQL$SQLEXPRESSsqlquery ].

    AND STILL GETTIN THIS STUPID ERROR !!!!!

    This is the error I’m getting……

    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 – An invalid argument was supplied.)

    This is my connection String

    Server=(local);

    Initial Catalog=myDatabaseName;

    User ID=sa;

    PWD=MyPassword;

    I’m thinking to go back to SQL 2000 !!!!!

    Vene

  52. Venerando Villarino says:

    sorry, This is the full error

    A connection was successfully established with the server, but then an error occurred during the pre-login handshake.  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 – An invalid argument was supplied.)

    Note that the connection was successfully established with the server

    more ERRORLOG

    2007-11-07 19:42:53.20 Server      Server is listening on [ ‘any’ <ipv4> 1027].

    2007-11-07 19:42:53.20 Server      Server local connection provider is ready to accept connection on [ \.pipeSQLLocalSQLEXPRESS ].

    2007-11-07 19:42:53.20 Server      Server named pipe provider is ready to accept connection on [ \.pipeMSSQL$SQLEXPRESSsqlquery ].

    2007-11-07 19:42:53.20 Server      Dedicated administrator connection support was not started because it is not available on this edition of SQL Server. This is an informational message only. No user action is required.

    2007-11-07 19:42:53.21 Server      The SQL Network Interface library could not register the Service Principal Name (SPN) for the SQL Server service. Error: 0x54b, state: 3. Failure to register an SPN may cause integrated authentication to fall back to NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies.

    2007-11-07 19:42:53.21 Server      SQL Server is now ready for client connections. This is an informational message; no user action is required.

    2007-11-07 19:42:53.51 spid8s      Clearing tempdb database.

    2007-11-07 19:42:54.06 spid8s      Starting up database ‘tempdb’.

    2007-11-07 19:42:54.11 spid5s      Recovery is complete. This is an informational message only. No user action is required.

    2007-11-07 19:42:54.11 spid11s     The Service Broker protocol transport is disabled or not configured.

    2007-11-07 19:42:54.11 spid11s     The Database Mirroring protocol transport is disabled or not configured.

    2007-11-07 19:42:54.20 spid11s     Service Broker manager has started.

    2007-11-07 19:50:51.81 Server      The time stamp counter of CPU on scheduler id 1 is not synchronized with other CPUs.

    2007-11-07 19:58:00.03 Server      Server resumed execution after being idle 872 seconds: user activity awakened the server. This is an informational message only. No user action is required.

    2007-11-07 19:58:00.28 Logon       Error: 18456, Severity: 14, State: 16.

    2007-11-07 19:58:00.28 Logon       Login failed for user ‘sa’. [CLIENT: 192.168.11.5]

    2007-11-07 19:58:30.93 Logon       Error: 18456, Severity: 14, State: 16.

    2007-11-07 19:58:30.93 Logon       Login failed for user ‘sa’. [CLIENT: 192.168.11.5]

    Vene

  53. Matt Neerincx (MSFT) says:

    From the state code of 16, this means there were issues opening/using the default database for the incoming user.  Try logging into a different database like master or try another user (try integrated login and local admin of machine for example).

  54. Jared says:

    This is a piece of gold bros, it saves may days out of trouble and keep me alive in the business, I thank you very big for the help I got, as I said piece of gold.

    This is greate place to get noble ideas.

  55. Ratish says:

    Dear Sir,

    I am facing this above problem to connect the SQL SERVER 2005 it pops up a error saying Remote Connection not allowed.

    It is listening 127.0.0.1 <ipv4> 3344 port

    Everything Is Enabled

    TCP/IP or Named Pipe are enabled.

    SQL Server Browser is active running

    Fire and Printer Sharing,sqlsevr.exe and sqlbrowser.exe are in  Firewall exception list

    browser using port 1433

    Kindly advice.

  56. Tres London says:

    How are you trying to connect? Are you able to connect with Management Studio? Have you tried connecting with osql? Have you checked your error log for any clues? If you are able to connect with Management Studio and unable to connect through ASP.NET, it may be a problem with your connection string or something like that.

    HTH

  57. Blair Rorani says:

    *Here is a copy of my error message. I have a default instance installed, login using sa and password*

    TITLE: Connect to Server

    ——————————

    Cannot connect to [my server].

    ——————————

    ADDITIONAL INFORMATION:

    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: SQL Network Interfaces, error: 26 – Error Locating Server/Instance Specified) (Microsoft SQL Server, Error: -1)

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=-1&LinkId=20476

  58. Zayn says:

    Hello,

    I am running SQL 2005 NAMED INSTANCE on windows 2003 cluster on PORT 1433 with SP2.

    When i connect from Server’s Management studio (ServerNameinstanceName), i am able to connect BUT when i try to connect to that remote Server from a client’s Management Studio, i am not able to connect unless i put the port number in front (ServerNameinstanceName, port#). I know there is a work around by using the alias BUT IS THERE ANY WAY I CAN FIX THIS WITHOUT USING THE ALIAS AND WITHOUT CHANGING THE PORT NUMBER.

    I will really appreciate your help.

    Thanks,

    Zayn

  59. Bas Wanders says:

    Very helpful article, solved my problems with remote connectivity.

    Thanks!

  60. Manoj says:

    Good Comments and usful blog. It gaves us all kinds of SQL error messages while connection issues.

    Thanks Everyone again for your inputs

  61. Anuj says:

    Hi,

    I am trying to connect to a OLAP Remote Server and I am getting the error message that you are not authenticated User.

    Please Give me some Idea

    Regards,

    Anuj

  62. 3pThank’s.8y I compleatly disagree with last post .  bqj

    <a href="http://skuper.ru">ламинат и паркет</a> 9y

  63. Dr. Debug says:

    I had figured out the solution to this last year but only made it on my main dev system.  My laptop had the same problem and after hours of searching I finally recalled the ‘trick’: In SQL Server Configuration Manager under define an Alias under the Client Configuration, add a value in the Alias section that references the target server you are trying to access and the port, along with TCP as the protocol. In my case the port for SQLExpress 2005 on the target is 3306.  Insure your Hosts file in WindowsSystem32driversetc has the same name of the server you specify and then every thing will work.  

    This is so frustrating – but this is a good blog to help in trouble shooting the problem.

  64. Allan says:

    Hello,

    I have 2K3 Server and Sql Server 2005.

    I can connect from a machine witch is not on the domain to the database engine on the server. But, when I join the machine to the domain (now the machine is on the domain) I cannot connect?

    Any ideas?

    Thank you,

  65. Vijay Chandra says:

    I have developed an windows form application using visual basic 2005 (professional) and sql server 2005 (express) database. This is working fine ( single user ). Now the client has got in for expansion within the city limits and would like to add another 5 client systems. As the application is already created is it possible by just changing the connection string to point to the server at the head office. Report are created using reportviewer.

  66. jajal.ketan says:

    Hi,

    I have installed SQL Server and not able to access it from the local area network

    I tried to telnet the sql server but it failed

  67. ebailey says:

    SQL 2005

    Cannot connect from external point of sale station in one of our stores.

    SQL server is behind ASA firewall with appropriate port opened.

    Verified settings (server and remote client) as per SQL Server 2005 Remote Connectivity Issue troubleshoot doc (by the way thanks for the info)

    Test data sent from remote client and receiving the following error

    2009-04-01 12:45:17.25 Logon       Length specified in network packet payload did not match number of bytes read; the connection has been closed. Please contact the vendor of the client library. Followed by IP address of remote client

    Event App log displays:

    MSSQLSERVER

    Category:4

    Event ID: 17836

    Length specified in network packet payload did not match number of bytes read; the connection has been closed. Please contact the vendor of the client library

    Any help would be appreciated

  68. ebailey: Can you contact us thorugh the contact form at the top of the blog?  

    http://blogs.msdn.com/sql_protocols/contact.aspx

    Thanks,

    Brad Sarsfield

  69. Maggie B says:

    I am moving a sql 2000 database to a new windows 2003 server running sql 2005.  My issue is with a remote SQL 2000 server that I connect to via tcp/ip.  I have no problem connecting and viewing the databases on my old sql2000 server.  On the new 2005 server I can connect to the remote server, and see and expand all the objects – but cannot expand the databases in Management Studio.  I get the following error:

    Failed to retrieve data for this request. (Microsoft SQLServer.SmoEnum)

    Additional Information: An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

    A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 – The semaphore timeout period has expired.) (Microsoft SQL Server, Error 121)

    I’m stumped….

  70. jm says:

    A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: TCP Provider, error: 0 – The semaphore timeout period has expired.)

    Can anybody help me in troubleshooting the error mentioned above. It’s already running for few months and been searching for few months also but no possible solution.

    This SQL Server 2005 running on Win Server 2003 remotely. What’s the worse part is that connectivity is very unstable. Once it is normal my web application and remote access using SQL Management Studio runs very smoothly and if the issue starts, everything fails.

    It’s very, very frustating on my part because we are not able to solve the issue.

    Any suggestion is highly appreciated. TIA

  71. jm says:

    Oh and by the way, we’ve already tried replacing the Server NIC and Network Switch on the Remote Network where this Server is Located.

    Thanks again.

  72. allen says:

    Hi, My SQL Server Browser Service is disabled, and i use Vista Home Basic, pls how can i Enable it.

  73. Frank I. says:

    We have just recently upgraded to Sql2005 from 2000.

    Our main application uses services which are written in Ms Java 6.0.  One service in particular is used very frequently to receive data over a port and enter it into the application database.  We use java and com classes in com.ms.wfc.data.*.  In particular we use com.ms.wfc.data.Connection to create a Connection to the database.  We are experiencing com error "Failed remote creation of coclass com/ms/wfc/data/adodb/Connection" We are using  a connection string like "Driver={SQL Server};SERVER=FINFANTE;DATABASE=MegaCare;UID=MegaCareUser;PWD=pwd;APP=Infinity MegaCare;"

    What is happening is that after creating and using several Connections successfully, and storing data in the db successfully, then after a short period like a minute or 2, the next attempt to create a Connection fails with the com error.

    Nothing like this had ever happened with sql 2000.

    We have tried using different drivers and/or providers and are still getting the same error.

    We have enabled all network protocols except VIS.  We have exceptions in Windows firewall for SQlBRowser.exe and File and Printer Sharing.

    Any clues about how to proceed debugging would be greatly appreciated.

  74. orhan says:

    hi i have big problem and i can’t solve it.

    My applications (vb6) connected to (sql server 2005 sp1 and windows 2003 server sp1) with msdaSQL (oledb).

    And i downloaded windows 2003 sp2 and sql server 2005 sp3. then apllications cannot connect to sql server 2005. (sql state 01000 and sql error : 14). Only sql native client connect to database. what happened and how to solve it.

  75. orhan says:

    i found a solution. Network card driver uptaded and it solved. Waavv.Machine hp ml570 and driver 1 gb nic ethernet driver from hp sites.  Be carefull updateing from microsoft. i hope i helped many person.

  76. Gareth says:

    There is no such command as ‘netstate’ on windows.

  77. Indeed, that is a typo in the article. It should read "netstat". Thanks for catching it!

  78. Latha Lakshmanan says:

    While connecting to the server Database Engine tab is not enabled. i cant able to connect with other sever.

  79. Martin says:

    Hi, thanks, it helped me. Martin

  80. Thank you very much for the concise background and comprehensive step-by-step troubleshooting advice.  Much appreciated!

  81. Parham says:

    i have sql server express 2008 on windows server 2008 the port which server listen to is 1433 and the server name lets say is server2893. i made a database and i'm using plesk 9.5

    the information of the data base is

    DBName : test_DB

    user : dbtestUser

    pass : visitpassw00rD

    —————

    now tell me wht i should put as username, password and server name =/ whtever i try is nt working

  82. luis_arenal@hotmail.com says:

    Hi,

    My problem trying to connect to a SQL Server 2008 remotely. I made a connection to the same server in other networks but in the one at home I recieve "Error: 26 – Error Locating Server/Instance" from the management studio.