Skip to content

AM Infrastructure setup on-prem for Self-hosted BI

Disclaimer

Please note that this is a guideline, and the setup will still need to be verified by your company security board to ensure your company standards and policies have been adhered to. IFS takes no responsibility for any data breach for any artifact hosted outside of the IFS domain.

This section guides you through the provisioning and configuration of resources on-prem for Self-Hosted BI.

An overview of the components to be created is on the right side of this image. This image depicts Scenario 6 specifically, where IFS Cloud production is hosted by IFS, and BI infrastructure is hosted by the customer.

1. SQL Server Installation

  • Download and run the setup for SQL Server. (You can use Standard or Enterprise Edition).
  • Under Feature Selection, select the following features:
    • Database Engine Services
    • Analysis Services
    • Integration Services
  • Under Instance Configuration, you can select the default instance or a named instance.

  • Under Server Configuration, since the SQL Server Agent is needed, it is recommended to have the Startup Type to Automatic.

  • Under Database Engine Configuration, select the Authentication Mode as Mixed Mode. You would need to set a sa password and also add administrators.

  • Under Analysis Services Configuration, select the Server Mode as Tabular Mode. You would need to select administrators as well.
  • Complete the setup with default options.

2. Create a new Database for the IFS Data Warehouse

  • You may keep the Recovery model as Simple for improved performance and space.
    • e.g. ifs-sql-dw

3. User Setup

3.1 User Setup Overview

Two users need to be created as in the below image and description in subsections below.

3.2 SQL Server user

  • Create a login with SQL Server authentication.
    • e.g. ifs-bi-user
  • Grant db_owner for the database created above as ifs data warehouse.
  • Grant db_owner for the SSISDB.
  • Grant SELECT, EXECUTE GRANT on msdb.
  • Grant SQLAgentOperatorRole role on msdb.
  • Important:
    • The user needs elevated privileges during the SQL environment setup step.
    • Therefore before you run the Environment Setup Assistant grant sysadmin privilege to this user
      • This is needed to deploy the SSIS Project and create the SSIS Agent Job under the SSAS user.
    • After the setup assistant finishes (takes usually about 5 minutes), remove the sysadmin privilege.

3.3 SSAS User

  • Create a domain account.
    • e.g. <domain>\ifsbiuser
  • This account needs to be set up as an administrator in SSAS.
  • The Agent job service should be run using this user.
  • Alternatively, if you want to use the existing domain account running the Agent Job
    • Make that user an Administrator in SSAS,
    • Use it as the SSAS username in the CSM Portal Form here.

4. Create a new Catalogue Folder in Integration Services

  • Login in to SQL Server using SSMS.
  • Expand the Integration Services Catalogs folder and right-click on SSISDB.
  • Choose the Create Folder... option.
    • e.g. ifs-etl

5. ODAC Installation

  • You need to install this driver on the same machine that has SQL Server.
  • 64-bit ODAC 12c Release 4 (12.1.0.2.4) for Windows x64.
  • This file is available as a download from Oracle here.

Note

The below steps are only applicable in Scenario 6, i.e. when IFS Cloud is hosted by IFS, and BI infrastructure is hosted in the Customer on-prem network.

6. Network Setup

  • SQL Server needs to be set to a public IP address or an FQDN, and its firewall needs to be set as follows:
    • It allows only the IFS outbound IP address for you.
    • This outbound IP address is available in the Cloud Operations Guide (CoG) document.
    • There are two unique static IPs per customer, one for PRD and the other for Non-PRD.
    • Only port 1433 (or the port the instance is running on), and TCP/IP should be allowed for this IP.
  • The public IP/FQDN should be internally resolved to the private IP of the SQL Server.
    • The SSIS packages that IFS installs in the SSISDB also communicates with the SQL Server, and these calls should be routed to the private IP of the SQL Server.
    • To achieve this scenario, where SQL Server needs to be accessed using the same IP address/FQDN both externally and internally, you can set up Split DNS or a Hosts file entry on the server running the SQL Server instance.
    • Please consult your network engineering team for setting this up.
  • Encrypted connections can be enabled to further secure this communication.
  • Refer to step 7 below for setting up encrypted communications.

7. Setting up Encrypted only Connections

Data from the Oracle transactional server travels through the VPN tunnel securely, but SQL server connections are made over the internet to trigger SSIS packages. Therefore it is recommended to have encrypted connections enabled.

  • Configure SQL Server Database Engine for encrypting connections. (Please follow this Microsoft guide).

  • Configure the below System Parameters accordingly.

    Parameter Description
    ENABLE_ENCRYPT_SQL_CONN Used to specify the value to the "encrypt" connection string property. It should be set to TRUE to enable encryption.
    TRUST_SERVER_CERTIFICATE Used to specify the value to the "trustServerCertificate" connection string property. It should be set to FALSE if you have provided a public CA certificate for the SQL Server. If self-signed this needs to be set to TRUE.
    HOST_NAME_IN_CERT Used to specify the value to the "hostNameInCertificate" connection string property. The hostname is to be used to validate the SQL Server TLS/SSL certificate. Make sure the value passed to "hostNameInCertificate" matches the Common Name (CN) or DNS name in the Subject Alternate Name (SAN) in the server certificate for a TLS connection to succeed.

Refer to the following Microsoft documentation to find more information and get an understanding of these connection string properties before setting the values.

  1. Understanding encryption support
  2. Setting the connection properties

8. Verify Connections

  • Make sure the site-to-site VPN is functioning and the IFS Oracle database is connectable using ifsinfo user.
  • The Private IP for Oracle databases and the SID are available in the CoG document.
  • The connection can be validated using a tool like Oracle "SQL*Plus" or by creating a linked server in SQL Server.