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

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

  • 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

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

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

  • 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 [ \\.\pipe\SQLLocal\SQLEXPRESS ].

    2007-11-07 19:42:53.20 Server      Server named pipe provider is ready to accept connection on [ \\.\pipe\MSSQL$SQLEXPRESS\sql\query ].

    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

  • 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 [ \\.\pipe\SQLLocal\SQLEXPRESS ].

    2007-11-07 19:42:53.20 Server      Server named pipe provider is ready to accept connection on [ \\.\pipe\MSSQL$SQLEXPRESS\sql\query ].

    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

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

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

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

  • 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

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

  • 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 (ServerName\instanceName), 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 (ServerName\instanceName, 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

  • Very helpful article, solved my problems with remote connectivity.

    Thanks!

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

    Thanks Everyone again for your inputs

Page 4 of 6 (82 items) «23456