---
title: "Connecting to Microsoft SQL Server"
description: "You can establish a direct connection to Microsoft SQL Server to pull in data into xP&A.\n\nIt is possible to use an SSH tunnel for the connection."
source_url: https://support.lucanet.com/en/documentation/xp-a---extended-planning-and-analysis/integrating-data/connecting-to-a-data-source/connecting-to-a-bi-data-warehouse-system/connecting-to-mssqlserver
language: en
last_updated: 2026-05-01
---
# Connecting to Microsoft SQL Server in Lucanet xP&A

## Overview

You can establish a direct connection to Microsoft SQL Server to pull in data into Lucanet xP&A (Extended Planning & Analysis).

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.

> **Note:** 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](https://support.lucanet.com/en/documentation/xp-a---extended-planning-and-analysis/integrating-data/connecting-to-a-data-source/connecting-to-a-bi-data-warehouse-system/defining-database-queries.md).

## Prerequisites for the Setup in xP&A

### Whitelist IP Address in Microsoft SQL Server

If your database is behind a firewall, you have to whitelist the static IP address before connecting Microsoft SQL Server with xP&A.
Which IP address needs to be whitelisted depends on the region where your Lucanet instance is hosted:

| Region | IP address to whitelist |
|--------|------------|
| eu-central-1 | 52.59.129.235 |
| ap-southeast-1 | 13.214.111.217 |
| us-east-1 | 3.223.155.63, 98.87.131.112, 44.219.125.183 |
| eu-central-2 | 51.34.5.247 |
| eu-north-1 | 13.51.154.160 |

> **Tip:** The correct IP address(es) to whitelist matching your region is shown in the **New Data Source** dialog when you create the connection, so please check this information on the user interface.

### 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 in xP&A

To connect to Microsoft SQL Server in xP&A:

1. 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.](https://support.lucanet.com/assets/docs-images/xp-a---extended-planning-and-analysis/integrating-data/connecting-to-a-data-source/connecting-to-a-bi-data-warehouse-system/connecting-to-mssqlserver/_images/en/new-data-source-data-workspace.png)
    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.](https://support.lucanet.com/assets/docs-images/xp-a---extended-planning-and-analysis/integrating-data/connecting-to-a-data-source/connecting-to-a-bi-data-warehouse-system/connecting-to-mssqlserver/_images/en/new-data-source-model.png)
    New data source connection from within a model

2. 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.](https://support.lucanet.com/assets/docs-images/xp-a---extended-planning-and-analysis/integrating-data/connecting-to-a-data-source/connecting-to-a-bi-data-warehouse-system/connecting-to-mssqlserver/_images/en/bi-database-tab.png)
    'BI/Database' tab

3. The **New Data Source** dialog is displayed as follows:

    ![Shows the 'New Data Source' dialog for an MS SQL Server connection](https://support.lucanet.com/assets/docs-images/xp-a---extended-planning-and-analysis/integrating-data/connecting-to-a-data-source/connecting-to-a-bi-data-warehouse-system/connecting-to-mssqlserver/_images/en/new-data-source-ms-sql-server.png)
    'New Data Source' dialog

4. Configure the steps as described in the following section.

5. Click **Create data source**.

### Set-up Steps

The following steps are required to set up the data source:

1. **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**
    - N **ame 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

    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

    > **Tip:** 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.

2. **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](https://support.lucanet.com/en/documentation/xp-a---extended-planning-and-analysis/integrating-data/connecting-to-a-data-source/connecting-to-a-bi-data-warehouse-system/defining-database-queries.md).
    - 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.)

    > **Tip:** 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**.

    ![Shows the 'Complete the query form' step of the connection setup, with fields for Data Source Name, Query, Date column, and Variable columns.](https://support.lucanet.com/assets/docs-images/xp-a---extended-planning-and-analysis/integrating-data/connecting-to-a-data-source/connecting-to-a-bi-data-warehouse-system/connecting-to-mssqlserver/_images/en/complete-query-form.png)