SQL Server 2005 Remote Connectivity Issue TroubleShoot

SQL Server 2005 Remote Connectivity Issue TroubleShoot

Rate This
  • Comments 82

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>\pipe\sql\query. 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 "..\Binn\sqlsevr.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 Files\Microsoft SQL Server\90\Shared\sqlbrowser.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 \\.\pipe\sql\query.

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

 

 

Leave a Comment
  • Please add 4 and 1 and type the answer here:
  • Post
  • 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.

  • 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.

  • 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.

  • 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.

  • 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?

  • 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.

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

  • 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!

  • I have a remote connectivity issue using an alias.  

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

    Alias Name: OPERACIONESDC\ALIAS

    Port No: 1435

    Protocol: TCP/IP

    Server: OPERACIONESDC\DELFOS

    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

  • 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.

  • 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

  • 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

  • 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.

  • 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 ?

  • 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

Page 2 of 6 (82 items) 12345»