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

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

    Thanks!

    Ming.

  • Hi

    Just wondering if you can help me. I have Cisco CSA picking up the following message.The process 'C:\Program Files\Microsoft SQL Server\90\Shared\sqlbrowser.exe' (as user NT AUTHORITY\NETWORK SERVICE) attempted to accept a connection as a server on UDP port 1434 from 172.16.71.132 using interface Wired\Broadcom 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

  • 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

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

  • 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>,port\instance" format when trying to connect to the publisher. Please hhhhheeeeeelllllppppppp me!!!

    I have had long hours on this challenge.

    Regards.

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

    2007-08-16 18:50:38.22 Server      Server named pipe provider is ready to accept connection on [ \\.\pipe\MSSQL$MS2005\sql\query ].

    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

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

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

  • 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

  • Try starting sqlbrowser from command prompt like so:

    net stop sqlbrowser

    cd "C:\Program Files\Microsoft SQL Server\90\Shared"

    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 Files\Microsoft SQL Server\90\Shared>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.  

  • 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=******;"

  • 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

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

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

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

Page 3 of 6 (82 items) 12345»