Create a connection in Anaplan Data Orchestrator to import data from Microsoft SQL Server (MSSQL). Then use the connection to extract data and create a source dataset.

You must have an MSSQL instance. This can be hosted on premises or on a cloud platform. This could be Amazon Web Services (AWS), Azure, Google Cloud Platform (GCP), or any other cloud provider. The solution is also compatible with on-premises SQL Server deployments. 

You also need to have a database user that has a password with at least read permission. Here is an example of how to create a new database user with read access:

USE {database name};
CREATE LOGIN {user name}
 WITH PASSWORD = '{password}';
CREATE USER {user name} FOR LOGIN {user name};
EXEC sp_addrolemember 'db_datareader', '{user name}';
EXEC sp_addrolemember '{role name}', '{user name}'; 

Create a connection to MSSQL

The Microsoft SQL Server (MSSQL) connector also supports connections to Azure SQL.

You will need your MSSQL or Azure credentials. View the MSSQL or Azure documentation for more information about your credentials.

To create a connection:

  1. Select Data Orchestrator from the top-left navigation menu.
  2. Select Connections from the left-side panel, then select Create connection.
  3. Select Microsoft SQL Server (MSSQL) and then select Next.
    If you can't find the connector, enter a search term in the Find... field.
  4. On the Connection details page, enter a Name and Description for your connection, and then select Next.
  5. On the Connection credentials page, enter your MSSQL credentials, and then select Next.
    See the table below in the Connection credential fields section for more information about the fields.
  6. After the connection test is complete, select Done.

Connection credential fields

This table provides more information fields that display on the Connection credentials page.

Connection credentials fieldsDescription
SSH Tunneling

This feature lets you securely connect to MSSQL instances with a Secure Shell (SSH) tunnel. It also adds another layer of security.

SSH tunneling options:

  • No Tunneling: Select this option if you don't want to connect with an SSH tunnel.
  • SSH Tunneling Key Authentication: If you select this option, you must complete these fields:
    • SSH Login Username: The username used to authenticate the SSH tunnel login.
    • SSH Private Key: The private key in unencrypted PEM format used for SSH authentication. Here's an example of the private key format you need to include in the field:

-----BEGIN PRIVATE KEY-----MIIEvAIBADANBgkqhkiG9w0BAQEFAASCBKcwggSjAgEAAoIBAQDxq...
... (Your private key content in Base64) ...
... multiple lines ...
-----END PRIVATE KEY-----

    • SSH Tunnel Jump Server Host: The hostname or IP address of the SSH Jump Server that acts as the tunnel.
    • SSH Connection Port: The SSH port number.
UsernameThe user name required to authenticate.
PasswordThe password required to authenticate.
HostThe MSSQL host domain. The host is provided by the administrator of your MSSQL or Azure SQL instance.
Port

The MSSQL port number. The port number is provided by the administrator of your MSSQL or Azure SQL instance.

The port number is typically 1433.

DatabaseThe MSSQL database name.
Schemas

The MSSQL database schema. This field is optional. 

You can enter a single schema name, or leave the field empty. If the field is empty, Data Orchestrator retrieves all schemas.

Extract data from the MSSQL connection

You can extract data from the MSSQL connection to add source data to Data Orchestrator. The data extract creates a source dataset.

To extract data:

  1. Select Data Orchestrator from the top-left navigation menu.
  2. Select Source data from the left-side panel.
  3. Select Add data > From connection.
  4. On the Dataset details page, enter these details and select Next:
    • Connection: Select the MSSQL connection you want to base the dataset on. 
    • Dataset name: Create a name for the dataset.
    • Description: Enter a description about the dataset.
    • Namespace: Select a namespace. This is the MSSQL schema for the specific database defined for the connection. 
    • Path name: Select a path name. This is the MSSQL table name. 
  5. On the Choose an upload type page, enter these details and select Next:
    1. Select the Load type:
      • Full replace: Completely replaces the current loaded data with the new data.
      • Append: Adds the new data to the end of the current table.
      • Incremental: Takes the new data and incrementally updates the contents of the dataset based on matching the primary key fields. Exiting data is updated and new data is appended.
    2. Select the columns to import.
  6. Select Create in the confirmation dialog.