Summary

Data Import 1.2 executes PowerShell scripts in Constrained Language Mode and also constrains the runspace to explicitly allowed commands. Together, these features drastically limit exposure in the event PowerShell scripts are compromised.

Introduction

Data Import 1.2 increases the flexibility of working with PowerShell-based preprocessor scripts.

Earlier versions of Data Import provide support for agent-based Custom File Generator and Custom Row Processor scripts, both maintained via known file system locations.

With Data Import 1.2, the data map-based Custom File Processor script is introduced to enable transforming any kind of input file via script into a CSV-compatible format for mapping.

Additionally, Data Import 1.2 provides a UI for managing all scripts (which are relocated from the file system to the database), and Custom File Processors are now automatically packaged with shared data maps.

With increased support for PowerShell script execution, Data Import 1.2 also includes additional security measures to mitigate attack vectors.

This document explains how those measures work and why they are important.

Constrained Language Mode

By default, a PowerShell session runs in Full Language mode, which permits all language elements.

Data Import 1.2 uses Constrained Language mode instead. As its name implies, this mode limits available language elements to a defined subset of features that an explicitly allowed set of capabilities.

While constrained language mode does not limit the use of cmdlets, this limitation is implemented via a constrained runspace (described next).

A significant limitation of constrained language mode is to limit the use of .NET objects to a specifically allowed subset of types. The cmdlet New-Object is blocked, but the versatility of PowerShell means there are many ways to instantiate an object, so constrained language mode also prohibits the execution of methods on disallowed types.

What if you have a legitimate need to invoke a .NET type that is disallowed? As a developer, you can create cmdlets to wrap the required functionality and then deploy your cmdlets into your environment. This is described in more detail later.

Read about PowerShell language modes.

Constrained Runspace

A PowerShell session executes in the context of a runspace. The default runspace provided by Windows PowerShell includes all available core commands.

Data Import 1.2 uses a custom runspace to constrain execution to only a subset of available commands. The implementation of this is to create an empty runspace that includes no allowed commands, then add a standard set of cmdlets generally known to be useful for use by Data Import, then add a custom set of cmdlets explicitly defined by the developer.

Read about PowerShell constrained runspaces.

Creating and Using Cmdlets

As previously described, rather than allowing all core commands, Data Import established an empty runspace and adds only those commands that have been explicitly authorized.

First, a "whitelist" of generally useful (but safe) cmdlets are added:

• ConvertFrom-Json

• Get-Date

• Measure-Object

• Out-Null(note: prefer use of  > $null instead)

• Write-Output

• Write-Error

• Write-Warning

• Write-Information

• Write-Host

Second, a set of cmdlets written specifically for use by Data Import are added:

• New-ArrayList, Add-CollectionItem, and Remove-CollectionItem provide the ability to work with an ArrayList.
• ConvertFrom-FixedWidth is a utility to convert from a fixed-width row of text to an array of items.
• Invoke-OdsApiRequest is a light wrapper for Invoke-WebRequest that executes against the currently active API connection without explicitly providing credentials.
• Set-AgentCacheItem and Get-AgentCacheItem provides a memory-cache that persists for a single agent file execution.

Finally, an explicit set of cmdlets are added as specified in the columns ImportPSModules and AvailableCmdlets of the Configurations database table. See the documentation on using cmdlets with Data Import for additional details.

Upgrading From an Earlier Version of Data Import

If you are upgrading an existing Data Import installation and you are currently using Custom File Generator or Custom Row Processor scripts, then there is a distinct possibility that those scripts will cease to function due to the new security constraints.

As a temporary workaround for this limitation, you can add an ap configuration setting to the DataImport.Server.TransformLoad project as follows:

<appSettings>
  <add key="UsePowerShellWithNoRestrictions" value="True" />
</appSettings>

Understand that this should only be used on a temporary basis and as a last resort because it disables the security constraints described in this article.

A more permanent solution will require reviewing your existing scripts, identifying what changes can be made to work within the defined security constraints, and as needed, creating custom commands to wrap execution of prohibited functionality.

Note that this configuration setting does not affect the new Custom Files Processor scripts, which will always execute with all security constraints in place regardless of this configuration setting value.

Other Security Options

It is beyond the scope of this article to describe configuration options to minimize the attack surface area of Data Import, but some options to consider include:

• Disallowing public internet access to the website and transform load job execution.
• Limiting the security access for the application pool identity.
• Running data import in containers.