March 23, 2016 at 10:11 am
So for some reason M$ thought it a good idea to make configuring remote access to SSIS an incredibly convoluted and contrived process, and I've been bit by it several times now, so after the latest head-to-desk session I wrote down all steps that I took to resolve the issue. Problem is that now another user in my environment wants access to SSIS remotely and despite following my previously-documented steps it's still puking out errors.
So, could we try and come up with a one-size-fits-all list of steps to give a non-privileged user remote access to SSIS 2014?
Here's what I have:
A clustered SQL Server 2014 with SSIS installed.
The user tries to connect and gets the error (also, don't you love it when they send you a screenshot of an error rather than its text?):
"Failed to retrieve data for this request (Microsoft.SqlServer.Management.Sdk.Sfc)
Connecting to the Integration Services service on the computer "foo.blah.com" 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 not have permisisons to access the SQL Server Integration Services service through DCOM."
My steps:
1. On SQL Server:
- Create login for the user from the domain login.
- Mapped the login to the user in MSDB and made the user a member of the following roles: db_ssisadmin, db_ssisoperator.
2. On the cluster nodes:
- Add the domain login of the user to Distributed COM Users group.
- Under Component Services -> Computers -> My Computer -> DCOM Config -> Microsoft SQL Server Integration Services 12.0 -> Properties -> Security tab:
Launch and Activation Permissions -> Customize -> Edit -> Add domain login for the user and grant Local Activation and Remote Activation.
Access Permissions -> Customize -> Edit -> Add the domain login for the user and grant Local Access and Remote Access.
Restart SSIS service.
It SHOULD work, but the user is still getting the same "Access Denied" error as before. I already had the user log off and log back on to the workstation, no effect.
What else am I missing? And why does M$ make it impossible to easily configure this crap?
March 24, 2016 at 11:55 am
This has always worked for me
Grant Permissions to Integration Services Service
Are you sure the user account and the account you setup access for match? (Local vs. AD)
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply