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 7 and 2 and type the answer here:
  • Post
  • 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

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

  • 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

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

  • 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(); } ??

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

  • 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

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

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

  • 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

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

  • 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

  • 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

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

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

Page 1 of 6 (82 items) 12345»