Connecting to Integration Services Access is Denied in SQL Server 2016 or 2017


By:   |   Updated: 2019-11-27   |   Comments   |   Related: More > Integration Services Security

Problem

After installing SQL Server 2017 or SQL Server 2016, a user tries to connect to SQL Server Integration Services (SSIS) for the first-time using SQL Server Management Studio (SSMS), most likely the connection attempt will fail.  The received error message indicates: Failed to retrieve data for this request.  Connecting to the Integration Services service on the computer failed with the following error: "Access is denied."  By default only administrators have access to the Integration Services service.  On Windows Vista and later, the process must be running with administrative privileges in order to connect to the Integration Services service.  See the help topic for information on how to configure access to the service.

For users who have worked with older versions of SQL Server, this can be surprising as connecting to SSIS with SSMS usually does not have problems. The reason for this is that in the older versions, all users of the "Users" group had access to SSIS by default.  In the newer versions of SQL Server, however, access is secure by default and it must be granted to a specific user who needs to connect to that service.

Solution

In this article, we will reproduce this problem and describe how to solve it.

Let’s assume that we have SQL Server 2017 installed and never connected to SSIS before on that server. SSIS should be running, which can be checked in SQL Server Configuration Manager as follows:

sql server configuration manager

To connect to SQL Server Integration Services, we open SQL Server Management Studio and in the "Object Explorer" choose "Connect" and then "Integration Services…":

ssms object explorer connect to ssis

Now, we need to log in with our credentials:

sql server credential screen

After clicking "Connect", the following error message will appear: Failed to retrieve data for this request.  Connecting to the Integration Services service on the computer failed with the following error: "Access is denied."  By default only administrators have access to the Integration Services service.  On Windows Vista and later, the process must be running with administrative privileges in order to connect to the Integration Services service.  See the help topic for information on how to configure access to the service.

ssis connection error message

The problem is that starting from SQL Server 2016, Users in the "Users" group do not have access to SSIS by default. Therefore, if we have just installed the services, but haven’t granted the special permissions to the user, it will be impossible to access SSIS by using the credentials of that user. Thus, we need to manually grant these permissions using an Administrator user.

First, we should open a "Command prompt" and type Dcomcnfg.exe:

command prompt

This opens the UI for working with "Component Services" where we can change certain settings in the registry:

windows component services

Then we locate "DCOM Config" under the "Console Root" - Component Services > Computers > My Computer > DCOM Config.  Under the "DCOM Config", "Microsoft SQL Server Integration Services" can be found. Right-click on it and choose "Properties".

windows component services

Then, in the "Properties" window, we select "Security" tab:

windows component services security settings

Here we should set "Launch and Activation Permissions" and "Access Permissions" for our user.

Click "Edit" on "Launch and Activation Permissions".

Then click "Add" we find the user and grant the corresponding permissions shown below:

windows launch and activiation permission

After that, we set the corresponding permission for the user on the "Access Permissions" in the same way as shown below:

windows access permission

Having set the necessary permissions for the user, we should restart SQL Server Management Studio and Integration Services:

sql server configuration manager

When we open SSMS after restarting, try to connect to Integration Services again and the attempt should be successful:

successful connection to ssis using ssms

Hence, the problem is solved, and the user can work with SQL Server Integration Services using SQL Server Management Studio.

Conclusion

In conclusion, as we can see above, in order to be able to connect to SSIS in SQL Server 2016 or 2017, we need to do additional manual work to grant permissions. This is because unlike the older versions of SQL Server, in this version access to SSIS is secure by default. Therefore, if the current user needs access to SSIS, the administrator should manually grant access to that user as shown above.

Next Steps

To find more information about the discussed topic, please follow the links below:



Last Updated: 2019-11-27


get scripts

next tip button



About the author
MSSQLTips author Sergey Gigoyan Sergey Gigoyan is a database professional with more than 10 years of experience, with a focus on database design, development, performance tuning, optimization, high availability, BI and DW design.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.






download

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools