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

  • 3pThank's.8y I compleatly disagree with last post .  bqj

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

  • 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 Windows\System32\drivers\etc 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.

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

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

  • 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

  • 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

  • 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

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

  • 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

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

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

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

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

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

Page 5 of 6 (82 items) «23456