Security Considerations

Overview

Being a cloud hosted solution, there have been many considerations and precautions taken with the development of Loome Integrate Online so that your data is handled in a matter that is as secure as possible.

This article will detail how Loome Integrate handles sensitive information throughout the application as well as where your data is actually passed through during the execution of jobs and tasks.

The main points of this article are:

  • The Loome Integrate Agent communicates with the server using HTTPS and Websockets and so Port 443 must be opened on the server which the agent is hosted on.
  • All interactions between the agent and the server are secured using HTTPS as well as the contents of the transmissions to the agent being securely encrypted using an asymmetric encryption algorithm specific to that agent.
  • Windows installations of the agent will require a user account with “Logon as a Service” permissions.
  • Linux installations of the agent will require the creation of a loomeusers group for the purposes of file permissions.
  • No client data (for example the data you are migrating during a data migration) passes through the Loome Integrate Online servers and instead data is passed through the agent to its destination with no local caching.
  • No matter whether a connection is on-premises or in the cloud, the client data will never pass through the Loome Integrate Servers.
  • The tenant databases are secured using the security features provided by Azure SQL.
  • The connection details to tenant databases are secured using the security features provided by Azure App Service.
  • Connection passwords and credentials are saved using a tenant unique, symmetric encryption algorithm.
  • Loome Integrate does support authentication through SQL Server Trusted Authentication in the instances where the agent is running as a trusted user.
  • The only data saved in Data Migration tasks are metadata/schema details about the data being migrated, none of the data itself.
  • Loome Integrate cannot be held responsible for any security issues that may come of clients including sensitive data in plain text within tasks.
  • Application logs in Loome Integrate will never contain client data, only diagnostic information about the task itself.

Connecting the Agent to the Server

When installing the Loome Integrate Agent the first considerations that need to made are what security exceptions need to be made for the Agent to work on your server, and how messages are transmitted between the server and the REST API.

Firewall & Ports

The agent itself does not host any servers, instead it communicates with the Loome Integrate Server over a full duplex WebSocket Connection. WebSockets are a W3C Standard Protocol for communicating over the internet in a realtime fashion. The main notable feature of WebSockets is in relation to the ports required, they run off the standard ports for HTTP (80) and HTTPS (443).

As Loome Integrate Online uses HTTPS for transmission, this means that only port 443 will need to be opened for the agent to communicate with the server.

This means that hosting the agent only requires a server that you are able to browse the internet from, and there are no additional requirements assuming you have opened those ports.

Firewall Rules

If the Loome Integrate Agent is sitting behind a VNet, you will need to allow the following connections to the host, according to your location, as this is the server the agent uses to connect to Loome Integrate Online.

AU

  • dg-api-au.perspectiveilm.com:443
  • identity-au.perspectiveilm.com:443
  • blob.core.windows.net:443
  • logging-au.loomesoftware.com:443

US

  • dg-api-us.perspectiveilm.com:443
  • identity-us.perspectiveilm.com:443
  • blob.core.windows.net:443
  • logging-us.loomesoftware.com:443

Transmission Security

All communication between clients and server in Loome Integrate are over HTTPS, meaning that all packets are securely encrypted. Messages sent to the agent are also encrypted using a unique asymmetric decryption algorithm, which means that even if HTTPS wasn’t implemented the messages would still be secured.

Service Accounts

If you are running the Agent on Windows, it is required that the agent runs with a user account that has the “Logon as a Service” permission enabled.

On Linux, a user group will be called loomeusers will be created during installation. This group is purely for managing file permissions in the /.loomeagent/ directory, the user the agent is running as will be added to this group.

Data Migration & Data in Motion

Loome Integrate Online’s Data Migration task allows you to easily move data across different sources and targets, even if they’re not on the same host. A major aspect of Data Migrations and all tasks in Loome Integrate Online is that none of your data ever passes through our servers, nor is it saved in any form on your tenant database.

This requirement of ensuring client data is never passed through our servers during the data in motion stage of tasks is the reason why you are required to install the Loome Integrate Agent - as you have full control over where it is installed and can assume that your data is only passing through the server which the agent is hosted on.

With Data Migration in particular, the agent will never save data from the migration. It is pulled from the source and pushed to the target, with the only transmissions to the server being in the form of logs and emitting the result of the task whether it is a success or a failure.

