Welcome to the SQL Server Protocols blog site

Welcome to the SQL Server Protocols blog site

  • Comments 39

Ever get a GNE?  Ever wonder why SQL Discovery doesn't seem to be consistent?  Ever get trapped by Window's firewall?  Confused about VIA?  Then, this is the blog site for you to watch.

This blog is run by the SQL Server protocols component team.  This is the team responsible for the networking in SQL Server.  This includes Discovery (SSRP), TDS, SOAP, transports like TCP, Named Pipes, and VIA.  In this blog we hope to proactively answer many of the recurring issues that we see our customers running into.  We should be able to tell you how to do some of the more subtle configurations and how to diagnose issues you're running into more efficiently (We've added TONS of diagnostics in SQL Server 2005).  We'll show you how to use them.

Thanks and hopefully you'll find this useful!

The SQL Server Protocols team

Disclaimer: This posting is provided "AS IS" with no warranties, and confers no rights

Leave a Comment
  • Please add 3 and 5 and type the answer here:
  • Post
  • This is cool! Anyone who has ever had to wrestle connectivity issues with SQL 2000 should watch this site!
  • I am trying to connect to the SQL Server Integration Services on my
    back end 2005 database server through SQL Management Studio. When I try
    to connect I am receiving the following errors:


    TITLE: Connect to Server
    ------------------------------


    Cannot connect to server.


    ------------------------------
    ADDITIONAL INFORMATION:


    Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)


    For help, click:
    http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=2...


    ------------------------------


    The RPC server is unavailable. (Exception from HRESULT: 0x800706BA)
    (Microsoft.SqlServer.DTSRuntimeWrap)


    ------------------------------


    The RPC server is unavailable. (Exception from HRESULT: 0x800706BA)
    (Microsoft.SqlServer.DTSRuntimeWrap)


    The server resides in a different subnet and it's behind a firewall. I
    have verified that a station running SQL Management Studio in the same
    subnet can connect to the Integration Services on the server in
    question without incident.
    I have worked with our firewall administrator and we have opened up the
    recommended ports to no avail. We have even gone so far to remove all
    rules for my IP address to that destination server.
    Is there anyone that has seen this before? Any advice would be greatly
    appreciated. Thanks in advance,


    Drew Flint
    afli@plex.com
    System Administrator
    Plexus Systems
  • Me too have the similar problem, i am trying to connect to the sql server instance from reporting services configuration tool, and my web server is out side the firewall and my DB server is inside. Please help regarding this.

    Thanks,
    Goud.
  • In both of your cases the server is remote from the client.

    So in this case, the first thing to do is figure out the target server name, it's IP address, and the port number for the SQL instance.

    Then a simple basic test is try telnet to target ip and port from client.  For example:

    telnet 123.123.123.123 1433

    If you get failure here like below, then your problem is very likely a firewall issue:

    C:\>telnet 157.55.120.141 1433
    Connecting To 157.55.120.141...Could not open connection to the host, on port 1433: Connect failed

    If this fails, then go to the SQL server machine and try the same thing locally.  If it works locally and not remotely, then it's a firewall issue.

    If telnet connects remotely, then it is not a firewall issue, but some other problem, for example it could be a SQL port resolution issue.  SQL will attempt to resolve the port of a named instance using the UDP port 1434, this port could be blocked.

    You can work around this UDP 1434 port blocking issue by modifying your connection string to include the correct target port.

    So for example when prompted for the server name instead of entering:

      servername

    Use this syntax:

      tcp:servername,5555

    This will give a hint to the driver to tell it what port to use and what protocol to try, this is a common technique we use to check if we can connect without UDP 1434 open.  You can also use this technique if your admin is reluctant to open UDP 1434 port.
  • I have the same "RPC server is unavailable" error. I can connect to the server for Analysis Services and Database Engine, just not SSIS ........    What's going on ?
  • Regarding SSIS remote connection - please see
    http://sqljunkies.com/WebLog/knight_reign/archive/2006/01/05/17769.aspx
  • Also, if you have firewall, you need to configure it. SSIS is using DCOM protocol, see Books Online Topic "How to: Configure a Windows Firewall for Integration Services Access", also at
    http://msdn2.microsoft.com/en-us/library/ms141198.aspx
    http://msdn2.microsoft.com/en-us/library/ms137861(SQL.90).aspx
    (the URLs may change).
  • I have diffrent case.. where i connect ssis through server.. but it give the message
    any idea?

    ===================================
    Cannot connect to server.
    ===================================
    Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)
    ------------------------------
    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476
    ------------------------------
    Program Location:
      at Microsoft.SqlServer.Management.Smo.Enumerator.Process(Object connectionInfo, Request request)
      at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.ObjectExplorer.ValidateConnection(UIConnectionInfo ci, IServerType server)
      at Microsoft.SqlServer.Management.UI.ConnectionDlg.Connector.ConnectionThreadUser()

    ===================================

    Connect to SSIS Service on machine "server" failed:
    Error loading type library/DLL.
    .


    ------------------------------
    Program Location:

      at Microsoft.SqlServer.Dts.Runtime.Application.GetServerInfo(String server, String& serverVersion)
      at Microsoft.SqlServer.Dts.SmoEnum.DTSEnum.GetData(EnumResult erParent)
      at Microsoft.SqlServer.Management.Smo.Environment.GetData()
      at Microsoft.SqlServer.Management.Smo.Environment.GetData(Request req, Object ci)
      at Microsoft.SqlServer.Management.Smo.Enumerator.GetData(Object connectionInfo, Request request)
      at Microsoft.SqlServer.Management.Smo.Enumerator.Process(Object connectionInfo, Request request)

    ===================================

    Connect to SSIS Service on machine "server" failed:
    Error loading type library/DLL.
    .


    ------------------------------
    Program Location:

      at Microsoft.SqlServer.Dts.Runtime.Wrapper.ApplicationClass.GetServerInfo(String bstrMachineName, String& serverVersion)
      at Microsoft.SqlServer.Dts.Runtime.Application.GetServerInfo(String server, String& serverVersion)

    Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476

    ------------------------------

    Connect to SSIS Service on machine "server" failed:
    Error loading type library/DLL.
    .


    ------------------------------

    Connect to SSIS Service on machine "server" failed:
    Error loading type library/DLL.
    .


    ------------------------------
    BUTTONS:

    OK
    ------------------------------
  • I have a similar problem. I get connected to the database, but sometimes when i try to modify a store procedure or try to open a new query i get the following error message.
    Though the database is situated in the same development machine.

    Message:

    TITLE: Microsoft SQL Server Management Studio
    ------------------------------

    Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476

    ------------------------------
    ADDITIONAL INFORMATION:

    Failed to connect to server vinod. (Microsoft.SqlServer.ConnectionInfo)

    ------------------------------

    A connection was successfully established with the server, but then an error occurred during the login process. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.) (Microsoft SQL Server, Error: 233)

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=233&LinkId=20476



    And there is no much information in the sites recommended in the message.
  • I have an interesting issue. This is a two node SQL 2000 sp3a cluster running on Windows Server 2003 sp1. Node 1 is able to handle UDP 1434 port lookups fine. When any instance fails over to Node 2 UDP stops working. I validated that this is not an IPSec or firewall issue. Running a packet capture from any client and on the server itself show the initial UDP request from the client, but no response from the server. Netstat shows that it is listening on UDP 0.0.0.0:1434. The PID for this open connection is different than the PID for any SQL Server instances running and does not show up with either a TLIST or from task manager. When I try to kill this PID the system does not allow it.

    Any idea how to free up this issue without rebooting the machine? I'm curious how it got in this state so that I can prevent it from happening again. Are there any log files or additional diagnosticts I can enable? I don't see any event log errors or anything in the ERRORLOG.

    Can I install the SQL Server 2005 Browser service into this cluster standalone since it is backward compatible?

    Anything else anyone can think of?

    Thanks,
    Dan Wilson
  • Looks like you have a zombie process left over on Node 2. Can you try fail over your cluster to Node 1 and see if the process is still there? You can try using process explorer (downloadable from http://www.sysinternals.com/) to kill it. When other process is hold the UDP port 1434, sql browser won't function.

    You cannot install SS2005 browser standalone. SQL browser must be installed with SQL Server 2005 and/or SQL Server Analysis Services. If the zombie process does not go away, SQL Browser won't work as well.

    Thanks
  • I looked for the process with process explorer; it doesn't exist. I also tried out their TCPVCON tool and sure enough the port is open, but no process is listening on the other end.

    C:\Documents and Settings\DanW>netstat -ano | findstr 1434
     UDP    0.0.0.0:1434           *:*                                    5948

    C:\>tcpvcon -a 5948

    TCPView v2.34 - TCP/UDP endpoint lister
    Copyright (C) 1998-2003 Mark Russinovich
    Sysinternals - www.sysinternals.com

    [UDP] <non-existent>
         PID:     5948
         Local:   usseadb14:ms-sql-m
         Remote:  *:*
    [UDP] <non-existent>
         PID:     5948
         Local:   usseadb14:2215
         Remote:  *:*


    Does anyone know how to close a port when the process that opened the port no longer exists?
  • Hi,

    I am facing the TimeOut Expired issue. As given, I tried "telnet 123.123.123.123 1433" after replacing the IP address and I get an error 'Connect failed' when tried remotely and 'Connection to host lost' when tried locally on the server.

    What do I need to do? Everything was working fine until last week.

    Regards,

    Satish.

  • Hi,

    I am facing the TimeOut Expired issue. As given, I tried "telnet 123.123.123.123 1433" after replacing the IP address and I get an error 'Connect failed' when tried remotely and 'Connection to host lost' when tried locally on the server.

    What do I need to do? Everything was working fine until last week.

    Regards,

    Satish.

  • I am having problems with connecting to an SQL server via telnet. I was getting the connection failed, but resolved that by adding port to TCP in SQL server config. However now the connection just times out without asking for user name or password. It does this locally and remotely.

Page 1 of 3 (39 items) 123