Hello,

I have SQL Server 2008 64bit version on the server.
Integration, Reporting, Analysis services are installed there too.

I want to connect over MS SQL Server Management Studio to the Integration
Services on the server, but I cannot. It says:

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

------------------------------
For help, click:
http://go.microsoft.com/fwlink?ProdN...r&LinkId=20476

Access is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED))
(Microsoft.SqlServer.ManagedDTS)

!!!!!!!!!!!!!!!!!!!!!!!!!!

Yes, Im local administrator of the server.
Yes, I checked DCOM MsDts component and my user rights for the Remote
Launching
Yes, I can connect to the Database Engine and manage all objects of that.
Yes, Im sysadmin of the SQL Server
Yes, Im Local Admin of my station

I dont know, what to do next.

Can you help me please?

Thanks

This is what worked for us...

From the webpage http://msdn2.microsoft.com/en-us/library/aa337083.aspx,
under "To configure rights for remote users on Windows Server 2003"...

Replace step 9 with "Click OK to close the dialog box."

Add a step 9.1 with the following text: "On the same Security tab, under
Access Permissions, select Customize, then click Edit to open the Access
Permission dialog box."

Add a step 9.2 with the following text: "In the Access Permission dialog
box, add or delete users, and assign the appropriate permissions to the
appropriate users and groups. The available permissions are Local Access, and
Remote Access. The easiest is to add the local DCOM Distributed Users group. "

Add a step 9.3 with the following text: "Click OK to close the dialog box.
Close the MMC snap-in."

Step 10 stays as-is: "Restart the Integration Services service."


"Mirek Endys" wrote:

The article starts with the sentence:
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.

I said that:


"sam01m" wrote:

OK, I'm going to run through a whole host of possibilities/steps that we had
to go through in order to make our system remotely connectable.

Actually, I wrote an internal administrative guide on the subject, so I'll
be copying verbiage from there...

GROUP MEMBERSHIPS
Verify, from Services.msc, what account the SQL service(s) runs/run under.
SQL Server configures the appropriate rights during the installation process,
but if the LogIn account is ever changed for a given service, it is important
to update the appropriate Windows Group memberships, accordingly. Typically,
all SQL services run under the same account. For that reason, this section
does not attempt to separate which Windows Group is associated with a given
SQL service, but rather which Windows Groups the service account needs to be
a member of. From Compmgmt.msc, expand "Local Users and Groups" and "Groups".
Notice the list of Windows Groups that begin with SQLServer200x. From this
list, you must add the account that the SQL services run under to the
following Groups.
-SQLServer200xMSOLAPUser$<ServerName>$MSSQLSERVER
-SQLServer200xMSSQLUser$<ServerName>$MSSQLSERVER
-SQLServer200xReportServerUser$<ServerName>$MSSQLSE RVER
-SQLServer200xSQLAgentUser$<ServerName>$MSSQLSERVER
-SQLServer200xSQLBrowserUser$<ServerName>

The following Windows Groups should contain NT AUTHORITY\NETWORK SERVICE:
-SQLServer200xDTSUser$<ServerName>
-SQLServer200xMSSQLServerADHelperUser$<ServerName>

The following Windows Groups should contain ASPNET:

-SQLServer200xReportingServicesWebServiceUser$<Serv erName>$MSSQLSERVER

REMOTE SSIS CONNECTIONS
When a user attempts to log into the SSIS service via the Microsoft SQL
Server Management Studio from another computer they would receive an “Access
Denied” error unless the DBA has performed the following list of actions on
the SQL server instance the user is attempting to log into.
-Open Run and type Dcomcnfg.exe to open the Component Services MMC
snap-in, then expand Component Services >> Computers >> My Computer >> DCOM
Config
-Right-Click MsDtsServer from the list and select Properties and
click the Security tab.
-Under the Launch and Activation Permissions select Customize and
click Edit, then add users/groups and assign the appropriate permissions.
-Under Access Permissions select Customize and click Edit, then
add users/groups and assign the appropriate permissions.
-Finally, click OK, close the MMC snap-in and restart Integration
Services for the changes to take affect. The newly added users should now be
able to log into the SSIS Server, remotely.

INTEGRATION SERVICES ROLES
By default, the permissions of the db_dtsadmin, and dtsoperator fixed
database-level roles and the unique security identifier of the user who
created the package apply to the reader role for packages, and the
permissions of the db_dtsadmin role and the unique security identifier of the
user who created the package apply to the writer role. A user must be a
member of the db_dtsadmin, db_dtsltduser, or db_dtsoperator role to have read
access to the package. A user must be a member of the db_dtsadmin role to
have write access.