The following diagram provides a visualisation for how data flows in a Data Migration task;

data in motion

As you can see the blue lines (representing your data being migrated) never leaves the path from the source connection, through the agent and to the target connection. The data transmitted from the agent to the server contains no data from the migration, instead providing helpful status updates on the migration and the job as a whole.

This flow does not change for cloud and on-premises connections, whilst you may also wish to use Azure SQL as a migration source or target the agent will never transmit that data through Loome Integrate’s Azure SQL Server.

The Tenant Database

Each Loome Integrate Online tenant has an associated database with it. For instances where you as a user have multiple tenants, switching between tenants is essentially a case of changing what database your user account is querying.

Loome Integrate Online has been designed from the ground up to only store data required by Loome Integrate to operate as expected.

Before detailing how each area of Loome Integrate is particularly handled in the database, the following pieces of information are standard across all database transactions:

Connection Data

Connections in Loome Integrate are the foundation for working with a variety of data sources and targets. As the Agent must be able to connect to these sources and targets, connection details are stored in the connections table of the tenant.

This is the only scenario where Loome Integrate stores data on the tenant that is considered sensitive to the users of the product. To factor this in, additional security considerations are taken when working with connection data.

Usernames & Passwords

Usernames and passwords for connections are stored separately from the connection string. Take the following example for an Azure SQL Database below:

username and password

In the case of Azure SQL, the username and password would usually be passed into the connection string as User Id and Password respectively. In Loome Integrate, we do not include the fields in the connection string and instead enter them into the provided inputs above the connection string editor.

This will store the username and password separately from the connection string. The password field is also treated with extra care, the value for password encrypted with a per-tenant symmetric key (on top of the encryption provided by Azure SQL). The only time this value is decrypted is when it’s sent to the agent for usage in a task, the decrypted value is not stored anywhere and disposed of as soon as the operation is completed.

Recommendations for Avoiding Storing Passwords in Loome Integrate

If you are still not comfortable storing the password in Loome Integrate’s servers despite the considerations taken, there are a few means of accessing some data sources/targets without providing credentials.

  • SQL Server
    • When the Agent is installed On-Premises as a Windows Service, the user account running the service should be configured as a trusted user of the SQL Server so that the Agent is able to connect using Trusted Authentication rather than a username and password.
  • ODBC
    • In most cases for ODBC, you will not need to provide credentials for connecting to a DSN as the credentials are associated at the DSN level.
    • This means that the connection credentials are saved on the server the DSN was configured on, not the Loome Integrate Online server.

Task Contents

When working with Loome Integrate tasks, there are plenty of areas where the task has user inputted content that could contain sensitive data.

Loome Integrate Online cannot protect against sensitive content being included in tasks - it is the responsibility of the user to ensure that tasks do not contain sensitive information.

There are a few considerations that can be taken for the various task types:

Data Migration

Data Migration tasks only have data relating to the schema of the data being migrated, no information about the contents of the data sources are kept in the task.

That being said, when a task uses Query as Source ensure that your query does not contain any sensitive information.

Script Based Task Types (Powershell, SQL Statement, OS Command, Python, etc…)

These tasks involve the user providing a script for the Loome Integrate Agent to execute; with this user input, there is a possibility of the user providing sensitive information in the task. This can be problematic as not only is the data saved in the task table, logging from the task executions may duplicate the sensitive data across app logs.

If you require the usage of sensitive data in scripts, it is recommended you provide the value using environment variables on the host of the agent.

For example in Powershell:

# Retrieve a password value from a pre-defined environment variable.
$password = $env:EXAMPLE_PASS;

Application Logs

One of Loome Integrate’s major features is its extensive logging capabilities. The only information provided by logging includes:

  • Task Diagnostics
    • Execution status.
    • Execution time.
    • Execution errors.
  • Connection Schema
    • This is just in the form of stating the database and table that is being used.
  • SQL Queries
    • This is for debugging Data Migrations and SQL Statements.
  • Script Contents
    • This is for debugging script based task types.
  • Standard Output/Error
    • Scripting tasks convert stdout and stderr to Information and Error logs respectively.
    • This means that provided scripts should be wary of what is being logged.