Connecting to Microsoft SQL Server

Last updated on 2025-11-17

Overview

You can establish a direct connection to Microsoft SQL Server to pull in data into xP&A.

It is possible to use an SSH tunnel for the connection.

 

This article contains a description of the prerequisites and the individual steps of the set-up process.

The fields that are pulled in must be defined during the set-up process using a database query. For a detailed instruction on how to structure such a query, see Defining Database Queries.

This article contains the following sections:

Prerequisites for the Setup
Whitelist IP Address in Microsoft SQL Server

If your database is behind a firewall, you have to whitelist the following static IP address before connecting Microsoft SQL Server with xP&A:

  • 52.59.129.235
Optional: Preparation for Connection via SSH Tunnel

If you want to establish a connection via an SSH tunnel, a user called causal must be created on the remote SSH server and the following public key must be associated with it:

ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAIP9DCR3hBNU/01YU9iK1JMKx2HNsK1+3ChWj3yBqb+Kr causal

Connecting to Microsoft SQL Server

To connect to Microsoft SQL Server:

Choose one of the following options:

  • Open the Data workspace from the overview on the start page and click + New.
Shows the start page of Lucanet xP&A. Highlighted are the buttons that need to be used to create a new data source connection from the start page.
New data source connection from the Data workspace
  • Open the model into which you would like to integrate the data, click the + sign next to Data in the overview, and choose New data source:
Shows the view of a model. Highlighted are the buttons that need to be used to create a new data source connection from the model view.
New data source connection from within a model

In the Data sources dialog, open the BI/Database tab and choose Microsoft SQL Server:

Shows the Data Sources dialog. Highlighted in red is the 'BI/Database' tab.
'BI/Database' tab

The New Data Source dialog is displayed as follows:

Shows the 'New Data Source' dialog for an MS SQL Server connection
'New Data Source' dialog

Configure the steps as described in the following section.

Click Create data source.

Set-up Steps

Step

Description

Choose a connection

Choose an existing connection, or, if you have not configured a connection yet, click New Connection and enter the following in the New Microsoft SQL Server connection dialog:

  • Microsoft SQL Server database host name without port and database
  • Microsoft SQL Server database port
  • Name of the database to which you want to connect
  • Database user name
  • Database user password
  • Whether to use encryption when connecting to Microsoft SQL Server

 

Upload file step
'New Microsoft SQL Server Connection' dialog

If you want to establish a connection via an SSH tunnel, additionally enter:

  • Host name of the SSH server used to establish a tunnel connection to the database
  • Port of the SSH server used to establish a tunnel connection to the database

If you configure access to your data warehouse via SSH:

  • Make sure to fulfill the prerequisites for the setup.
  • Remember to update the database hostname to be the database’s IP internal to your network.
  • If you wish to update an existing connection to use SSH, please contact us.

Complete the query form

Enter the following:

  • Data Source Name
  • Query to define the fields which are to be pulled in. For more information, see Defining Database Queries.
  • Name of the Date column, which must be one of Microsoft SQL Server's date formats 
  • Names of the columns that contain variables (which must have a numeric data type.)

Any remaining columns will be treated as dimensions, and must have a string data type.

An exception is the cohort dimension, which must be a date, with the column header explicitly labelled Cohort.
Contact Us