September 27, 2006 at 3:08 pm
I want to give a developer access to SSIS to create packages but he's getting the error below. I thought adding him to the DTSADMIN role in MSDB would work, but it doesn't. I'm pretty much a newbie in 2005, so any guidance would be appreciated.
TITLE: Connect to Server
Cannot connect to SQLTEST-02.
ADDITIONAL INFORMATION:
Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)
Connect to SSIS Service on machine "SQLServerName" failed:
Access is denied.
September 28, 2006 at 8:36 am
A few things to check:
If you have a firewall between the developer and the sql server:
open a port for SSIS on the firewall (3882 and an additional port for the return traffic)
switch from using a dynamic return port to a static port:
hkey_classes_root\appid\{F38B7F09-979B-4241-80D9-2EADED02954F}
new reg_mutlistring
Key=Endpoints
Value=ncacn_ip_tcp,0,port# (port# is the port you have open on your firewall for SSIS)
Check MsDtsServer in Component Services:
add all permissions for msdtsserver for the developer account
component services > My computer properties > MSDTC > Security Configuration > Network DTC Access > Allow Remote clients
COM Security > Access permissions > Edit Limits
add all permissions for msdtsserver for the developer account
COM Security > Launch and activation permissions > Edit Limits
add all permissions for msdtsserver for the developer account
Allow inbound
allow outbound
restart MSDTC
restart SSIS
If this does not fix the problem, make sure you have build 2153:
select @@version
Microsoft SQL Server 2005 - 9.00.2153.00
If you do not have that build, apply SP1 and then the post SP1 rollup in the correct order (apply all six patches in the order listed in the aticle below):
rollup - http://support.microsoft.com/Default.aspx?id=918222
If you still can't connect to SSIS, you need to check other things like DNS and account permissions.
Joshua Perry
http://www.greenarrow.net
September 28, 2006 at 9:04 am
Thanks ... I probably should have given some more information to narrow it down. Another developer has "sysadmin" rights, and he doesn't have any problem. Neither do I, also sysadmin. I'm pretty sure it's not a connection problem, but a permission problem that I'm not understanding. I want a non-sysadmin to be able to build packages and work in SSIS on this server. I am at build 2153
September 29, 2006 at 9:37 am
I found the problem. The user's Windows account needed to be in the Distributed COM Users group on the server.
March 26, 2007 at 10:26 am
There may be even more to the story. If you have XP SP2 installed (or likely 2003 SP1 as well) and your system admin hasn't fixed it yet, you may still be blocked from DCOM remote connections. Be sure to do what it says in this article first (chances are you already have and that's why you ended up here)
http://msdn2.microsoft.com/en-us/library/aa337083.aspx
You may also need to add the users to the SQLServer2005DTSUser$computername group as well, but I haven't been able to verify if that is necessary yet.
If you did what it said to get around the access denied message and you're still having the issue, see this first article to help diagnose if your issue is due to system wide limits.
http://support.microsoft.com/?kbid=892500
Don't worry that it says 2003, it works on XP SP2 as well. After you try to remotely attach to SSIS, check the Event Viewer (should be in the System log) on the server, and you should see DCOM errors showing the user trying to do "Remote Activation" and an ANONYMOUS LOGON trying to do a "Remote Access". If you do, you'll need to go into Component Services and edit properties on the computer node. On the COM Security tab click the Edit Limits buttons to make the updates. Here are a few more articles that help explain what is going on.
http://www.microsoft.com/technet/prodtechnol/winxppro/maintain/sp2netwk.mspx#XSLTsection126121120120
http://support.microsoft.com/default.aspx?scid=kb;%5BLN%5D;833977#2020 (this one is for remote debugging, but is along the same vein)
May 7, 2008 at 9:34 am
hi I am having the same problem, and i think adding my login to the Distributed COM Users Properties could be the answer, is there any requirements to do this?
December 18, 2009 at 5:56 am
I had searched a lot for this...!!!
Finally got a one simple solution... 🙂
The user's windows login should be having the admin rights in the machine where the SQL Server resides...
This can be added in the server as follows:
Right click My Computer
--> Managae
--> System Tools
-----> Local Users and groups
--------> Groups
------------> Administrators
Click on Add button and add the windows user id...
Thats it...
December 18, 2009 at 8:10 am
SMNayak (12/18/2009)
I had searched a lot for this...!!!Finally got a one simple solution... 🙂
The user's windows login should be having the admin rights in the machine where the SQL Server resides...
This can be added in the server as follows:
Right click My Computer
--> Managae
--> System Tools
-----> Local Users and groups
--------> Groups
------------> Administrators
Click on Add button and add the windows user id...
Thats it...
I don't think that's a good idea. Major security vulnerability. Usually only Network Administrators should have that permission, and in our case, also the DBA on the SQL servers (some consider that a no-no)
January 21, 2011 at 1:33 pm
Thanks for the info posted here. I got it working but we have both 2005 SP3 and 2008 R2 RTM running on a dev server so when I connect locally it's connecting to the 2005 SSIS but when I connect remotely it's connecting to the 2008 SSIS. How do I change these to be specific, or is running multiple SSIS versions on 1 server disallowed?
Thanks!
Lori
June 24, 2011 at 12:57 pm
Giving permissions to MsDtsServer or MsDtsServer10 DCOM application on the Server fixes this issue.
Please refer to http://Support.Microsoft.Com/kb/2000474 for more details.
Thanks,
Pradeep Deviprasad.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply