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 Syste
-----------------------------------------------------------------------------------
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.MSSQLSERVER -> 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
Post by sam01mOK, 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>$MSSQLSERVER
-SQLServer200xSQLAgentUser$<ServerName>$MSSQLSERVER
-SQLServer200xSQLBrowserUser$<ServerName>
-SQLServer200xDTSUser$<ServerName>
-SQLServer200xMSSQLServerADHelperUser$<ServerName>
-SQLServer200xReportingServicesWebServiceUser$<ServerName>$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.
Post by Mirek Endys1.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.
Post by sam01mPost by Mirek EndysYes, Im local administrator of the server.
Yes, I checked DCOM MsDts component and my user rights for the Remote
Launching and Accessing and there are local admins as Allowed to Remote Access and Remote Launch
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
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."
Post by Mirek EndysHello,
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
Failed to retrieve data for this request.
(Microsoft.SqlServer.Management.Sdk.Sfc)
------------------------------
http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&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