The fixed database-level roles work in conjunction with user-defined roles.
The user-defined roles are the roles that you create in SQL Server Management
Studio and then use to assign permissions to packages. To access a package, a
user must be a member of the user-defined role and the pertinent Integration
Services fixed database-level role. For example, if users are members of the
AuditUsers user-defined role that is assigned to a package, they must also be
members of db_dtsadmin, db_dtsltduser, or db_dtsoperator role to have read
access to the package.

I know it's probably overkill, but there might be something to this,
especially Roles and Groups.

"Mirek Endys" wrote:

Hi Mike,
This is a common encountered issue, please refer to this article to resolve
this issue:
Connecting to a Remote Integration Services Server
http://msdn.microsoft.com/en-us/library/aa337083.aspx

Thanks.

Best regards,
Charles Wang

Thanks Sam01m, Im going to go throught step by step and will reffer what
happends.

For info:
I have SQL2008 64bit running on the Windows Server 2003 64bit. Computer I
want to connect from running on Windows 7 Ultimate RTM 32bit.

Each server service has own account. Here is the description of my SQL
services setting (SERVICENAME / STARTUP / ACCOUNTNAME):
SQL Active Directory Helper Service / Manual / NETWORK SERVICE
SQL Full-text filter Daemon Launcher / Disabled / LOCAL SERVICE
SQL Server / Automatic / DOMAIN\slqde
SQL Server Agent / Automatic / DOMAIN\slqsa
SQL Server Analysis Services / Automatic / DOMAIN\slqas
SQL Server Browser / Disabled / LOCAL SERVICE
SQL Server Integration Services 10.0 / Automatic / DOMAIN\sqlis
SQL Server Reporting Services / Automatic / DOMAIN\sqlrs
SQL Server VSS Writer / Automatic / Local System
-----------------------------------------------------------------------------------
Well, now the group membership of the DOMAIN\sqlxx accounts
All of these accounts are members of the local Administrators group on the
server where the SQL server is running.
My DOMAIN\myuseraccnt account is member of the local Administrators group
there too.

The local groups SQLServerSERVICE$SERVERNAME contains their users

SQLServerDTSUser$<ServerName> -> DOMAIN\sqlis
SQLServerMSSQLUser$<ServerName>$MSSQLSERVER -> DOMAIN\sqlde
SQLServerReportServerUser$<ServerName>$MSR10.MSSQL SERVER -> DOMAIN\sqlrs
SQLServer200xSQLAgentUser$<ServerName>$MSSQLSERVER -> DOMAIN\sqlsa

etc.

Moreover, my DOMAIN\myuseraccnt account is member of the each local
SQLServer group.

Groups SQLServerDTSUser$<ServerName> and
SQLServerMSSQLServerADHelperUser$<ServerName>
contains NT AUTHORITY\NETWORK SERVICE

------------------------------------------------------------------------
Now, the Component configuration.
In both components - MsDtsServer and MsDtsServer100 are same settings.

Launch and Activation Permissions
----------------------------------------
LOCAL\Administrators -> Allow - Local Launch, Remote Launch, Local
Activation, Remote Activation
Moreover i put there DOMAIN\myuseraccnt and set the same permissions as the
LOCAL\Administrators, even if Im member of the LOCAL\Administrators


Access permissions
----------------------------------------
LOCAL\Administrators -> Allow - Local Access, Remote Access
Moreover i put there DOMAIN\myuseraccnt and set the same permissions as the
LOCAL\Administrators, even if Im member of the LOCAL\Administrators
----------------------------------------------------------

The security log of the server does not contain any record about auditing
failure, or access denied violation.


I dont know, what else check and set
Thanks for patiente.
Mirek

"sam01m" wrote:

Thats why im so unhappy.
I check step by step all settings but nothing was helpfull.


TITLE: Connect to Server
------------------------------

Cannot connect to SERVERDB03.

------------------------------
ADDITIONAL INFORMATION:
Failed to retrieve data for this request.
(Microsoft.SqlServer.Management.Sdk.Sfc)
For help, click:
http://go.microsoft.com/fwlink?ProdN...r&LinkId=20476
------------------------------
Access is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED))
(Microsoft.SqlServer.ManagedDTS)
------------------------------
BUTTONS:
OK
------------------------------



""Charles Wang [MSFT]"" wrote:

Is your client computer's system Vista/Windows Server 2008/Windows 7? If
so, please try starting SQL Server 2008 Management Studio with "run as
administrator". This is necessary for SQL Server 2008 on Vista:
http://msdn.microsoft.com/en-us/library/bb326612.aspx

Thanks.

Best regards,
Charles Wang

Windows 7: tried immediately. Situation is the same.


""Charles Wang [MSFT]"" wrote:

To help isolate if this is a client side issue or server side issue, please
check if this issue also happen on your other computer.

Best regards,
Charles Wang