Granting the SSIS Access This article provides a step-by-step guideline for the system administrator to give SQL Server Integration Services (SSIS) access to non admin users so they can remotely list and execute the packages. Accessing SSIS will not become an issue for a user who become an administrator of the server (i.e. a member of the Administrators group) because as a super user, the person has unlimited rights to do many things including administrative tasks that the ordinary users cannot do. Although assigning a user as a member the Administrators group is the simplest way to allow the user to access SSIS, but that is something that we do not want to do because Administrator role should be limited only to certain users that really entitled for the right. Ordinary users should not be granted that right primarily for the security wise, so the objective of the system administrator is to give limited SSIS access for the non admin users. NOTE: SSIS distinguishes two kinds of access: local access and remote access. As the names imply, a local access means the user physically sits in front of the server and logs in. Most likely it will not happen to the user because normally the server is physically locked in a special room and isolated from the user access. Conversely a remote access means the user tries to connect the server through a computer network. A user access in this article refers to the remote access. By default the non admin user has no access to SSIS. If the user tries to connect SSIS, a typical error message will appear: Connect to SSIS Service on machine “…” failed: Access is denied To provide a limited access to the user, there are several steps that must be performed by the system administrator either in the Windows operating system as well as in the SQL Server side. Connecting to SSIS on a remote server, from SQL Server Management Studio or another management application, requires a specific set of rights on the server for the users of the application.
-1-
Expert
www.tobuku.com
Step-1: Setting the DCOM Permissions 1. Open Control Panel Administrative Tools Component Services MMC snap-in. 2. Expand the Computers My Computer MsDtsServer100 component is found.
Component Services to start the DCOM Config nodes until the
Right-click on and select Properties. 3. In the Properties dialog box, select the Security tab.
-2-
www.tobuku.com 4. Under Launch and Activation Permissions, select Customize, and then click Edit to open the dialog box. Add the Distributed COM Users local group, and then allow the Local Activation and Remote Activation. The Launch rights grant or deny permission to start and stop the service, while the Activation rights grant or deny permission to connect to the service.
Click OK to close the dialog box. 5. Repeat the steps for Access Permissions to give Local Access and Remote Access.
Click OK and close all dialog boxes. 6. Restart the Integration Services service.
-3-
www.tobuku.com
Step-2: Adding Users to the Distributed COM Users Group To allow non admin users to access the SSIS, add them into the Distributed COM Users local group one by one. This step can be done easily through Control Panel Administrative Tools Computer Management.
Step-3: Creating Windows Authentication Logins in the SQL Server As far as I know, unlike SQL Server that supports SQL Server authentication and Windows authentication, SSIS only supports Windows authentication. Therefore in order to be able to access SSIS, the user must have a Windows login in SQL Server which is created by the system administrator. 1. Open SQL Server Management Studio and go to Security Logins. Ensure you choose the Windows authentication, NOT the SQL Server authentication.
2. Choose the appropriate Windows domain user. 3. Repeat the steps for the other users.
-4-
www.tobuku.com
Step-4: Granting the db_ssisoperator Database Role Use SQL Server Management Studio to apply the following steps: 1. Expand the MSDB database and open the Security node. 2. Create a new user and associate the user with the login created in the step earlier. 3. Grant the db_ssisperator role. 4. Click OK to apply the change.
5. Repeat the steps for the other users/logins.
-5-
www.tobuku.com For your reference, the table below shows the actions allowed by the database roles related to SSIS: No Database Role
Read Action
Write Action
1
Enumerate own packages.
Import packages.
Enumerate all packages.
Delete own packages.
View own packages.
Delete all packages.
View all packages.
Change own package roles.
Execute own packages.
Change all package roles.
db_ssisadmin
Execute all packages. Export own packages. Export all packages. Execute all packages in SQL Server Agent. 2
db_ssisltduser
Enumerate own packages.
Import packages.
Enumerate all packages.
Delete own packages.
View own packages.
Change own package roles.
Execute own packages. Export own packages. 3
db_ssisoperator Enumerate all packages. View all packages. Execute all packages. Export all packages. Execute all packages in SQL Server Agent.
-6-
None
www.tobuku.com
Troubleshooting This section explains possible errors that a user may encounter when connecting the SSIS. No Error Message
Cause
Resolution
1
Access is denied
The DCOM permissions have not been configured or the user is not a member of the Distributed COM Users group
See Step-1 and 2
2
Login failed for user ‘…’
The user has no Windows authentication login in the SQL Server instance.
See Step-3
3
The EXECUTE permission was denied on the object ‘sp_ssis_listfolders’, database ‘msdb’ schema ‘dbo’.
The login has no associated user in the MSDB database or not granted any of the SSIS related roles.
See step-4
Reference MSDN. “Connecting to a Remote Integration Services Server”. http://msdn.microsoft.com/enus/library/aa337083.aspx. MSDN. “Using Integration Services Roles”. http://msdn.microsoft.com/enus/library/ms141053.aspx. SQL Sqlservercentral. SQL Server 2005 Forums. http://www.sqlservercentral.com/Forums/Topic281713-148-1.aspx Wightman, James. “Pro SQL Server 2005 Integration Services”. Apress, 2008.
