Thursday, April 14, 2011

Can't Expand MSDB in Integration Services Server

TCP Provider: No connection could be made because the target machine actively refused it. (MsDtsSrvr)
"Named Pipes Provider: Could not open a connection to SQL Server [2].  (Microsoft SQL Native Client)"

Other day one of the team here had to work on a fairly static server which has few SSIS packages installed on it. The Team wasn't able to expand MSDB node to view packages installed on the Intergration Services.
Error they were receiving was:

Steps to check if you are not able to expand MSDB in Integration Services:
1. User has permission on MSDB database as SSIS pacakges are stored in MSDB

2. Check if SQLBrowser services is running. Run > Services.msc

3. Check if TCP-IP/Named pipe are enabled for the Sever instance from SQL Server Configuration Manager

4. Windows firewall is not blocking SQL Sever access. Add SQLServer.exe or port 1433 to exceptions.

5. Find MsDtsSrvr.ini file at drive:\Program Files\Microsoft SQL Server\90\DTS\Binn (for 2008 find in 100\DTS\bin folder) and check for Server tag if it is <ServerName>.ServerName> (this was the case with the team here :-) )

If yes then replace "." with Servername\instance name and restart Integration Sevices.

Have fun!!