Sql-server – “Access is denied” when connecting SSMS to Integration Services

clusteringpermissionssql serverssiswindows

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.

This is a routine problem with a well-documented solution. For example, see the solutions here and here.

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:

    1. Launch and Activation Permissions: Allow Local Launch, allow Remote Launch, Local Activation, Remote Activation

    2. Access Permissions: Allow Local Access, allow Remote Access

    3. 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.

Platform details:

  • 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.

Best Answer

Long shot perhaps, but worth checking file

\Program Files\Microsoft SQL Server\100\DTS\Binn\MsDtsSrvr.ini

or the equivalent on your setup. You may have to manually edit it with the instance name. Otherwise SSIS connections might be looking for an msdb of a default SQL instance that doesn't exist.

Related Question