Question 1: What is the difference between SSIS Service and SSIS
Server?
In SQL Server 2005, 2008, and 2008R2, the
pivotal element of SSIS was the SSIS Service, which was used primarily to
allow access to deployed SSIS packages (regardless of whether they were stored
in a database or in the file system) and to host the SSIS execution information
(either on demand or through an automated process, such as the SQL Server
Agent).
Starting with SQL Server 2012, the SSIS Service is no longer needed—neither to manage
SSIS packages nor to execute them. Nonetheless, the service, now referred to as
the SSIS Legacy Service, is still available and can be used to manage, execute,
and monitor the execution of SSIS packages with the familiar toolset from
previous versions of SQL Server.
The functionalities that were once
provided by the SSIS Service have been replaced in SQL Server 2012 by the SSIS Server - an instance of SQL Server hosting
the SSISDB catalog. SQL Server 2012 uses this new, special, database to store
SSIS solutions (projects, packages, and parameters), together with the complete
operational history. The
SSISDB catalog is also used in managing SSIS security (assignment of
permissions on the SSIS objects stored inside the SSISDB catalog).
So in this post, SSIS Server means for the
SSISDB Catalog in SQL Server 2012 and 2014, whereas SSIS Service mean the
legacy SSIS package deployment in SQL Server 2005 – 2008 R2.
Question 2: Why SSIS Service is replaced by SSIS Server?
The SSISDB catalog serves as the dedicated
storage facility for SSIS solutions, providing not only a safe location for
storing SSIS projects, packages, and configurations, but also SSIS solution
maintenance, monitoring, and troubleshooting.
In contrast to the older SSIS package
store, SSISDB is not shared with any other part of the SQL Server platform
(which is the case with the msdb system database) and does not depend on any
external features that are not part of SQL Server (which is the case with the
file system). Therefore, for your future work, you should plan on exclusively
using the SSISDB catalog as the deployment target for all your SSIS solutions.
Question 3: How is SSIS Service Security implemented?
SQL Server 2005 Integration Services
(SSIS) uses three fixed database-level roles - db_dtsadmin, db_dtsltduser,
and db_dtsoperator for
controlling access to packages. These three roles have been renamed to db_ssisadmin, db_ssisltduser,
and db_ssisoperator since SQL Server 2008. Please note
that roles can be implemented only on packages that are saved to the msdb database
in SQL Server. You assign roles to a package using SQL Server Management
Studio. The role assignments are saved to the msdb database.
The following table describes the read and
write actions of Windows and fixed database-level roles in Integration Services.
Role
|
Read action
|
Write action
|
db_dtsadmin (or db_ssisadmin)
or
sysadmin
|
Enumerate own packages.
Enumerate all packages.
View own packages.
View all packages.
Execute own packages.
Execute all packages.
Export own packages.
Export all packages.
Execute all packages in SQL
Server Agent.
|
Import packages.
Delete own packages.
Delete all packages.
Change own package roles.
Change all package roles.
|
db_dtsltduser (or
db_ssisltduser)
|
Enumerate own packages.
Enumerate all packages.
View own packages.
Execute own packages.
Export own packages.
|
Import packages.
Delete own packages.
Change own package roles.
|
db_dtsoperator (or db_ssisoperator)
|
Enumerate all packages.
View all packages.
Execute all packages.
Export all packages.
Execute all packages in SQL
Server Agent.
|
None
|
Windows administrators
|
View execution details of all
running packages.
|
Stop all currently running
packages.
|
In SQL Server 2005, the sysdtspackages90 table
in msdb contains the packages that are saved to SQL Server. In
SQL Server 2008 – 2014, the table is renamed as sysssispackages.
The sysdtspackages90 or sysssispackages table includes columns that contain
information about the roles that are assigned to packages.
· The readerrole column
specifies the role that has read access to the package.
· The writerrole column
specifies the role that has write access to the package.
· The ownersid column
contains the unique security identifier of the user who created the package.
This column defines the owner of the package.
By default, the permissions of the db_ssisadmin (db_dtsadmin) and db_ssisoperator (db_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_ssisadmin (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_ssisadmin, db_ssisltduser,
or db_ssisoperator (or db_dtsadmin, db_dtsltduser,
or db_dtsoperator) role to have read access to the
package. A user must be a member of the db_ssisadmin (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.
If you do not assign user-defined roles to
packages, access to packages is determined by the fixed database-level roles.
If you want to use user-defined roles, you
must add them to the msdb database before you can assign them
to packages. You can create new database roles in SQL Server Management Studio.
Question 4: How to assign reader andwriter roles to a package via
SSMS?
1. Open SQL Server Management Studio.
2. Click Object Explorer on
the View menu.
3. On the Object Explorer toolbar,
click Connect, and then click Integration Services.
4. In the Connect to Server dialog
box, provide a server name. You can use a period (.), (local), or localhost to
indicate the local server, or a named instance such as localhost\MyInstance.
5. Click Connect.
6. Expand the Stored Packages folder, and
then expand the subfolder that contains the package to which you want to assign
roles.
7. Right-click the package to which you want
to assign roles.
8. In the Packages Roles dialog
box, select a reader role in the Reader Role list and a writer
role in the Writer Role list.
9. Click OK.
Question 5: How to Create a User-Defined
Role in SSMS?
1. Open SQL Server Management Studio.
2. Click Object Explorer on
the View menu.
3. On the Object Explorer toolbar,
click Connect, and then click Database Engine.
4. In the Connect to Server dialog
box, provide a server name and select an authentication mode. You can use a
period (.), (local), or localhost to indicate the local server.
5. Click Connect.
6. Expand Databases, System Databases, msdb,
Security, and Roles.
7. In the Roles node, right-click Database
Roles, and click New Database Role.
8. On the General page, provide a name and
optionally, specify an owner and owned schemas and add role members.
9. Optionally, click Permissions and
configure object permissions.
10.Optionally, click Extended
Properties and configure any extended properties.
11.Click OK.
Question 6: How is SSIS Server Security is similar to or different
from the SSIS Service Security?
They are governed by the same general
security model in SQL Server: SSISDB permissions define which SSISDB principals
can perform which SSISDB operations on which SSISDB securables. They share the
same three possible actions on permissions: grant, deny, and revoke as well.
However, there are significant differences between them on securables,
principals, and permission granularity.
Question 7: What are the securables in SSIS Server?
Folders - These represent the topmost securables
in the SSISDB object hierarchy; they contain all other objects.
Projects - These represent child securables of the
folders they belong to; they contain packages and environment references.
Environments - These represent child securables of the
folders they belong to; they contain environment variables.
Operations - represent actions performed against the
SSISDB catalog (deployments and catalog-level configurations) and against
SSISDB projects and packages (validations and executions).
Question 8: What are the principals in SSIS Server?
Different from the three fixed database
roles (i.e., db_ssisadmin, db_ssisltduser,
and db_ssisoperator) in the msdb database in SSIS Service
Security, there is only one special database role in the SSISDB catalog named ssis_admin. Others are the standard
fixed database role in SQL Server, including the Public database role.
This ssis_admin role can perform
administrative tasks on the SSIS server; they can view and modify any SSISDB
securable. The general db_owner database role is a default member of the
ssis_admin role; therefore, the person who creates the SSISDB catalog
automatically becomes a member of the ssis_admin role.
The other SSISDB users are implemented the
same way as in any other SQL Server 2012 database. That is, you create a login,
a user first, and then assign permissions to the user on the SSISDB securables.
A SQL Server user who has been granted access to the SSISDB catalog, also
referred to as an SSISDB user, can query any public catalog view and execute
any public stored procedures, except for the following administrative stored procedures:
catalog.configure_server, catalog.grant_permission, and
catalog.revoke_permission.
Please note the following statement in the book (Training Kit (Exam 70-463): Implementing
a Data Warehouse with Microsoft SQL Server 2012 - p. 483) is not correct. By default, any SSISDB user is permitted to deploy projects
to the SSIS server.
The above statement bothers me. It
triggers me to summarize and compare the SSIS Service Security and SSIS Server
Security for this post. Anyway, I have tested it.
1. I have created a windows user, created a
SQL Server login from the windows account, and created a database from the
login user in the SSISSB.
2. I also assigned the user to the sysadmin
role in the AdventureWorks 2012 and TK463DW databases in order to test the
package. But the user has no permissions on any securables in SSISDB.
3. I re-login to the windows with the new
account. Under this account, the packages execution works fine in the SQL
Server Data Tools for Visual Studio 2012.
4. I connect to SQL Server 2012, and notice
nothing under the SSISDB folder (there were two projects there under the
original admin account).
5. When I try to Create Folder, I get
an error on permission.
6. When I try to deploy the project in the
SQL Server Data Tools for Visual Studio 2012 under this account, the same
problem:
TITLE: SQL Server Integration Services
------------------------------
Operation 'Create' on object 'CatalogFolder[@Name='SSISDB2']'
failed during execution. (Microsoft.SqlServer.Management.Sdk.Sfc)
For help, click:
http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=11.0.5058.0+((SQL11_PCU_Main).140514-1820+)&LinkId=20476
------------------------------
ADDITIONAL INFORMATION:
Don't have rights to execute requested operation.
(Microsoft.SqlServer.Management.Sdk.Sfc)
For help, click:
http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=11.0.5058.0+((SQL11_PCU_Main).140514-1820+)&LinkId=20476
------------------------------
The EXECUTE permission was denied on the object 'create_folder',
database 'SSISDB', schema 'catalog'.
The EXECUTE permission was denied on the object
'set_folder_description', database 'SSISDB', schema 'catalog'. (Microsoft SQL
Server, Error: 229)
So the next statement on page 484 is right:
Only members of the ssis_admin role are
permitted to create folders in the SSISDB catalog. SSISDB users must be granted
the Read and Create Objects permissions on at least one existing SSISDB folder
in order to be able to deploy projects to the SSISDB catalog.
In general, permissions in SSIS Catalog
must be granted explicitly. However, the following implicit permissions are
granted automatically to SSISDB users who create SSISDB objects:
· Any user who deploys a project is
automatically granted permissions to read (enumerate), modify, and execute that
project.
· Any user who creates an environment is
automatically granted permissions to read and modify that environment.
· Any user who creates an operation is
automatically granted permissions to read and modify that operation.
In other words, by default, access to
SSISDB objects is limited to the users who created them and the members of the
ssis_admin database role. Of course, for any user other than the SSISDB
database owner to be able to create SSISDB objects, the administrator must
first allow that user access to the SSISDB catalog.
Additionally, the hierarchical
relationships between SSISDB objects allow permissions to be inherited from the
ancestral object. But, the following objects cannot have permissions assigned
directly, only through inheritance:
· Packages and environment references
inherit permissions from the containing project.
· Environment variables inherit permissions
from the containing environment.
In other words, permissions can be
controlled explicitly on folders, projects, operations, and environments, but
not on packages, environment references, or variables. Permissions on the
latter are inherited from the containing object (the project or the environment
to which the object belongs).
Though inheritance does simplify
permission assignment, it makes determining permissions slightly less
transparent when SSISDB data is accessed. Therefore, the actual permissions are
computed from explicitly assigned and inherited permissions every time an
SSISDB object is accessed.
Question 9: What are the permission in SSIS Server security?
Permission Name
|
Applicable Object Types
|
READ
|
Folder, Project, Environment,
Operation
|
MODIFY
|
Folder, Project, Environment,
Operation
|
EXECUTE
|
Project
|
MANAGE_PERMISSIONS
|
Folder, Project, Environment,
Operation
|
CREATE_OBJECTS
|
Folder
|
READ_OBJECTS
|
Folder
|
MODIFY_OBJECTS
|
Folder
|
EXECUTE_OBJECTS
|
Folder
|
MANAGE_OBJECT_PERMISSIONS
|
Folder
|
Question 10: Anyway to implement SSIS Server Security via Roles,
not via individual users?
Yes. It is possible. See SSIS
Catalog Access Control Tips for
details. Here is the summary:
If you want to allow a login to be
able to do anything on SSISDB, make it as a member of the ssis_admin role.
If you want to
allow a login to be able to read/execute only one project, but not able to
access any other objects (projects or environments) in a folder where the
project is in:
· Make it a member of the SSISDB database
role -- public. (This leverages SQL security mechanism.)
· Grant it Read to the
folder, and grant it Read/Execute to the project. (This uses
SSIS Catalog security mechanism.)
If you like to allow a login (user or group) to be able to
read/execute all projects in a folder:
· Map it to a member of the SSISDB database
role -- public.
· Grant it Read/Execute/Read
Objects to the folder.
