Recently, I encountered a client query regarding connectivity issues between SSIS on the database server and an application server. The client was receiving the error message: “Connecting to the Integration Services service on the computer ‘SQL’ failed with the following error: ‘Class not registered’.”
Upon analyzing the error message, it became apparent that the issue was related to a registration problem or missing DLLs. It was not a security or connection blockage issue. To begin troubleshooting, I checked the versions of SSIS and SSMS on the application server and discovered a version mismatch. It is crucial for the SSIS and SSMS versions to match in order to establish a successful connection.
Initially, I thought upgrading the SSMS version on the application server would resolve the issue. However, even after the upgrade, I encountered a different error message: “The RPC server is unavailable. (Exception from HRESULT: 0x800706BA) (Microsoft.SqlServer.DTSRuntimeWrap)”.
In this particular environment, SQL Server 2016 was running as a named instance on a static port. The application login did not have membership in the local admin group on the database server, as per company standards. To further troubleshoot, I followed these steps:
- Verified that the RPC service was running, as indicated by the error message.
- Ensured that security was correctly set up for SSIS in the group admin and DCOM permissions, even though there were no permission-related errors.
- Tested the connection on port 135 from the application server to the database server using the telnet command, as SSIS utilizes this port number.
Despite completing these steps, the application was still unable to connect to SSIS. At this point, I ruled out permissions as the cause of the error and considered other possibilities, such as network issues or a connection blockage. Since firewalls are commonly enabled on database servers in most companies, I suspected that the firewall might be blocking the connection.
To test this hypothesis, I temporarily disabled the firewall during non-production hours and found that the connection started working. However, it was essential to re-enable the firewall and investigate the issue further using the netstat command. I discovered that SSIS was not using port 135, but instead, it was running on a range of higher (dynamic) ports that changed every time the service restarted.
To resolve the issue, I needed to ensure that all the dynamic ports within the range of 49152-65535 were open and that all connections were allowed from MsDtsSrvr.exe. I created a rule in the Windows Firewall on the database server, specifying the inbound rule configuration as follows:
- Rule name: Test_RPC1
- Rule direction: Inbound
- Rule Action: Allow the connection
- Protocol: TCP
- Local IP: (10.10.xx.xx)
- Remote IP: App Server IP address – APP1
- Local Port: (empty)
- Remote Port: 49152-65535
After implementing this rule, the connectivity issue was successfully resolved, and the client was able to connect to SSIS without any further problems.
It is important to note that troubleshooting SSIS connection issues requires a systematic approach, considering factors such as version compatibility, permissions, network configuration, and firewall settings. By following the steps outlined in this article, you can effectively diagnose and resolve similar issues in your SQL Server environment.