Digital Signing
Digital signing is a new feature in SSIS that lets you sign a package with a code-signing certificate. With this certificate, you can detect whether a package has changed in some way. Whether the change was malicious or inadvertent, digital signing lets you ensure that the package you run today is the same package you deployed yesterday.
However, systems don't remain static over time. Hard drives go out, machines become deprecated, and database names and locations change. You might think that updating a package to reflect those changes means you'll need to sign the package again. But if the package is designed to effectively use package configurations, you won't need to re-sign the package. Because you create configurations after load time, changing configurations to reflect changes in your system and keep packages running correctly won't corrupt the package signing. Thus, digital signing ensures that the package remains untouched, and configurations isolate the package from the constantly changing environment.
Putting It All Together
Now that I've explained SSIS's new security features, let's look at how to use them in different business situations. The following are some typical scenarios a DBA might want to enable.
Tier-three operations. Many large organizations (e.g., MSN) encompass multiple operation tiers. Tier-one operators are typically less-technical employees who use step-by-step instructions for every operation. If a problem arises, they pick up the phone and call another tier for help. Tier-two or tier-three employees know systems more intimately and can usually troubleshoot common problems. Tier-three employees are typically quite technical; they might have created the systems, can diagnose difficult problems, and might be on call for troubleshooting serious problems.
Imagine that a tier-one operator, who shouldn't have write access to packages, needs the ability to change the SQL Server Agent schedules and sometimes the jobs that run the packages. The operator also needs to be able to run all the packages. You can give the operator the necessary access by making sure that
- all packages are stored in SQL Server
- the package reader role is db_dtsoperator
- the package writer role is NULL, which is the default access for db_dtsadmin
- the operator is in the SQLAgentUserRole and the db_dtsoperator role
- the operator is using any OS login credentials, including those in only the user role
Creating packages whose contents are invisible to sysadmins. A common business problem arises when users who are in different companies or are in a different company than the DBA want to share information. Imagine that some employees are creating packages that contain sensitive data and intellectual property that they don't want to be visible to any other user or administrator. For example, the sysadmin might be a contractor or vendor in your company. Or you might be saving packages to a SQL Server on your ISP's systems. If your customers want to protect their passwords so that nobody outside their group can view their packages, be sure that
- all packages are stored in SQL Server or file system
- the package reader role is db_dtsltduser so that no other users can open the packages
- users save packages by using one of the password-encryption levels; users who need the password share it, but administrators won't have package access
Packages running on only one machine. Say you're concerned that sensitive packages might be taken from a given location and opened and plundered offsite. In this scenario, you want to ensure that the package can never be viewed or executed except on a given machine under a certain account. To set these restrictions, be sure that you save the sensitive packages to SQL Server or file system. When importing the package into SQL Server, use the Encrypt sensitive data with user key option. You must be logged in or running Management Studio with the Run as capability under the same account that the package will be opened or executed under in SQL Server Agent, Development Studio, DTExec.exe, or another such package client. Also be sure that you remove all roles from the reader and writer roles except the username for the account under which the package will execute or be opened. Although it's not technically necessary because the package is encrypted with the user key and won't be readable even if others attempt to view it, replacing the roles with the name of the account hides the packages from other users.
Simplify Your Packages
The new security features in Integration Services simplify all the ways you work with packages. Better integration with SQL Server Agent yields finer control and granularity for configuring Agent package-execution schedules. And more options for encrypting the package make it possible to custom fit your package security to the requirements of your operation.
End of Article
Prev. page
1
[2]
next page -->
