17

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.

Improve this question
6
  • 1
    If you're using the Administrators group, you may be getting bitten by UAC privilege masking. Try creating a new group, or granting individual user permissions directly on the SSIS DCOM application.
    – db2
    Jun 6, 2013 at 19:24
  • If you have a cluster, then users should be connecting to cluster alias, not individual machines. Is it the case?
    – Stoleg
    Jun 10, 2013 at 9:31
  • Yes, they should be using the cluster name, not the individual node name. However, for some reason the error is only occurring when using the cluster name.
    – James Lupolt
    Jun 10, 2013 at 12:32
  • We don't have UAC enabled on the clients or the server, but I'm going try granting DCOM permissions to individual users instead of groups and see if that makes any difference. Currently the problem doesn't make any sense to me.
    – James Lupolt
    Jun 10, 2013 at 12:33
  • OK, now this just unaccountably started working for everyone who should have access based on the permissions I'd granted weeks ago. I have no idea why this only started working in the last few days. So the problem appears to be resolved, but I have no idea why it started or why it stopped when it did. My guess is that it's related to unannounced group policy changes.
    – James Lupolt
    Jun 12, 2013 at 17:09

2 Answers 2

Reset to default
9

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.

Improve this answer
1
  • 1
    Thanks, but I've already set that. Upvoted because it's worth mentioning.
    – James Lupolt
    Jun 4, 2013 at 11:17
0

The problem has something to do with permissions on the underlying server and not SSIS or the MSDB. We had the same problem. Temporarily adding the user's AD account to the local administrators group fixed this for us. Adding their AD account to PowerUsers or Users did not; however, I am certain that we could have found what was missing in the Local Security Policy to make it happen.

Improve this answer
1
  • Adding to local account also worked for me, can you suggest what could be probable reason. As this couldn be best practice
    – Saurabh Sinha
    Jun 17, 2015 at 11:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.