DBA Sensation

April 27, 2009

How to connection sql server named instance SSIS service

Filed under: 3. MS SQL Server — Tags: , , — zhefeng @ 1:52 pm

I’ve occasionally run into the following error when attempting to connect to SSIS from Management Studio:

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

The SQL Server specified in Integration Services service configuration is not present or is not available. This might occur when there is no default instance of SQL Server on the computer. For more information, see the topic "Configuring the Integration Services Service" in SQL Server 2008 Books Online.

Login Timeout Expired

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2008, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.

Named Pipes Provider: Could not open a connection to SQL Server [2]. (MsDtsSvr)."

The problem, as it turns out, is that when I installed SQL Server 2005/2008 on a machine, that a default instance was already installed. In my case SQL Server 2000 was already installed as the default instance. The initial posts I read (and heard about in talking to other colleagues who had the same problem) said that SQL Server 2005/2008 had to be installed as the default instance  if you wanted to be able to use SSIS.

The problem is that unlike the database engine, which can have many running named instances running, SSIS can only run one instance on a server at a time. When SQL Server 2005/2008 is installed there is no option to specify which SQL Server instance should be used to run SSIS. Instead the default configuration is always applied and SSIS tries to start under the default instance. If the default instance is SQL Server 2000, you have a problem. You can only run SSIS on SQL Server 2005 or higher.

Since a server can only run one instance of SSIS you need to make sure that the config file points to the correct instance of SQL Server. Locate the MsDtsSrvr.ini.xml configuration fileand open it with a text or xml editor.

SQL 2005 location:
C:\Program Files\Microsoft SQL Server\90\DTS\Binn\MsDtsSrvr.ini.xml

SQL 2008 location:
C:\Program Files\Microsoft SQL Server\100\DTS\Binn\MsDtsSrvr.ini.xml

The default contents of the file:

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

Notice the ServerName element points to the default instance of SQL Server (when connecting to the local machine “.” is the same as “(local)” – they both use NamedPipes to connect).

Modify the configuration file to point to the named instance as follows.

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

After making the change you MUST restart the SSIS service for the change to take effect.

Another normal issue to connect to SSIS in sqlserver 2005 is “access is denied”. The following workground can resolve this issue(http://msdn.microsoft.com/en-us/library/aa337083.aspx):

  1. If the user is not a member of the local Administrators group, add the user to the Distributed COM Users group. You can do this in the Computer Management MMC snap-in accessed from the Administrative Tools menu.
  2. Open Control Panel, double-click Administrative Tools, and then double-click Component Services to start the Component Services MMC snap-in.
  3. Expand the Component Services node in the left pane of the console. Expand the Computers node, expand My Computer, and then click the DCOM Config node.
  4. Select the DCOM Config node, and then select MsDtsServer in the list of applications that can be configured.
  5. Right-click on MsDtsServer and select Properties.
  6. In the MsDtsServer Properties dialog box, select the Security tab.
  7. Under Launch and Activation Permissions, select Customize, then click Edit to open the Launch Permission dialog box.
  8. In the Launch Permission dialog box, add or delete users, and assign the appropriate permissions to the appropriate users and groups. The available permissions are Local Launch, Remote Launch, Local Activation, and Remote Activation. The Launch rights grant or deny permission to start and stop the service; the Activation rights grant or deny permission to connect to the service.
  9. Click OK to close the dialog box.
  10. Under Access Permissions, repeat steps 7 and 8 to assign the appropriate permissions to the appropriate users and groups.
  11. Close the MMC snap-in.
  12. Restart the Integration Services service.
About these ads

2 Comments »

  1. Next time I read a blog, I hope that it doesn’t fail me as much as this particular one. I mean, I know it was my choice to read through, nonetheless I truly thought you would probably have something helpful to talk about. All I hear is a bunch of whining about something that you could fix if you were not too busy searching for attention.streetdirectory

    Comment by streetdirectory — July 9, 2013 @ 3:19 am

  2. Hi there friends, how is everything, and what you would like to say concerning this piece of
    writing, in my view its in fact awesome for me.

    Comment by cash advances — September 8, 2013 @ 12:43 am


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Silver is the New Black Theme Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: