SQL Server – Integration Services – Connectivity – Issue – “Class Not Registered”

Background

Using SQL Server Management Studio (v14), trying to connect to a remote SQL Server Integration Services running on SQL Sever v2012.

But, running into problems.

I have been at the same junction since before the Christmas break; and so I am saddened and perplexed by it.

 

Errors

Error – “Class Not registered”

Error Message

Image

ClassNotRegistered-cutomized

 

Textual


Failed to retrieve data for this request.

(Microsoft.SqlServer.Management.Sdk.Sfc)

For help, click: <a href="http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&amp;LinkId=20476">http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&amp;LinkId=20476</a>

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

Connecting to the Integration Services service on the computer "LABDB" failed with the following error: "Class not registered".

This error can occur when you try to connect to a SQL Server 2005

Integration Services service from the current version of the SQL Server

tools. Instead, add folders to the service configuration file to let

the local Integration Services service manage packages on the SQL

Server 2005 instance.

Remediation

Extend Integration Services Configuration – Add new repository

Everybody and their mama, especially on the Q/A sites, says that the problem is due to version mismatch.

And, that to fix it, one should look for the SSIS Service configuration file and add a new XML Tag to it.

The XML tag is meant to have SQL Server expose other SQL Server Instances on same box, Clustered instances, etc.

 

Configuration – File – Original

 


<?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>DBLAB</ServerName>
    <Folder xsi:type="FileSystemFolder">
      <Name>File System</Name>
      <StorePath>..\Packages</StorePath>
    </Folder>

  </TopLevelFolders>
</DtsServiceConfiguration>

 

Configuration – File – Revised

<?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>DBLAB</ServerName>
    </Folder>
	 <Folder xsi:type="SqlServerFolder">
         <Name>DBLAB - Local MSDB ( SQL Server 2012)</Name>
         <ServerName>DBLAB</ServerName>
      </Folder>
    <Folder xsi:type="FileSystemFolder">
      <Name>File System</Name>
      <StorePath>..\Packages</StorePath>
    </Folder>

  </TopLevelFolders>
</DtsServiceConfiguration>

 

made the changes, but still stuck

 

 

Network Monitoring

WireShark
ScreenShot

nwtrace20170104-0337pm-masked

Explanation
  1. Network Addresses
    • Our Desktop is x.x.x.189
    • Our SSIS Server is x.x.x.111
  2. Number 269 thru 277
    • From Desktop Connect to TCP Port 135
      • Port 135 is RPC Endpoint Mapper (EPM)
        • Protocol
          • DCERPC
  3. Number 278 thru 279
    • Request from Source To Component Services
      • Requesting RemoteCreateInstance

Nothing seems to be the matter!

 

Re-configure Integration Services DCom Component to use specific port

I have seen this problem before, but too much In & Out burger is making my brain mushy.

And, so took to the Net and found this wonderful gem:

Authored :- Mohammad Hoque
Title :- Integration Service is running on dynamic port can’t connect remotely
Link

Basically, Mohammad’s contention is that the problem is because RPC uses ephemeral ports and that we can either keep things that way and open up all of  TCP Block 49152 thru 65535.

Or hard code a  specific TCP Port.

 

Remediation Steps:
  1. Using Component Services, Determine the Application ID that corresponds to the version of your Integration Services
  2. In MS Windows Registry, search out HKEY_LOCAL_MACHINE\SOFTWARE\Classes\AppID\<Application ID>
    • Add a new registry key
  3. Restart Integration Services
  4. Retry Client Connection

 

Component Services

Launch Component Services and find SQL Server Version Specific Integration Services

DCOM Config

You want to look under Component Services \ Computers \ My Computer \ DCOM Config

dcomconfig

And, look for Microsoft SQL Server Integration Services <version#>

findintegrationservices

Microsoft SQL Server Integration Services 12

 

component-general-20170104-0531pm

The info we want is Application ID

 

Registry

Original

registry-20170104-0545pm

Revised

Here we add :

  1. Item :- Endpoints
  2. Name :- REG_MULTI_SZ
  3. Data
    • Syntax :- ncacn_ip_tcp,0,[portNumber]
    • Value :- ncacn_ip_tcp,0,50000

Adding Data …

registry-add-endpoints-20170104-1000pm

 

Post Data

registry-20170104-0546pm

 

 

Integration Services

Restart Integration Services.

 

Client Retry

Retry from Client ( SSMS )

 

Network Monitoring

WireShark
ScreenShot

nwtrace20170104-1020pm-masked

Explanation

With an hardcoded TCP Port for our DCOM Component and Firewall Changes for TCP 135 and our assigned DCOM Port, we have a fuller network conversation.

 

Credits

Crediting Mohammad Hoque

Integration Service is running on dynamic port can’t connect remotely
April 26, 2011
Link

Microsoft Connect Items

  1. SSMS 2016 error connecting to Integration Services on a SQL Server 2008 R2 instance – by G Britton
    • ID :- 2779968
    • Opened By :- G Britton
    • Date Published :- 2016-Jun-3rd
    • Link

 

Summary

For most people things will work.

In our case, it did not and we will cover edge cases in later posts.

 

Addendum

  1. SQL Server – Integration Services – Permissions on Client Computer – Day 1
    Published On: 2017-Jan-05
    Link
  2. SQL Server – Integration Services – Connectivity – Issue – “Class Not Registered” / Address by installing ssms /w same version as SSIS Server
    Published On: 2017-Jan-06
    Link
  3.  SQL Server – Integration Services – Network Flow and Rules
    Published On: 2017-Jan-07
    Link

 

One thought on “SQL Server – Integration Services – Connectivity – Issue – “Class Not Registered”

Leave a Reply

Please log in using one of these methods to post your comment:

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