Access to Files Used by Packages
Applies To: SQL Server 2016
The package protection level does not protect files that are stored outside the package. These files include the following:
These files must be protected separately, especially if they include sensitive information.
If you have sensitive information in a configuration, such as login and password information, you should consider saving the configuration to SQL Server, or use an access control list (ACL) to restrict access to the location or folder where you store the files and allow access only to certain accounts. Typically, you would grant access to the accounts that you permit to run packages, and to the accounts that manage and troubleshoot packages, which may include reviewing the contents of configuration, checkpoint, and log files. SQL Server provides the more secure storage because it offers protection at the server and database levels. To save configurations to SQL Server, you use the SQL Server configuration type. To save to the file system, you use the XML configuration type.
For more information, see Package Configurations, Create Package Configurations, and Security Considerations for a SQL Server Installation.
Similarly, if the checkpoint file that the package uses includes sensitive information, you should use an access control list (ACL) to secure the location or folder where you store the file. Checkpoint files save current state information on the progress of the package as well as the current values of variables. For example, the package may include a custom variable that contains a telephone number. For more information, see Restart Packages by Using Checkpoints.
Log entries that are written to the file system should also be secured using an access control list (ACL). Log entries can also be stored in SQL Server tables and protected by SQL Server security. Log entries may include sensitive information, For example, if the package contains an Execute SQL task that constructs an SQL statement that refers to a telephone number, the log entry for the SQL statement includes the telephone number. The SQL statement may also reveal private information about table and column names in databases. For more information, see Integration Services (SSIS) Logging.