• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2748
  • Last Modified:

Cannot connect to SQL Server 2005

I am trying to deploy a SSIS package that I created in SQL Server Business Intelligence Development Studio.  I go through the deployment process fine but when I go into SQL Server Management Studio I can connect Integration Services. The problem is when I try to go to Stored Packages > MSDB I am getting an error saying 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 gone through and allowed remote connections and the protocols for the server and the native client, and restarted the services

Is there anything else that I can try?

Here is a copy of the error

TITLE: Microsoft SQL Server Management Studio
------------------------------

Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476

------------------------------
ADDITIONAL INFORMATION:

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)

------------------------------

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)
0
countyprob
Asked:
countyprob
  • 7
  • 6
1 Solution
 
fiboCommented:
Does not seem to really belong to the 'MySQL' area
0
 
imran_fastCommented:
Do You have default instance of SQL Server in your machine?

Please refer to SQL Server 2005 Books online: http://msdn2.microsoft.com/en-us/library/ms137789.aspx

from above , the default configuration file is for default instance of SQL Server in the machine. If you do not have a default instance in your machine and you want to use a named instance, then you have to modify the configuration file as described.

Please try it out and let us know if still have problems .
0
 
countyprobSystem AnalystAuthor Commented:
Yes I do have a default instance of SQL on my machine.  I tried looking at the book you sugested but I am still don't understand.  
Sorry I am new to SQL
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
imran_fastCommented:
There is a file called 'MsDtsSrvr.ini.xml' in the 'Program Files\Microsoft SQL Server\90\DTS\Binn' folder on each machine with SSIS installed that tells the SSIS service what SQL Server instance to connect to for enumerating packages stored in the MSDB.  By default, it is set as '.' which means the local server name, default instance.

In your case, you probably have either a named instance, or are trying to connect to a non-default instance.  Basically, open that file, and change the ServerName element under the MSDB element to the name of the instance to connect to (in a clustered server case, use the virtual SQL instance name)...so, the file looks something like the following probably now:

<?xml version="1.0" encoding="utf-8"?>
<DtsServiceConfiguration xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <StopExecutingPackagesOnShutdown>true</StopExecutingPackagesOnShutdown>
  <TopLevelFolders>
    <Folder xsi:type="SqlServerFolder">
      <Name>MSDB</Name>
      <ServerName>.</ServerName>   <<<---------here
    </Folder>
    <Folder xsi:type="FileSystemFolder">
      <Name>File System</Name>
      <StorePath>..\Packages</StorePath>
    </Folder>
  </TopLevelFolders>  
</DtsServiceConfiguration>


So, the part that I arrowed is what needs to be modified...just overwrite the '.' with the appropriate instance name or server name (default instance)and see if that works for you.
0
 
countyprobSystem AnalystAuthor Commented:
I overwrote the . with my server name or instance name and I am still getting the error

Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)

------------------------------
ADDITIONAL INFORMATION:

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 completely reinstalled Microsoft SQL server on the machine, is there anything else I can try.

Thank you,
0
 
countyprobSystem AnalystAuthor Commented:
Ok, I went back and looked at the install of package and it looks like it isn't installing because it could not establish a connection to the server.  I tried running it with the sever name as local (I have an instance instilled on my machine) and typing in the server name and instance name, neither seem to be working
Can anyone please help
0
 
imran_fastCommented:
What is the version of your SQL Server?
0
 
imran_fastCommented:
And please check the server name of your sql intallation on the server.
You can connect to your sql server locally and then run the following qurey

select * from master.dbo.sysservers

and check for the srvname for sid = 0

and then edit your 'MsDtsSrvr.ini.xml' with the srvname you found above.
0
 
countyprobSystem AnalystAuthor Commented:
Thank you for your all your help.
I am running SQL Server 2005 Standard ed.
I ran the query and inserted the server name into the MsDtsSrvr.ini.xml file, but I am still getting the error, I also tried the IP with no success.  
Could any of this have to do with the fact that this is all on my machine and not on a server somewhere?
Thank you again
0
 
imran_fastCommented:
>>my machine and not on a server somewhere?

You machine have sql server services or does it has a client component only..
0
 
countyprobSystem AnalystAuthor Commented:
I believe it has the server services.  The server name it gives me is my machine name and the instance that I had created during the install.
0
 
imran_fastCommented:
Did you check the sql server services is it running...?

check for
Sql server agent
Sql Server Integration Services
Sql Server

0
 
countyprobSystem AnalystAuthor Commented:
Everything is running and all the protocols are enabled
0
 
countyprobSystem AnalystAuthor Commented:
Ok this is sad but I restarted the SSIS service after I inserted the server name into the MsDtsSrvr.ini.xml file and I can get into MSDB, but I am still getting the same error when I try to execute the package in Management Studio
0

Featured Post

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

  • 7
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now