By: Sergey Gigoyan | Comments (11) | Related: > 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:
To connect to SQL Server Integration Services, we open SQL Server Management Studio and in the "Object Explorer" choose "Connect" and then "Integration Services…":
Now, we need to log in with our credentials:
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.
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:
This opens the UI for working with "Component Services" where we can change certain settings in the registry:
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".
Then, in the "Properties" window, we select "Security" tab:
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:
After that, we set the corresponding permission for the user on the "Access Permissions" in the same way as shown below:
Having set the necessary permissions for the user, we should restart SQL Server Management Studio and Integration Services:
When we open SSMS after restarting, try to connect to Integration Services again and the attempt should be successful:
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:
- https://docs.microsoft.com/en-us/sql/integration-services/service/integration-services-service-ssis-service?view=sql-server-2017
- https://docs.microsoft.com/en-us/sql/relational-databases/sql-server-configuration-manager?view=sql-server-ver15
About the author
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.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
