Wednesday, October 14, 2015

Question: How to implement the security in SSIS package?

SSIS security features fall into five functional categories: encryption, for securing packages or parts of packages; sensitive-data protection, for identifying and protecting passwords and other sensitive data; SQL Server roles, for controlling access to packages stored in SQL Server; digital code signing, for ensuring that a package hasn't changed; and integration of SQL Server Agent subsystems, for securely storing and executing packages.


SSIS lets you use either a password or the user key to encrypt an entire package or just parts of the package. Like your login password, an encryption password can be any password you create. In contrast, SSIS automatically generates user keys for all the users on a machine and makes the keys part of their profiles. The user key is an opaque number that that few users know about and fewer actually see.

Sensitive Data Protection

SSIS lets you designate which of a component's properties are sensitive so that you can differentiate between normal package data and sensitive data. Generally when a property is marked sensitive, that property is a password. But the sensitive attribute can protect any information that the custom component writer deems too sensitive to be stored in clear text. To mark a property as sensitive, the component writes a Sensitive= True XML attribute to the property element when saving the component. SSIS detects that attribute on properties and can give special treatment to a component's sensitive-property values depending on the package-protection setting for the package.


SSIS adds three new roles that affect the way you run packages in SQL Server Agent and access packages stored in SQL Server. The db_dtsoperator role is the most limited new role. Users in this role can only enumerate (i.e., determine which packages are available) and view existing packages; operators can't create or modify SSIS packages. The db_dtsltduser role lets users create and modify their own packages and enumerate existing packages. And the db_dtsadmin role lets users create, modify, enumerate, and view all packages. Systems administrators are automatically in the db_dtsadmin role.
You must be in one of these three roles to access SSIS packages. If you aren't, and you attempt to enumerate a package by opening the Packages node for the SSIS Server object explorer in SQL Server Management Studio, you'll get the error message
SQL Server Agent Integration

SQL Server 2000 has one SQL Server Agent proxy account. Once enabled, any SQL Server Agent user can set up and execute job steps for the CmdExec subsystem. In addition, DTS doesn't provide good support for unattended execution scenarios. To execute DTS packages with passwords, you have to encrypt the entire command line and paste it into the parameters for CmdExec to pass to DTSRun.exe. To eliminate such problematic practices, Microsoft introduced multiple SQL Server Agent subsystems, which are dedicated environments for executing particular technologies such as ActiveX Scripts, Analysis commands or queries, and SSIS packages. By default, only the sysadmin has rights to create jobs. To grant other users the rights to create jobs, the sysadmin creates proxy accounts, which are essentially wrapped credentials that have SQL Server Agent subsystem associations. Proxy accounts let sysadmins grant job-creation permissions to other users while finely controlling the kinds of jobs those users can create.

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.

No comments:

Post a Comment