July 18, 2006 at 8:39 am
Hello,
I'm trying configuring SSIS to allow remote connections from management studio for non-admin account but get the following error:
Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)
Connect to SSIS Service on machine "XYZ" failed:
Access is denied.
I tried differnet solutions posted on the inet, such as reconfigure permissions for MsDtsServer in Dcom components, editing MsDtsSrvr.ini.xml file -- I added servername\instance_name there, I added account to SQLServer2005DTSUser$ local group. Nothing helps.
This not networking issue because at the moment when I'm adding user acount to Administrators group on the SSIS server the connection starts working.
I would be very grateful for any help.
Thanks,
Gary.
July 21, 2006 at 8:00 am
This was removed by the editor as SPAM
August 17, 2006 at 11:19 am
I have only been working with this a short time but I found the using the SQL Destination has problems when trying to connect to non-local servers. I have replaced my destinations with OLE DB Destinations. I hope that may help.
Thomas Lane, PhD
DBA
Edgenet / Big Hammer Data
SQL Lane
August 17, 2006 at 11:39 am
I can back up what Thomas has said - I too have only been using SSIS for about 5-6 packages, but have moved to using OLD DB Destination and Source. Also, I found, when struggling with some of the problems I had, that there were several people out there recommending the use of OLE DB over SQL Destination.
August 29, 2006 at 1:31 pm
I'm having the exact same issue as the original thread author. I have followed MSDN's steps for DCOM config, used individual user accounts in the Distributed COM Users group and the group in the DCOM Config MsDtsServer security settings, but I still get:
====================================
Cannot connect to %servername%
Additional information:
- Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)
- - Access is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED)) (Microsoft.SqlServer.ManagedDTS)
=======================================
Again, adding myself to the local Admin group works as expected but upon removal, I get the above error. The DCOM steps did clear the initial blanket "Access is denied" message, but left me with this one...and no answers anywhere. Anything would be appreciated. Thanks!
August 29, 2006 at 5:47 pm
After add my login to DCOM config (local activation), I am still having the same issue, the following are details:
===================================
Connect to SSIS Service on machine "xyz" failed:
Access is denied.
.
------------------------------
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)
August 30, 2006 at 8:10 am
After posting to the Microsoft Partner Newsgroups and getting the same textbook answer, I tried one more thing -- and it worked!
In addition to the posted Launch and Activation rights that a user needs to connect remotely, the user also needs "Remote Access" rights in the "Access Permissions" section of the DCOM component (Component Services > My Computer > DCOM Config > MsDtsServer > Properties > Security (tab)). Once I gave the account Remote Access rights, I was able to connect without local admin.
Please comment if this is ill-advised, but for now, it works and seems less dangerous than local admin. Thanks!
September 3, 2006 at 9:27 am
Hello to All,
Thanks for your answers.
The procedure posted by Chris is worked for me either.
I assume it can be cosidered as best know method.
thanks
Gary.
July 17, 2007 at 12:03 pm
This worked for me as well. Thanks for posting.
March 23, 2008 at 4:41 am
Hi,
Add NT Login ID to Distributed COM Users Groups of SQL Server.It is Working form me.
TnR
Kumar
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply