How to resolve SSIS access denied error in SQL Server Management Studio

By:   |   Comments (16)   |   Related: 1 | 2 | > Integration Services Security


Problem

When I try to connect to a remote instance of SQL Server Integration Service from Management Studio in my workstation, I receive an "Access Denied" error message. My Windows account is a member of the db_ssisadmin role in the msdb database of this server but I still can't access the service. 

Solution

One of the business intelligence developers in my team once came to me looking for an answer to this problem. He had been developing and troubleshooting a group of complex SSIS packages in his local workstation. The application had been migrated to a development server only a few days ago and he was trying to connect to it from SQL Server Management Studio. Unfortunately he was not having much luck.

The error message looked like the following:

Remote Integration Service Connectivity Error

Like so many other DBAs, my first instinct was to resolve the issue rather than investigating its root cause. I just wanted the problem to go away so I started with some elevated permissions, hoping my wild guesswork would help. Initially I tried to resolve the issue by adding his Windows account as a user in the msdb database and then assigning the account under db_ssadmin role. This did not help. Next he was made a db_owner in the msdb database, this did not help either. Going against all security best practices and knowing  I was committing a cardinal sin, I assigned his account to the sysadmin server role. Still no luck.

So SQL was not the issue. Promptly rolling back his elevated permissions, I decided to look at the Windows side of things. Once his Windows AD account was added as a local Administrator in the development box, things looked okay. I did not want him to be an administrator even in a development box, so I started to look at the error message closely and tried to make some sense of it. I was pretty sure there was a better solution than making someone a Windows or SQL admin.

As it turns out, a user needs to have certain permissions enabled against the DCOM component for Integration Service to remotely access it with a tool like Management Studio. In this case the Integration Service was part of a SQL Server 2008 R2 installation and was hosted in a Windows 2008 R2 server.

These are the steps I followed:

DCOM User Group Membership

  • Remotely logged into the server where SQL Server Integration Service was running.
  • Started the Server Manager and brought up the "Distributed COM Users" group properties. To get there, start the Server Manager (usually the first icon in the application bar in Windows 2008). From the left pane, expand the Configuration node, then "Local Users and Groups" and then Groups. The following images show this:

    Windows Distributed COM Users Group

    Windows Distributed COM Users Group Properties

  • I then added the Windows account of the developer to this group.

DCOM Component Permission

  • Next, I started the Windows Component Services Applet. To get there, you can navigate from Start > All Programs > Administrative Tools > Component Services. From the left pane, I expanded the DCOM Config node under Component Services > Computers > My Computer:


    • Windows Component Services: DCOM Config

  • Once the node was expanded, I searched for the Component named MsDtsServer100. Note the number 100 at the end of it: we were looking at a SQL 2008 DTS Server here.


    • MsDtsServer DCOM Component

  • Right clicking the icon and selecting Properties from the pop-up menu brought up a dialog box. First, in the Location tab, I ensured the checkbox "Run application on this computer" was turned on:

    Location Tab of the DCOM Component MsDtsServer 100

  • Next, the Security tab looked like the following:

    Security Tab of the DCOM Component MsDtsServer 100

  • This is the crux of the solution. First I made sure the radio button "Customize" was selected under both the "Launch and Activation Permissions" and  "Access Permissions" sections. Next, I was customizing their memberships. Clicking on the Edit buttons brought up the memberships properties window which looked like the following:

    Launch and Activation Permission for the MsDtsServer DCOM Component

    Access Permission for the MsDtsServer DCOM Component

  • In both the cases,  clicking the Add button allowed me to add the developer's Windows AD account under "Group or user names" section:

    Adding User Account for Required Privileges: Standard Windows Dialog Box

  • For the Launch and Activation Permission, I ensured the Windows account had the checkbox for "Remote Activation" turned on. Similarly the checkbox "Remote Access" was turned on for Access Permission. Both these permissions ensure the user can connect to the DCOM application remotely.  Once the settings were applied, I clicked the OK buttons to get back to the main window of Component Services.

  • To wrap up the whole thing, I then restarted Integration Services from SQL Server Configuration Manager. This was not a problem as the server was used for development purposes only.

    Restarting SQL Server Integration Service

Fine Tuning SSIS Access: Execute Permission Denied

With the configuration done at the server, my colleague was able to connect to the SSIS service from his workstation. There was no error message and his login attempt was successful. However, a different error message started to pop-up as he tried to expand the msdb node where packages had been deployed. The error message looked something like the following: The EXECUTE permission was denied on the object 'sp_ssislistfolders', database 'msdb', schema 'dbo'

Error Message Accessing SSIS

Now this error message is displayed when the Windows account does not have permissions on the msdb database hosting the SSIS package. There are three SSIS related roles in msdb database as shown below:

SSIS Related DB Roles in msdb Database

Since this was a development box and the user was going to manage and troubleshoot the packages, I decided to grant his Windows account db_ssisadmin role. The connectivity issue was finally resolved with that.

Final Words

