I receive the following error when attempting to connect SSMS to Integration Services using a particular SQL Server cluster's network name:
Connecting to Integration Services service on the computer 'FooDB' failed with the following error: "Access is denied."
This error occurs when the computer has not been configured to allow remote connections through DCOM, or the user does have permission to access the SQL Server Integration Services service through DCOM.
However, I've tried all the solutions I know of, and the problem remains.
In more detail, I've done the following:
Verified that the users connecting have the DCOM permissions listed in the articles linked to above on MsDtsServer100:
Launch and Activation Permissions: Allow Local Launch, allow Remote Launch, Local Activation, Remote Activation
Access Permissions: Allow Local Access, allow Remote Access
Configuration Permission: Allow Read
Confirmed with a packet sniffer that all traffic related to the connection is successfully coming through the firewall. The last packet shown before the TCP connection is torn down is a reply from the server containing the Windows status code for 'access denied' inside an MSRPC header.
Tested adding the users to the 'Distributed COM Users' group and/or the local administrators group, then restarting the servers. This allowed the users to connect to SSIS from SSMS using the local node names (FooDBN1, FooDBN2), but they still get an 'access denied' error when connecting to the the cluster network name (FooDB), which is what they're accustomed to using, and what works on our other clusters.
Also, I haven't found altering membership of these groups necessary on other clusters.
On the other clusters I've checked, I can connect SSMS to SSIS using the cluster name without any non-default configuration.
I realise this might be more appropriate for ServerFault and am OK with the question being migrated if needed, but it's also an SQL Server issue and I think users here might be more likely to have dealt with it before.
- Windows Server 2008 R2 SP1
- SQL Server 2008 R2 SP2
- 2-node active-passive cluster with a single SQL Server instance
Could anyone suggest what I should be looking at next here?
Update: this mysteriously just started working today, but only for members of the local administrators group. Nothing has changed as far as I can tell.