RE: SQL 2008 - remote connection to the SSIS 2008 - Access denied
- From: sam01m <sam01m@xxxxxxxxxxxxxxxxx>
- Date: Fri, 2 Oct 2009 12:06:02 -0700
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>$MSSQLSERVER
-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$<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.
"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:
Yes, 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
"sam01m" wrote:
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:
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?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
- References:
- SQL 2008 - remote connection to the SSIS 2008 - Access denied
- From: Mirek Endys
- RE: SQL 2008 - remote connection to the SSIS 2008 - Access denied
- From: sam01m
- RE: SQL 2008 - remote connection to the SSIS 2008 - Access denied
- From: Mirek Endys
- SQL 2008 - remote connection to the SSIS 2008 - Access denied
- Prev by Date: RE: DTS Package loading
- Next by Date: ssis send mail task-package can't find smtp server ip address
- Previous by thread: RE: SQL 2008 - remote connection to the SSIS 2008 - Access denied
- Next by thread: RE: SQL 2008 - remote connection to the SSIS 2008 - Access denied
- Index(es):
Relevant Pages
|
Loading
