SQL Protocols

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.

Server side

1. SQL Server 2005

2. Express

3. TCPIP

4. Yes Server starts successfully

5. Not sure

6. Local System

7. Trying to connect internally. Not going through the firewall.

I am using a remote management software that uses  SQL Express. I can't see or get to the connection string and I can't get it to work. I have two servers, one backup server, one web server. I am tring to connect to the SQL on the backup server from the web server. The vendor of the software asked if I could telnet to the backup server on port 1433. I tried this and the connection failed at first. I added the tcp  port to the SQL on the backup server and also enabled telnet on the backup server. Now when I try to telnet to the backup server from the web server all it does it sit there for a couple of minutes and then comes back to the commend prompt.

First of all, you should immediately un-install Telnet Service.  You don't need the Telnet service to connect to SQL Server.  A running TelNet service is a security concern so please un-install it now.

One way to test if you can connect to SQL Server is to use the telnet client tool from a command prompt.  If you get the blank screen when telnetting to port 1433, then you have proven that there is no firewall blocking port 1433.  

Note to exit the blank telnet screen, press Ctrl and the ] key and this will break you out to a Microsoft Telnet prompt, then enter q to quit.  It took me forever to figure this one out.

So if you can telnet to port 1433 like so:

  telnet MySQLServer 1433

And you get the blank screen, then your firewall is open and you are connecting to SQL and hopefully things will start working.

I have the 'The RPC server is unavailable. (Exception from HRESULT: 0x800706BA)' while configre report server to a remote database server. report server on a stand alone 2003 server with sql 2005. database on a instance of a clusted node, also 2003 server with sql 2005. RPC enabled on both servers. name pipes and tcp/ip enabled, remote access enabled, no firewall. I can see the remote database through the reporting configureation tool but failed at the step:create a grant right script with the following error message:

System.Runtime.InteropServices.COMException (0x800706BA): The RPC server is unavailable. (Exception from HRESULT: 0x800706BA)

  at System.Runtime.InteropServices.Marshal.ThrowExceptionForHRInternal(Int32 errorCode, IntPtr errorInfo)

  at System.Management.ManagementScope.InitializeGuts(Object o)

  at System.Management.ManagementScope.Initialize()

  at System.Management.ManagementObjectSearcher.Initialize()

  at System.Management.ManagementObjectSearcher.Get()

  at Microsoft.ReportingServices.Diagnostics.WebUtil.GetIPAddresses(String machine)

  at ReportServicesConfigUI.Panels.DatabaseSetupPanel.IsLocalDbServer(String dbServer)

  at ReportServicesConfigUI.Panels.DatabaseSetupPanel.SetDatabaseConnectionTask(String connectionString, String server, String database, ConfigurationCredentialsType credsType, String account, String password, Boolean upgrade, String dbVersion)

any suggestion is welcome!

Thanks

Hong

I have the 'The RPC server is unavailable. (Exception from HRESULT: 0x800706BA)' while configre report server to a remote database server. report server on a stand alone 2003 server with sql 2005. database on a instance of a clusted node, also 2003 server with sql 2005. RPC enabled on both servers. name pipes and tcp/ip enabled, remote access enabled, no firewall. I can see the remote database through the reporting configureation tool but failed at the step:create a grant right script with the following error message:

System.Runtime.InteropServices.COMException (0x800706BA): The RPC server is unavailable. (Exception from HRESULT: 0x800706BA)

  at System.Runtime.InteropServices.Marshal.ThrowExceptionForHRInternal(Int32 errorCode, IntPtr errorInfo)

  at System.Management.ManagementScope.InitializeGuts(Object o)

  at System.Management.ManagementScope.Initialize()

  at System.Management.ManagementObjectSearcher.Initialize()

  at System.Management.ManagementObjectSearcher.Get()

  at Microsoft.ReportingServices.Diagnostics.WebUtil.GetIPAddresses(String machine)

  at ReportServicesConfigUI.Panels.DatabaseSetupPanel.IsLocalDbServer(String dbServer)

  at ReportServicesConfigUI.Panels.DatabaseSetupPanel.SetDatabaseConnectionTask(String connectionString, String server, String database, ConfigurationCredentialsType credsType, String account, String password, Boolean upgrade, String dbVersion)

any suggestion is welcome!

Thanks

Hong

I'll see if I can find someone from reporting services to look at this.  In the meanwhile, could you post your question to this newsgroup?

http://www.microsoft.com/technet/community/newsgroups/dgbrowser/en-us/default.mspx?dg=microsoft.public.sqlserver.reportingsvcs

I think you will get better results there.

Rather interesting issue.

SQL Server 2000 does not listen on TCP/IP.

Logs report only listening on Named Pipes.

Both Named Pipes and TCP/IP are enabled.

Both machines (client and server) are VMWare.

Telnet on port 3389 works fine (testing network connectivity) but not on 1433.

Tried disabling and re-enabling TCP/IP without success.

Unable to telnet on 127.0.0.1 port 1433 on SQL server locally.

Kind Regards

Why can't I find anything that explains what I need to do to make this work!!  ARGH!

We don't want to use named pipes.  I can turn on TCP/IP only, but not sure how reporting services will know to connect.  Any help would be very nice.  :)

Travich

I had problem with Connecting to SQL DataBase through SQL Management Studio.

