Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
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 '13 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 '13 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 Jun 10 '13 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 Jun 10 '13 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 Jun 12 '13 at 17:09

1 Answer 1

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.

share|improve this answer
    
Thanks, but I've already set that. Upvoted because it's worth mentioning. –  James Jun 4 '13 at 11:17

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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