This document provides information on how to set up necessary security access in SQL Server related to execution of SSIS (SQL Server Integration Services) packages.
Configuration is only needed on the server that is supposed to execute the SSIS packages during the ETL process.
It is required that the owner of SSIS related jobs represents an account that is in the sysadmin fixed server role. This means that SSIS package steps will be executed under the account used by the SQL Server Agent service. IFS Analysis Models can only handle this situation.
If a Proxy Account is defined as the SSIS job owner when running the installer, then the execution of the job might fail.
Also make sure that SSIS-user and SQL Server Agent users set up under Windows Services are not defined with Automatically generated accounts like Network service or something similar.
IMPORTANT! In order to get integration with Power BI to work properly, or if you have a clustered SQL Server farm you should set up all SQL Server services with a domain user with local administrator priviliges.
A local user group named DTSReader must be added. The existence of the group is required by the IFS Analysis Models Installer.
It is required to add the users related to the services SQL Server Account (MSSQLSERVER) and SQL Server Integration Services to the local user group DTSReader.
These users are required in order to enable execution of SSIS related jobs, as created by the IFS Analysis Models Installer.
In order to enable execution of SSIS packages in the IFS AM Instance Home via e.g. the solution file All_IFS_SSIS.sln, it will be necessary to add these users as well to the group DTSReader. A good practice would be to add the users that are supposed to handle the installation of IFS Analysis Models to the group.
In the above case the user Administrator, i.e. the log on account for the mentioned services, plus two additional users have been added to the local group DTSReader.
After having added users to the local group DTSReader it is necessary to restart the SQL Server Agent and SSIS services. Also make sure that others users added to the list logs off and then on again.
The IFS Analysis Models Installer will perform the following:
The reason for setting permissions on the configuration files is related to security.
The permissions are set as shown below. DO NOT change these permissions.
Advanced properties will also be applied, making sure that inherited permissions are not included.
The BI Instance Home that is created via the
IFS Analysis Models
Installer represents the consolidated code
structure that supports installation of
IFS Analysis Models
on SQL Server. In a standard case one instance home is built
for a test installation and another instance home is built
for the production installation.
In both these cases
there might be a need to test one or more SSIS packages if
the package execution for some reason fails or leads to
unexpected result. To handle this it is possible to use the
solution file called All_IFS_SSIS.sln
delivered by IFS. The file is available in the sub folder
<AM_INSTANCE_HOME>\SSIS.
In order for the user to successfully run the solution file without getting problems with permissions on the referenced DTS configuration files, the solution is to add the user(s) to the local group DTSReader.