Security

With all the data moving in and out of your system, security is a big concern. I explain the security within SSIS in this section; before I do, however, you need to be aware of some other security aspects.

When you create your import schema, it is tempting to focus on only the technical challenges and design solutions. But you should resist the urge to jump right in and begin coding. Make sure you follow the process of gathering the requirements, documenting the process, and then implementing the solution.

When you do that, you should include each source system and the data path. You might think that if you are pulling production data from the human resources system and then stripping out the names and address before you move it into your system that you are safe. But if you leave the data on an FTP server or Web service before you bring it over, you need to identify that location as a risk factor and protect it accordingly. In most of the systems I have consulted on, I have found major security breaches on the "staging" areas.

After you have the data within the destination, standard SQL Server security takes over. I have covered what you need to be concerned about in that area earlier in this book. Within SSIS, you can apply three roles to allow your developers access to create and manage packages.

Role	Rights
`db_dtsadmin`	Package administrator
`db_dtsltduser`	Execute only the SSIS packages the user has been given permission to
`db_dtsoperator`	Execute as well as backup and restore packages

You can also increase security by protecting the package when you create it, and even the objects it contains. Access the Properties panel of the object or package to set the following protection levels.

Protection	Short Description
Do not save sensitive	This level does not encrypt anything, but it prevents properties that are marked sensitive from being saved with the package. Other users have to add the blank data themselves.
Encrypt all with password	Encrypts the package using a password, and other users must supply to the password to access or run the package.
Encrypt all with user key	Encrypts the package using a key based on the current user profile, and only that profile can load or run the package.
Encrypt sensitive with password	Unlike the "Do not save sensitive" level, this level does save data marked sensitive, but the data is encrypted by using a password. If the password is provided, the package opens. If not, the package is opened without the sensitive data.
Encrypt sensitive with user key	Encrypts sensitive information using keys based on the current user profile.
Rely on server storage for encryption	If you save the package to the msdb database, the package is secured using SQL Server database roles.