The reason why a can't connect to sql database was that sql server used another port number then SQL Management Studio.

Check this post :

http://www.windows-consulting.net/windows-how_to-problems_connecting_to_sql_database_through_sql_management_studio.htm

Maybe this change help You too.

Best regards

Here's my situation. I have a server (not clustered) with SQL Server 2000 installed on the default instance and SQL Server 2005 on a named instance.  It seems that SSIS was installed on the default instance since SSIS does not support named instance.

I am now getting en error when i am trying to say import a package to MSDB or connect to MSDB... here's the error message:

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

Additional Information:

  The SQL server specified in SSIS service configuration is not present or is not available.  This might occur where there is no default instance of SQL Server on the computer...

Does our setup will definitely cause this problem?

Looking forward to hearing from the forum.

Thanks,

Lhot

I am not able to telnet localhost on 1433....... only thing i can guess is i have a firewall.....please let me know how do i figure out if i have a firewall or not......and how can I put it off...

thanks

meeta

Both client and server resides in the same machine. Am unable to connect to my own machine (server is installed in my machine)

Below is the error output.

TITLE: Connect to Server

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

Cannot connect to HDBLDBSSP3593.

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

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=20476

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

Connect to SSIS Service on machine "HDBLDBSSP3593" failed:

Access is denied.

.

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

Connect to SSIS Service on machine "HDBLDBSSP3593" failed:

Access is denied.

.

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

BUTTONS:

OK

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

Regards,

Venkatesan Prabu . J

venkatesanj@hcl.in

I ma trying to connect to my local sql server using telnet by below command:

C:\>telnet local 1433

and getting below error message:

Connecting To localhost...Could not open connection to the host, on port 1433: Connect failed

We have the following situation:

Win2008 server running SQL2005 and Hyper-v

Win2003 server (virtual machine) running SQL2005

Win2008 server (virtual machine) running SQL2008

Vista Notebook running SQLExpress 2008

Vista Notebook rnning SQL2005

Vista Notebook running SQLExpress 2005

No firewalls anywhere, all SQLs on port 1433, telnet can access port 1433 on all servers, DCOM is properly configured on all machines to allow local and remote access to everyone (dangerous but only temporary for testing purposes), all computers in the same domain.

We are developing a commercial application that uses SQL server, and trying to enumerate all the SQL servers on the network.

All the servers (6 machines) are correctly found by the SqlDataSourceEnumerator GetDataSources function.

We then try to get the correct version, service pack etc by a call to WMI for each machine.

Results are:

Win2008 server OK

Win2003 server HRESULT: 0x800706BA

Win2008 server HRESULT: 0x800706BA

Vista Notebook where the program executes OK

Vista Notebook HRESULT: 0x800706BA

Vista Notebook HRESULT: 0x800706BA

Can you help? We are at our wits end...

Error trying to connect to SSIS from a client outside a firewall.   Connection to DB Engine works fine from outside firewall.  Currently have Port 135 open for SSIS.  The connections from within the firewall all work fine.

Receiving the following error.  

Cannot connect to <Server>

Additional information

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

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

Hi,

I am not able to telnet to SQL Server on port 1433 remotely.

I can telnet locally using telnet localhost 1433.

When I replace localhost with the IP address in the above command (locally) again telnet fails.

to resolve the issue install SQL 2000 SP4.

i support an application that requires sql server 2000 (msde) or 2005 (sql express) be installed on a customers system.

i know how to control the following things when i install sql (ENABLEALLPROTOCOLS=1).  

but in many cases, sql has previously been installed to the customers systems.  in this case, i am growing tired of having to download, install, then use - windows gui tools to CHECK and CHANGE the sql server protocols.

isnt there a direct SQL query that can be executed that will TURN ON TCP\IP and TURN ON NAMED PIPES?

it would take me a few seconds to execute an osql (msde) or sqlcmd (sqlexpress) statement.  that is, if such a statement exists?

i am also aware of how to determine if tcp\ip is presently enabled using the following query:

select net_transport, local_net_address, local_tcp_port

from sys.dm_exec_connections

where session_id = @@SPID;

so im half way there...

all i need to learn is how to turn on tcp\ip and named pipes?  are there any sql experts out there that can supply the necessary sql code?

sincerely,

greg

Hi,

I am having a problem connecting from a desktop Management Studio to a SQL Server 2005 Integration Service.  I believe my problem is with our firewall.  I have gotten the connection to work following the steps on http://support.microsoft.com/kb/940232 on our development environment but in our production environment that is behind the firewall it fails.  According to our network engineer it looks like is trying to use two ports; one static port 135 and then a second port that is dynamic.  We have open port 135 but it is still failing. Is there a way to make the second port static? Both server and client are running with sql 2005 sp 3

Here is the error message:

TITLE: Connect to Server

Cannot connect to p01sql2k5.

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=20476

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

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

Thank you,

Miguel Ramirez

miguel.ramirez@pcbancorp.com

I Have created dll using SMO which contains 3 functions that wil give the dbname and relative data file path and logfile path.

this works in all versions except windows2008sql2008(cluster).what is wrong in windows2008 cluster i did not get.it is working perfectly in Win2003sql2008 cluster.But in win2008sql2008 cluster  it success discovering the databases in 2 out of 6 attempts on average only.i dont know why it happen's

I am trying to access the dll(is created using c#)from c++.