Friday, February 17, 2012

SSIS and MSDB Packages in a Clustered Environment

There seems to be a lot of confusion about SSIS administration in a clustered environment, and I think a lot of it comes down to SQL error messages that occur in a variety of situations.

Take the following, when browsing the MSDB stored package folder when connected to an SSIS instance:
The SQL Server instance specified in SSIS 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 Server 2008 Books Online.
Login timeout expired
A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.
Named Pipes Provider: Could not open a connection to SQL Server [2].  (MsDtsSrvr)
Login timeout expired
A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.Named Pipes Provider: Could not open a connection to SQL Server [2].  (Microsoft SQL Server Native Client 10.0)
This error, in short, says, "I can't access the SQL Server (not SSIS) instance where the MSDB database is stored.  Fix that, and then we'll talk."

Most of the solutions you'll find when searching the web on this error suggest that the problem is that you're using a named instance.  If that's the case, the solution is simple:  change the <servername> line in the MsDtsSrvr.ini.xml file (@ <?>:\Program Files\Microsoft SQL Server\100\DTS\Binn) from the default of
      <ServerName>.</ServerName>
to
      <ServerName>.\instancename</ServerName>
where instancename is the name of your named instance.

When you're running in a clustered environment, however, we've got an additional wrinkle, and this is, I think, where a lot of the confusion comes in.

Since SSIS isn't cluster-aware, you connect to the SSIS instance on the individual cluster node (not the SQL cluster name).  That node, however, isn't where the MSDB database resides.

To fix this, we need to edit the MsDtsSrvr.ini.xml file
(@ <?>:\Program Files\Microsoft SQL Server\100\DTS\Binn)
and change the server name line to reflect the name of the SQL cluster so that SSIS knows where to look for the MSDB database.  Note that the SQL cluster name is not the cluster node (server) name.

So the change would look something like this:
      <ServerName>.</ServerName>
to
      <ServerName>sqlclustername</ServerName>

where sqlclustername is the name of your SQL Server cluster.

If you have a named instance, it would look like this
      <ServerName>sqlclustername\instancename</ServerName>

Note that you'll need to restart the SSIS service (on each node) after you update the configuration file.

9 comments:

  1. Thanks for this info, fixed a move to Sql Cluster I'm working on this weekend

    ReplyDelete
    Replies
    1. I'm glad it was useful, Gene; thanks for the feedback!

      Delete
  2. Thanks for this fix.

    ReplyDelete
  3. you are such a live saver... thank you for such a detailed post
    never had i encountered this and as we all know things go south right before production move/changes!!

    Thanks Lane!!

    ReplyDelete
  4. I'm fighting a similar issue, but now need to execute the package from a SQL Server job. No matter how I set up the config file - using cluster name, specific node name - i get an "access denied" error. I'm using an SSIS Proxy account, and have given it all the permissions I normally would. Any thoughts? Is this a "two hops" problem?

    ReplyDelete
    Replies
    1. Hi, Todd,
      If you're using Windows auth for that account, I'd guess it is a 'two hops' Kerberos problem. Is the access denied error in the logs for "anonymous", or for the actual account? If anonymous, you can be sure it's a Kerberos delegation problem. If it's for the actual account, though, it might be something else.

      If you've already worked through it, I'd love to know what the problem was; if it's still outstanding, I bet we can figure it out.

      Delete
  5. This was perfect! thank you!

    ReplyDelete

Thanks for leaving a comment!