Problem with Connecting to Remote SSIS Server
-
Hi,
I have a SQL 2005 server with SSIS service at a remote site. I created a local account at the remote server with same login ID and password as the account I use in local office. So my computer is able to see SSIS at the remote server. However I can't connect to the SSIS. I got the error:
Cannot connect to server xxx.xxx.xxx.xxx
Additional information:
Failed to retrieve data for this request. (Microsoft.SQLServer.SmoEnum)
Connecto to SSIS Service on machine :xxx.xxx.xxx.xxx" failed:
The object exporter specified was not found.
I am stuck here. Please help.
Thanks.
-
This was removed by the editor as SPAM
-
If you are getting the dreaded “Connect to SSIS Service on machine <MachineName> failed: Access is denied” error when you connect, check the security settings on the server machine as follows:
<O
> </O>Windows 2003 Server (SP1) and Windows XP (SP2)<O
></O><O
> </O>- If the user running under non-admin account it needs to be added to Distributed COM Users group
- Run %windir%\system32\Com\comexp.msc to launch Component Services
- Expend Component Services\Computers\My Computer\DCOM Config
- Right click on MsDtsServer node and choose properties
- In MsDtsServer Properties dialog go to Security page
- Configure your settings as described bellow
- Restart SSIS Service
<O
> </O>In the Security page we are interested in “Launch and Activation Permissions” section. Click Edit button to see “Launch Permissions” dialog.
<O
> </O>“Launch Permissions” dialog allows you to configure SSIS server access per user/group. In the bottom of the dialog you can select:
- Local / Remote Launch permissions if you allow to a user/group to start service locally or remotely.
- Local / Remote Activation permissions if you allow to a user/group to connect to SSIS server locally or remotely.
<O
> </O>Here are few examples of SSIS server configurations:
<O
> </O>Enable Remote Access<O
></O><O
> </O>By default low privileged users can only connect to SSIS Server on the local machine when the service already started. It is shown by the fact that only Local Activation checked for Machine\Users group. To grant the user permission connect to the running server remotely – check remote activation.
<O
> </O><O
> </O>Control Who Can Start the Service <O
></O><O
> </O>Normally SSIS Server can be started by a member of Administrators group either locally or remotely. It can be started either using conventional ways or by calling a method on an enabled SSIS service.
<O
> </O>In some situations you may want to share these rights with low privileged users or revoke these rights from an administrator. It can be done by checking/un-checking Local/Remote Launch check boxes.
<O
> </O><O
> </O>Windows 2000 SP4<O
></O><O
> </O>- Run dcomcnfg.exe
- On Applications page of “Destributed COM Configuration Properties” dialog select MSDTSServer and click Properties button
- Select Security page
- Configure your settings as described bellow
- Restart SSIS Service
<O
> </O>Security settings on Win2K are a little deferent then on Win2K3 or WinXP. There are 2 separate dialogs to configure Access Permissions and Launch Permissions. Unfortunately you can’t distinguish between remote and local access. By default on this platform any members of Machine/Users group have remote/local access and launch permissions.
<O
> </O><O> </O>Best Practice to Manage Remote Users for a SSIS Server
<O
> </O>We recommend creating one or several windows groups (Distributed SSIS Users, etc) that you can use to control remote access using steps described above. You can also use the same groups to control access to packages stored in SSIS Store.
<O
> </O>Unfortunately groups can’t be used when you need to add a user to Distributed COM Users group. Users should be added to the group one by one.
<O
> </O>Local User Access to a Remote SSIS Server
<O
> </O>If you are running under a local user account on your client machine, you can still access remote SSIS server under the following conditions:
<O
> </O>- The machine running remote SSIS server should have an account with exactly same name and password as the one you are running under.
- The account should be configured to access SSIS server as described above.
<O
> </O><O
></O>Delegation Problems
<O
> </O>SSIS 2005 doesn’t support scenario when a client, SSIS Server and underlying SQL server running on 3 different machines.
<O
> </O>This scenario can emerge when you change default MsDtsSrvr.ini.xml to point to a remote SQL server (for example when you implement a shared SQL Store or want to separate SQL Server and SSIS Server for security reasons).
-
Thanks for the reply. However it was not the problem I got. I actually did got the access denied error so I followed instructions to setup remote launch/remote activation then I'd gone further than that. I got authenticated, no more access denied problem. Below is the error I got:
Cannot connect to server xxx.xxx.xxx.xxx
Additional information:
Failed to retrieve data for this request. (Microsoft.SQLServer.SmoEnum)
Connecto to SSIS Service on machine :xxx.xxx.xxx.xxx" failed:
The object exporter specified was not found.
-
Just wondering if you ever got a solution to this problem.
I have 2 workstations that are having this exact same problem. However, I have 2 more workstations that are working just fine. From testing different userid's on different workstations I'm pretty certain that it's not a userid or server permissions problem, however I've yet to determine the problem. All machines are going through a firewall and i set MsDtcServer to use a static endpoint (i selected 3882). I had ports 135 and 3882 opened on the firewall, however 2 machines continue to give the message:
"Failed to retrieve data for this request. (Microsoft.SQLServer.SmoEnum)
Connecto to SSIS Service on machine :xxx.xxx.xxx.xxx" failed:
The object exporter specified was not found."
When I had the firewall group look at the logs, I can see the working workstations using ports 135 and 3882, however the non-working workstations are using 135 and a random port. I think that this is the problem but can't figure out why they are not using the static return endpoint port of 3882.
Any thoughts?
-
I haven't got this fixed. My boss said he would never open port 135 at the firewall!
-
After working on this problem for days, I happened to stumble upon the answer (in my case anyway). The workstations that were not working did not have name resolution working, so we were using the IP address (which worked for connecting to the database instance, but not for integration services). But adding an entry to the local hosts file seems to have solved the problem.
-
Thx for the update. It doesn't work in my case. The local host file was updated with an entry for the remote SSIS server. However the port 135 is blocked so I have no luck.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply