Role-Based Security

To control who has the authority to run a package or to make changes to a package that has been deployed to the database server, SQL Server 2005 employs role-based security. This form of security is managed by the SQL Server database engine, using database roles in the MSDB database. Specific permissions are applied to a package deployed using only the database deployment option.

A package is secured on the server, using database roles defined in the MSDB system database. The default roles are db_dtsadmin, db_dtsltduser, and db_dtsoperator. Two levels of permissions are applied to a package: reader and writer. By default, a package reader is any user in the db_dtsadmin, db_dtsltduser, or db_dtsoperator roles; a reader can view and execute a package. By default, a package writer is any user in the db_dtsadmin role; a writer can perform any user or administrative operations, including exporting, modifying, executing, or changing role assignments for a package.

Applying Security

The following exercise will walk you through the process of applying encryption and password protection to a package and then deploying and protecting the package access by using SQL Server role-based security. You will change the protection level of a package to EncryptAll-WithPassword, prepare a deployment utility, deploy the package to the server, assign reader and writer roles to the package, and then view the default database roles assigned to each.

Change the Protection Level of a Package to EncryptAllWithPassword

1. In BIDS, open the ImportCustomers package from Solution Explorer. Make sure that the package name appears in the drop-down list at the top of the Properties window.

2. Locate ProtectionLevel and change the value by selecting EncryptAllWithPassword.

3. Locate PackagePassword and change the value to a password (all lowercase.)

   

4. Build the project to refresh the deployment manifest.

5. Run the Package Installation Wizard and view the new password option:

   1. Right-click the project in Solution Explorer and choose Build.

   2. Using Windows Explorer, locate the project files in the DeploymentOutputPath specified in the project properties. This folder is located at Bin\Deployment, under the project folder.

   Your screen should look like this:

   

6. Double-click the  SSIS Sample Project.SSISDeploymentManifest file to start the Package Installation Wizard.

7. Click Next on the Welcome page.

8. Select SQL Server Deployment, and then click Next.

9. Specify the target server name: (local).

10. Select Rely On Server Storage For Encryption.

11. Click Next.

    Note the new page displayed entitled Encrypted Packages. Enter the password in the Password column and complete the wizard, using the Next and Finish buttons when prompted. View the encrypted package definition.

12. In BIDS, right-click the  ImportCustomers.dtsx file in the SSIS Packages folder.

13. Choose View Code.

    Note that the package definition is no longer readable Extensible Markup Language (XML). After the XML header, the rest of the content is displayed on one line.

14. Scroll to the right to reveal the encrypted file content.

15. Close this window when completed.

Assign Reader and Writer Roles to a Package

1. In SSMS, verify that you are connected to Integration Services. If not, click the Connect button in the Object Explorer pane, select Integration Services, accept the default local server connection, and then click Connect.

2. Under the Stored Packages folder, expand the MSDB folder to show the ImportCustomers package.

3. Right-click the ImportCustomers package, and then click Package Roles.

4. Note the settings for the Reader Role and Writer Role.

   

   Tip 

   The default setting references multiple preset database roles for the MSDB system database. In production, you can assign an existing or newly created database role and then manage user or group membership in this role.