Sometimes, it is fun to install new BizTalk Server environments, especially if you are not in the process/responsible for installing its prerequirements. This week, I’m having some fun experiences. One of them was while I was trying to connect to SQL Server Integration Services, I got the following error:
TITLE: Connect to Server
——————————
Cannot connect to SERVERNAME.
——————————
ADDITIONAL INFORMATION:
Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)
For help, click: https://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&LinkId=20476
——————————
Connecting to the Integration Services service on the computer “SERVERNAME” 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.
——————————
Connecting to the Integration Services service on the computer “SERVERNAME” 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.
Cause
The “Access is denied” error when connecting to SQL Server Integration Services (SSIS) in SQL Server Management Studio (SSMS) typically results from insufficient permissions or configuration issues with DCOM. These are some of the common causes for this to happen:
- When connecting to SSIS on a remote server, the account used needs administrative privileges on that server.
- SQL Server Integration Services uses DCOM for remote connections, and permissions may need to be configured explicitly.
- If there’s a firewall between your machine and the SSIS server, it might block the required DCOM ports.
- In some environments, group policies or security software may restrict DCOM access.
Solution
Depending on the root cause of your problem, you may have different solutions like:
- Add your user account to the Administrators group on the SSIS server or create a proxy account with the necessary administrative permissions.
- Ensure the firewall allows DCOM traffic, or consider opening TCP port 135 for DCOM communication.
- You may also need to work with your IT administrator to check any group policies or security settings that might restrict DCOM access to the SSIS service.
Sometimes – in many cases – running SSMS with elevated privileges can help bypass certain access restrictions, and this was, in fact, the solution I used to solve my problem:
- Right-click the SSMS icon
- And select Run as Administrator.
Then, attempt to connect to SSIS.
I hope you find this helpful! If you liked the content or found it useful and want to help me write more, you can buy (or help me buy) my son a Star Wars Lego!