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.

One thought on “SQL Server – Integration Services – Permissions on Client Computer – Day 1

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 )

Google+ photo

You are commenting using your Google+ 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 )

Connecting to %s