SQL Server – Integration Services – Permissions on Client Computer – Day 1

Background

This is the second in a series of post in which we will discuss troubleshooting techniques where users are able to connect to an Integration Service when attempting to do so over a Remote Desktop Connection.  But, stumble badly when attempting to do so on their local desktops.

 

Series

  1. SQL Server – Integration Services – Connectivity–Issue–”Class Not Registered”
    Link

 

Error Message

Here is the error message

===================================
Cannot connect to LABDB.

===================================

Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)

——————————
Program Location:

at Microsoft.SqlServer.Management.Sdk.Sfc.Enumerator.Process(Object connectionInfo, Request request)
at Microsoft.SqlServer.Management.SqlStudio.Explorer.ObjectExplorerService.ValidateConnection(UIConnectionInfo ci, IServerType server)
at Microsoft.SqlServer.Management.UI.ConnectionDlg.Connector.ConnectionThreadUser()

===================================

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.

——————————
Program Location:

at Microsoft.SqlServer.Dts.Runtime.Application.GetServerInfo(String server, String& serverVersion)
at Microsoft.SqlServer.Dts.SmoEnum.DTSEnum.GetData(EnumResult erParent)
at Microsoft.SqlServer.Management.Sdk.Sfc.Environment.GetData()
at Microsoft.SqlServer.Management.Sdk.Sfc.Environment.GetData(Request req, Object ci)
at Microsoft.SqlServer.Management.Sdk.Sfc.Enumerator.GetData(Object connectionInfo, Request request)
at Microsoft.SqlServer.Management.Sdk.Sfc.Enumerator.Process(Object connectionInfo, Request request)

===================================

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.
——————————
Program Location:

at Microsoft.SqlServer.Dts.Runtime.Wrapper.ApplicationClass.GetServerInfo(String bstrMachineName, String& serverVersion)
at Microsoft.SqlServer.Dts.Runtime.Application.GetServerInfo(String server, String& serverVersion)

TroubleShooting

One area that is very easy to overlook is the permission requirements that are required on the connecting client.

SysInternals

Process Monitor

MustBeRunFromAnAdministratorAccount

 

Unfortunately for now our troubleshooting pathway is blocked.

 

Remediation

Administrator Mode

As always, we took the big hammer approach.

And, that was to start command shell in Administrator mode and launch ssms.exe from there.

Command Shell in Administrator Mode

Here is what command shell looks like in Administrator Mode

commandShellInAdministratorMode

 

In Command Shell Administrative Mode, run ssms.exe

Access the Command Shell and initiate ssms.exe

Please be sure to specify the full path to ssms.exe if you have multiple versions of ssms installed and it is material.

 

Is UAC Issue

The problem appears to be User Access Control ( UAC ) related.

But, not quite sure how to detect whether UAC is enabled.