Some points need to be remembered when granting users permission to remotely connect to Integration Services:

  • It's best to use least privileged access. For example, the Remote Launch permission gives the user ability to remotely stop / start Integration Services. This may not be required if the user is not an administrator. Similarly, the db_ssisadmin role has elevated permissions meant for administrative accounts.
  • Restart of Integration Services in a production system can potentially mean an outage of applications using SSIS.  
  • Most importantly, unless you are running SQL 2005 with SP4 or SQL 2008 with SP2 or later,  DCOM permissions for SSIS service will reset after a Service Pack, Cumulative Update or Hotifix has been applied to the SQL Server instance. Microsoft has confirmed this as a problem for the versions prior to these releases. So if your DBAs have upgraded a SQL Server 2005 from a pre-SP4 Service Pack or a SQL 2008 from SP1, you may wish to recheck any custom DCOM configurations applied to the MsDtsServer component.
Next Steps


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Sadequl Hussain Sadequl Hussain has been working with SQL Server since version 6.5 and his life as a DBA has seen him managing mission critical systems.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Comments For This Article




Monday, December 14, 2020 - 10:06:34 AM - Bob T Back To Top (87911)
What an outstanding article! Exceptional competence, command of subject and responsibility - right down to the SP / path / upgrade advisory in the "Final Words." Not a surprise that you have managed mission-critical apps. I love to see it, thanks.

Monday, September 28, 2020 - 2:22:06 PM - barry Back To Top (86543)
Thank you so much!!! Worked like a charm!

Tuesday, August 8, 2017 - 2:22:24 PM - zymos Back To Top (64331)

 Very informative and helpful blog.

To add onto the DCOM Component Permission section, I located the 'MsDtsServer100' or respectively matching number by expaning either of the available 'Microsoft SQL Server Integration Services 11.0' /12.0/13.0/14.0 etc.  I hope that help. Regards.

 

Tuesday, June 27, 2017 - 6:21:15 PM - G Dog Back To Top (58444)

 Solved my problem w/o requiring full-blown admin. Thanks a ton for posting.

 

Sunday, May 21, 2017 - 1:41:16 AM - Marce Back To Top (55925)

Thank you! It was very helpful! 

 

Wednesday, May 10, 2017 - 4:35:03 PM - Cristhoper Back To Top (55658)

Awesome, thanks

Friday, March 3, 2017 - 1:46:04 PM - mark Back To Top (47081)

 Excellent post - thank you!

 

Monday, November 21, 2016 - 9:10:19 AM - Sandra Back To Top (43814)

 I followed these guidelines (and many more!) without success.  A restart of the server FINALLY enabled access to SSIS.

 

Tuesday, May 24, 2016 - 2:45:30 AM - Matt Back To Top (41547)

Excellent article, definitely fixed my problem after reading a lot of other half-solutions. Many many thanks Mr Hussain! 

 

 

Wednesday, February 24, 2016 - 10:01:23 AM - Lukas Back To Top (40773)

Or you just start management studio as administrator and problem is solved :) At least in my case :)

Thursday, December 3, 2015 - 5:22:46 PM - Dena Back To Top (40193)

Awesome! I've been struggling with this for days and this post is the only one that finished the job.  Everyone else stopped at the DCOM settings.  :)  Thank you!

Tuesday, July 28, 2015 - 6:12:10 PM - Ray Back To Top (38303)

Thanks for following through on this and sharing your findings.

I have struggled with this very issue multiple times and now I think I understand the problem at last.

 

Do you think they could have made the process more difficult :)

Sunday, June 21, 2015 - 5:06:20 AM - Sadequl Hussain Back To Top (37974)

Hi tu,

Without seeing error messages or dumps, I can't really say why you can't connect to SQL Server. General advice would be checking if SQL Service is running, if you are choosing the right instance name, port etc. and if you are using the correct service (Database Engine) when connecting from Managemrnt Studio. And of course checking permissions is another step. I wouild also say you try your tests from another workstation. If it succeeds, then it must be something from lient side. If the problem persists then it needs further investigation. Thanks.

Friday, June 19, 2015 - 12:06:45 PM - tu Back To Top (37960)

Thanks for the instruction.  I did what you instructed and am able to connect to ssis.  However, now I can't connect to sql database.  It does not show any errors, it just run but does not connect.

Do i have to do something with my permission because I add my user name to the DCOM?

 

thanks,

 

Wednesday, July 23, 2014 - 2:58:32 PM - Ben Back To Top (32842)

It turns out that restarting SSIS services is NOT a requirement.  I just went through these steps, with the exception of the SSIS restart, and had a user check on his access, and it already works without the restart.  Regardless, thanks for this article.  I had no knowledge of this part of Windows/SQL.

Friday, November 8, 2013 - 9:42:47 PM - Dieuhaucon Back To Top (27453)

Hello, I install sql server 2008 RTM, and I start sqlserver 2008, sql sever 2008 agen, and ssis 10. But I can't do any thing. Ex: when I create new maintenace plan or new job which show error: "General access denied error". I using sa login. Sa is public and sysadmin. Can you help me.














get free sql tips
agree to terms