I was recently asked about securing SQL Server Integration Services, and I knew next to nothing about it. After digging in for a while, here are my notes, mostly for myself, but shared in case they might help someone else.
There are 3 areas that need to be secured:
Protecting the SSIS Engine
Protecting SSIS Packages
The Package Protection Level allows you to determine the package protection method and scope.
As for the method, you can choose not to save sensitive data, encrypt only the sensitive data, encrypt all data in the packages (not the data that the packages operate on), or let Windows ACLs protect the entire package. Sensitive data is connection string passwords, some tagged nodes, and some SSIS variables (see http://msdn.microsoft.com/en-us/library/ms141747.aspx ). If you encrypt all data in the package, it will protect hide the logic of the operations, and the server and database sources and targets, in addition to the sensitive data.
The PPL scope allows you to encrypt with a user key or password. If you use a user key, only the user who creates or exports the package can open or run the package. When a package has passed the testing phase, the production team can re-encrypt a package with a maximum-strength password.
Package Protection Levels:
If, and only if, you store SSIS packages in SQL Server, then permissions to manage and run the packages are given only to and through these SSIS database-level roles: db_ssisadmin, db_ssisltduser, and db_ssisoperator. These roles are assigned to a package using SSMS, and they're saved to the msdb system database.
Connecting to the SSIS engine provides the ability to:
Protecting SQL Server against Rogue Packages
The first line of defense against rogue packages is the credentials under which a package makes connections to databases. If SQL Server is hardened to a standard such as the DoD’s Database STIG, an SSIS package should be unable to read or harm any data that the connection credentials don’t have the authority for.
Create a BlockedSignatureStates registry key to prevent SSIS from running packages unless they have digital signatures from a trusted authority (see http://msdn.microsoft.com/en-us/library/ms403378.aspx ).
By default, any user who connects to SSIS via SQL Server Management Studio (SSMS) can see a list of packages, all storage locations, and which of their packages are running. This can be prevented by removing execute permissions on the dts enumeration stored procedures (e.g. sp_enum_dtspackages) from the public role (see http://msdn.microsoft.com/en-us/library/cc645944.aspx ). SQL Server system administrators can see all running packages regardless of other permissions.
SQL Server Management Studio uses the SQL Server service to list running packages. Members of the Windows Administrators group can view and stop all currently running packages. Users who are not members of the Administrators group can view and stop only packages that they started.
As always, if anyone has any suggestions, I’d love to hear them!
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.