Applying Security to Integration Services


We have now looked at most of the important package administrative tasks including creating, managing, deploying, and executing Integration Services solutions. Additionally, we have reviewed the major Integration Services service administrative tasks. Now you will learn the detailed security options available within Integration Services.

Overview

Integration Services, like all of SQL Server, uses layers of security that rely upon different mechanisms to ensure the integrity of the design of packages as well as the administration and execution of packages. SSIS security is found on both the client and the server, implemented with features such as:

  • Package-protection levels to encrypt or remove sensitive information from the package

  • Package-protection levels with passwords to protect all or just sensitive information

  • Restricting access to packages with roles

  • Locking down file locations where packages may be stored

  • Signing packages with certificates

Within packages, Integration Services defines sensitive data generally as information such as passwords and connection strings. You are not able to define what should and should not be considered sensitive by SSIS unless you wish to do so within a custom-developed task.

Integration Services defines sensitive information as:

  • Connection string password (Sensitive) or Whole connection string (All)

  • Task-generated XML nodes tagged as sensitive by SSIS

  • Variables marked as sensitive by SSIS

Securing Packages

The two primary ways in which you secure packages within Integration Services includes setting package-protection levels and configuring appropriate database SSIS roles. Let's look at these two security implementations.

Package Protection Levels

Many organizations have sensitive information in addition to wanting to control where that information resides within the organization. This implies that your packages, which are responsible for handling that information and must also include information that directs the packages to know the location of that information, must have means to protect against unauthorized access.

These security concerns are addressed in Integration Services through the use of package-protection levels. First, you can ensure that sensitive information that would provide details of where your information resides, like connection strings, can be controlled by using EncryptSensitive package-protection levels. Second, you can control who can open or execute a package by using EncryptAll package passwords.

We have the following package-protection levels available at our disposal within Integration Services:

  • Do not save sensitive

  • Encrypt (all/sensitive) with User Key

  • Encrypt (all/sensitive) with Password

  • Rely on server storage for encryption (SQL storage only)

The package-protection levels are first assigned using Business Intelligence Development Studio. You can update these package-protection levels after deployment or during import or export of the package using Management Studio. Also, you can alter the package-protection levels when packages are copied from BIDS to any other location in which packages are stored. This makes for a nice compromise between development and administration, as developers can configure these levels to suit their rapid development requirements and administrators can follow up and revise these levels to meet production security standards.

Database Integration Services Roles

If you deploy your packages to SQL Server (msdb database), you need to protect these packages within the database. Like traditional databases, this security is handled by using database roles. There are three fixed database-level roles to apply to the msdb database in order to control access to packages. These roles include db_dtsadmin, db_dtsltduser, and db_dtsoperator.

You apply these roles to packages within Management Studio, and these assignments are saved within the msdb database, sysdtspackages90 table within the readerrole, writerrole, and ownersid columns. As the column names imply, you can view the roles that have read access to a particular package by looking at the value of the readerrole column, the roles that have write access to a particular package by looking at the value of the writterrole column, and the role that created the package by looking at the value of the ownersid column.

Here's how we can assign a reader and writer role to packages

  1. Open Management Studio and connect to an Integration Services server.

  2. In Object Explorer, expand the Stored Packages folder and also expand the subfolder to assign roles.

  3. Right-click the subfolder to assign roles.

  4. On the Packages Roles dialog, select a reader role in the Reader Role list and a writer role in the Writer Role list.

You may also create user-defined roles if the default read and write actions for existing roles do not meet your security needs. In order to define these roles, you connect to a SQL Server instance and open the roles node within the msdb database. In the roles node, you right-click the database roles and select New Database Role. Once a new role has been added to the msdb database, we must restart the MSSQLSERVER service before you can use the role.

These database integration services roles help to configure your msdb database sysdtspackages90 table with package security options for reading and writing to specific packages. By applying this level of security, you provide security at the server, database, and table levels. Again, the security discussed within this section only applies when you save your packages within SQL Server (msdb database). Next, we will explore the options for securing packages stored outside of SQL Server.

Saving Packages

When you develop packages, they are stored to the filesystem as XML files with a .dtsx file extension. In the deployment section, you saw how you could move these files to our Integration Services server using import and export utilities, the DTUtil utility, or the package deployment wizard.

Without saving the packages and any related files (such as configuration files) within SQL Server (msdb database), you must apply the appropriate NTFS file permissions to secure these packages.

Next we will consider how you should restrict access to computers running the SQL Server service because they can enumerate stored packages on local and remote locations.

Running Packages

Once packages have been secured by using package protection and either database roles or filesystem security, you must limit access to currently running packages on your servers. Specifically, you need to manage who will have the rights to view the running packages and who can stop your executing packages.

The SQL Server service is used by Management Studio to determine and list currently executing packages. Only members of the Windows Administrators group are able to perform these actions. The service can also be used to enumerate folders and possibly even remote folders. These folders are where you store your packages, and therefore it is important that you control access to computers running this service and also that you apply NTFS folder and file permissions to locations where you store your packages.

Next, we will review the various package resources that also merit security planning.

Package Resources

Having addressed the security needs of packages stored within SQL Server and stored within the filesystem, as well as managing the access to running packages, we next turn our attention to the various resources used by our packages.

The package resources we will need to consider include:

  • Configuration files

  • Checkpoint files

  • Log files

You must consider security for these resources, as they may contain sensitive information. For example, the configuration files often will contain login and password information for data sources used by your packages. Even if they do not contain sensitive information, you need to ensure that your packages can find and use them when they are executed. Another example would be the checkpoint files used for restartability of packages. These checkpoint files save the current state information from the execution of packages, including the current values of any variables defined within the packages. Often, we use the variables to store information that may be deemed sensitive, and, as a result, you should also ensure that only appropriate staff is able to access these files. In any case, you can store these various files in SQL Server and use the database Integration Services roles for security, or you need to apply appropriate NTFS permissions.

Digital Signatures

As a last line of defense for ensuring successful usage of valid packages during execution, you can sign packages with certificates. After signing the packages, when you execute a package using a utility or program, the package signature is checked, and a warning may be issued or the package may be prevented from executing should an invalid signature be determined. In order to ensure this validation takes place, we set the CheckSignatureOnLoad property for packages to true.



Professional SQL Server 2005 Administration
Professional SQL Server 2005 Administration (Wrox Professional Guides)
ISBN: 0470055200
EAN: 2147483647
Year: 2004
Pages: 193

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net