Share via

Securing SQL Server Integration Services (SSIS)

  • Article

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:

  1. The SSIS Engine
  2. SSIS Packages
  3. SQL Server

 

Protecting the SSIS Engine

  • Ensure file-based Access Control List (ACL) permissions are restricted on the SSIS executable (MsDtsSrvr.exe)
  • Use a security tool to monitor for changes to the executable.
  • Limit Windows administrator accounts on the SSIS server.

 

Protecting SSIS Packages

  • Ensure file-based Access Control List (ACL) permissions are restricted on dtutil.exe.
  • Store packages in a hardened instance of SQL Server (SQL Server stores them in the msdb system database).
  • I haven’t thought of any reason why someone would have to store packages on a file system, but if you must, then set file-based Access Control List (ACL) permissions on the packages.
  • You can store packages in the “Package Store” which uses the file system with permissions managed by SSIS. I haven’t found any detailed documentation for this, but I expect that storing packages in SQL Server is safer, if SQL Server has been hardened.
  • Set the Package Protection Level (PPL) on each package, regardless of where you store them.
  • Limit SQL Server permissions to manage 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 https://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:

  • Do not save sensitive data
  • Encrypt sensitive data with user key
  • Encrypt sensitive data with password
  • Encrypt all data with user key
  • Encrypt all data with password
  • Rely on server storage and roles for access control

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:

  • Check running packages
  • Store packages in MSDB or on a file system
  • Import packages
  • Execute packages
  • Export packages
  • Upgrade packages
  • Organize packages
  • Delete packages
  • Rename packages
  • Set Reader and Writer roles on packages

Protecting SQL Server against Rogue Packages

  • Harden SQL Server
  • Set BlockedSignatureStates to require valid, trusted digital signatures to run any package

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 https://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 https://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!

Comments

  • Anonymous
    January 01, 2003
    Very nice post! A reason to store packages on the file system could be, for example, that running a debug of a master package calling its child packages is spectacular :) Or, if someone is not really experienced and makes mistakes more often, it's very comfortable for them to copy files only. However utilizing deployment scripts can make life easier, and it's always better to minimize the number of people having permissions to modify the packages :)