Welcome to the SQL Server Protocols blog site

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

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.