As a database professional we are committed to protect the environment. However one of the places I find often unnoticed is SSIS security configuration from server level.
Typically a great deal of security are typically handled by entire infrastructure (includes from firewall to SQL Server itself) where the SSIS package executes and also by the design of package itself. However I suggest being very careful for SSIS handling with sensitive data.
On top of ensuring that the SSIS package itself is good in security design (typically this is responsibility of developers and testers) I recommend to do below configuration changes from server level:
1. Search for MsDtsSrvr.ini.xml. It will be in
Then search for following string in the file:
< ServerName>.< /ServerName> (Note! I had to add a space between < and following character for formatting. In actual file, there is no space.)
Once you detect the string, replace . with the SQL Server name where you installed SSIS
For example, if the SQL Server name is “SOMESERVER”, then the modified string will be like
2. Open DCOM Config from Component manager. It will be available at below path:
For SQL 2005 - Component Services -> Computers -> DCOM Config -> MSDtsServer
For SQL 2008 - Componenct Services -> Computers -> DCOM Config -> MSDtsServer100
Consider to change the security settings here for highly sensitive server. Though keeping this one as Default is acceptable at most of the time, I recommend reviewing the permission settings for all options for a sensitive production server.