Managing SSIS Service and Configuring Package Security

MANAGING SSIS SERVICE:
The SSIS Service is the Windows Service named 'SQL Server Integration Services'. You can configure the startup requirements and the service account by using SQL Server Config Manager.
After you connect to the SSIS Service,the object explorer window in SSMS lets you manage,secure and execute packages deployed to the server.
The Running Packages Folder:
You can connect to the SSIS services in SSMS and refresh the running package folder to see what packages are currently running. You can stop or terminate package execution by right clicking the package and it terminates after the next task completes.
The Stored Packages Folder:
It has 2 subfolders: File system and MSDB. The package store is located by default in %Program Files%\Microsoft SQL Server\100\DTS\Packages\Folder.
The services uses a config file at the location:
C:\Program Files\Microsoft SQL Server\100\DTS\Bin\MsDtsSrvr.ini.xml
You can edit this xml file and change the element value to a full address.

MSDB folders displays packages that have been deployed to SQL Server. These packages are stored in MSDB database in a table named [dbo].[sysssispackages]. You can create virtual folders in MSDB folder and import packages directly to those subfolders.

Adding SSIS Package Security:
Securing SSIS is important for 2 main reasons:
1.)Connection Information:
Although packages do not contain any data from sources or destinations there is connection information in those packages.
2.)Schema information:
It is not possible to access the sources or destination directly, but with an unencrypted package it is possible to get the look of how the sources and destination looks like.
There are 3 ways to limit the access to packages:
1.)You can apply SQL Server Security roles and can define which SQL logins or windows group or users have read and write access to a package.
2.)You can employ security encryption by using the package protection level in which either a part(sensitive data) or all of the package is encrypted.
3.)You can define file level security in which you can apply access rights to the file or folder.

Assigning Roles and Securing Packages stored in msdb:
Roles are of following types:
1.)Read actions focus on viewing and executing packages.
2.)Write actions apply to moving packages in and out of msdb without executing the packages.
How to use Security Roles:
1.)Expand stored package folder and then the MSDB folder.
2.)Right click the package name for which you need to apply the security roles and then select package roles dialog box.
Types of Roles:
1.)db_ssisadmin- read(view, execute and export)and write access(delete, change and import)
2.)db_ssisltduser-read and write own packages
3.)db_ssisoperator- read only access
4.)windows_Admin- can view exec details of currently running packages and stop currently running packages.
Package Protection Level:
The ProtectionLevel Property Encrypts package definition information that resides in the underlying XML file.
Package password property when protection level requires encrypting metadata in the package with a password.
By default the package is set to use EncryptSenstivityWithUserKey. This means if you create a package with default settings and the package contains sensitive data(such as connection with a password) the connection with be encrypted and viewed only by you, the package author if the package is opened in BIDS.
Options for Package Level Security:
1.)DontSaveSensitive: Sensitive information is not saved and every time a package is opened the information has to be reentered.
This is helpful if only Windows authentication is used for connections exclusively.
2.)EncryptAllWithPassword: The entire package is encrypted using a password used in the PackagePassword property. The package will not be executed without the correct password in BIDS.
3.)EncryptSensitiveWithPassword: The sensitive data is encrypted based on the password entered in the packagePassword property. if a password is not provided the package will be opened but the sensitive data will be replaced with blanks.If a user attemps to execute the package without the password the package fails.
4.)EncryptSensitiveWithUserKey: The sensitive data is encrypted based on the current user and the computer. if the package is opened by a different user it will be opened but the sensitive data will be replaced with blanks.
5.)EncryptAllWithUserKey:The entire package is encrypted based on the current user used in the PackagePassword property. The package will not be executed unless it is the same user who created it in BIDS.
6.)ServerStorage: This setting can be used only if the package is stored in the SQL Server db.Nothing in the package definition is encrypted. Instead the entire package is protected by the SQL Server db roles.
Note:
If you are deploying a package with the EncryptSensitiveWithUserKey ProtectionLevel setting to a new server the connection passwords will not be decrypted and the package execution fails.
Key Points to consider while deciding on the package ProtectionLevel Security:
1.)If all connections are windows authenticated they do not require passwords since users are authenticated with windows, you do not have sensitive info in your package. In this case use DontSaveSensitive.
2.)If there is sensitive info such as connection password you have to decrypt the password using the package pwd or by running the package on the same computer by the same user.

Managing Package Security with DTUtil:
The following eg encrypts MyPackage.dtsx with the password EncPawd. The parameter requires the package location and the encryption level and a package pwd.(0-DontSaveSensitive,1-EncryptSensitiveWithUserKey,2-EncryptSensitiveWithPassword,3-EncryptAllWithPassword,4-EncryptAllWithUserKey,5-SQLServerStorage)
dtutil.exe /file MyPackage.dtsx file;MyPackage.dtsx;3;EncPawd