Unable to open MSDB node SQL Server 2005 Management Studio HELP!!!

[bmann]

Unable to open MSDB node in SQL Server 2005 Management Studio. I get the follwoing error.

Login timeout expired
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.
VIA Provider: The specified module could not be found. (Microsoft SQL Native Client)


i have tried everything from modifying the ini.xml file. I allowed remote connection in sql server 2005 surface area configuration.

What else can I try?

 

[markros]

Are you working with SSMS directly? What kind of authentication you're using to connect? Can you expand other databases in the Object Explorer?

PluralSight Learning Library

 

[bmann]

I'm sure there is a simple answer to this but it eludes me at the moment.
I’m using the Enterprise Edition of SQL Server 2005.

The MSDB node under Integration Services->Stored Packages refuses to open.
It always fails with a login timeout message and that the connection was
actively refused.

I’ve run the SQL Server Surface Area Configuration tool and allowed remote
and local access, even though it’s all installed on the local machine. This
didn’t help.

The next thing I did was to turn off all my firewalls and internet access
control software and it’s still not working.

I’m using integrated authentication and the user is part of the
administrators group,
any ideas how I can try and rectify this?

 

[mrdenny]

Is there a default instance on the server? If not you'll need to edit the XML file which is the config file for the SSIS service and point it to the named instance.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2005 BI / SQL 2008 DBA / SQL 2008 DBD / SQL 2008 BI / MWSS 3.0: Configuration / MOSS 2007: Configuration)
MCITP (SQL 2005 DBA / SQL 2008 DBA / SQL 2005 DBD / SQL 2008 DBD / SQL 2005 BI / SQL 2008 BI)
MCM (SQL 2008)
MVP

My Blog

 

[bmann]

I am still unable to access MSDB under Integration Services. I’m using the Enterprise Edition of SQL Server 2005 on Windows 2003 server. I have made many changes but still does not work. If you have any suggestions I would be highly appreciative. Below are all the changes I made:

1. The MSDB node under Integration Services->Stored Packages refuses to open.
It always fails with a login timeout message and that the connection was
actively refused.

2. I’ve run the SQL Server Surface Area Configuration tool and allowed remote
and local access, even though it’s all installed on the local machine. This
didn’t help.

3. The next thing I did was to turn off all my firewalls and internet access
control software and it’s still not working.

4. Updated the MsDtsSrvr.ini.xml file. I changed the line <ServerName>.</ServerName>


to something like <ServerName>machineName\instanceName</ServerName>
I also changed to this server name <ServerName>PC1\SQL2K5LOCAL</ServerName> PC1\SQL2K5LOCAL


5. I went to Services, and stop "SQL Server Integration Services", and restart the service.

6. Login timeout expired
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.
VIA Provider: The specified module could not be found. (Microsoft SQL Native Client)



does not seem to work. If any further questions let me know.

 

[mrdenny]

Did you set a client alias to try and have the connections to the named instance using the VIA protocol?

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2005 BI / SQL 2008 DBA / SQL 2008 DBD / SQL 2008 BI / MWSS 3.0: Configuration / MOSS 2007: Configuration)
MCITP (SQL 2005 DBA / SQL 2008 DBA / SQL 2005 DBD / SQL 2008 DBD / SQL 2005 BI / SQL 2008 BI)
MCM (SQL 2008)
MVP

My Blog

 

[SQLBill]

Look at your jobs on the server...are there a bunch running and (I think the term is) awaiting resource? If so, your issue is the Max Worker Thread setting. Look in msdb.dbo.sussubsystems to see what the value is set for. This does not show up in sp_configure. Max Worker Threads limits how many jobs can be running at one time.

I'm not guaranteeing this is the cause of your issue, but if you've tried everything else try increasing the max worker threads.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875