Prerequisites

  • An up-and-running Microsoft Azure Synapse Analytics instance. Instructions for creating a Microsoft Azure Synapse Analytics destination are outside the scope of this tutorial; our instructions assume that you have an instance up and running. For help getting started with Microsoft Azure Synapse Analytics, refer to Microsoft’s documentation.

  • An existing Azure Storage account. Instructions for creating an Azure Storage account are outside the scope of this tutorial. For help getting started with an Azure Storage account, refer to Microsoft’s documentation.

Step 1: Verify your Stitch account's data pipeline region

First, you’ll log into Stitch and verify the data pipeline region your account is using. Later in this guide, you’ll need to grant Stitch access by whitelisting our IP addresses.

The IP addresses you’ll whitelist depend on the Data pipeline region your account is in.

  1. Sign into your Stitch account, if you haven’t already.
  2. Click User menu (your icon) > Manage Account Settings and locate the Data pipeline region section to verify your account’s region.

  3. Locate the list of IP addresses for your region:

Keep this list handy - you’ll need it later.

Step 2: Configure database connection settings

In this step, you’ll configure the database server to allow traffic from Stitch to access it. There are two ways to connect your database:

  • A direct connection will work if your database is publicly accessible.
  • An SSH tunnel is required if your database isn’t publicly accessible. This method uses a publicly accessible instance, or an SSH server, to act as an intermediary between Stitch and your database. The SSH server will forward traffic from Stitch through an encrypted tunnel to the private database.

Click the option you’re using below and follow the instructions.

I'm using a direct connection.

For the connection from Stitch to be successful, you’ll need to configure the firewall for your Microsoft Azure Synapse Analytics instance to allow access from our IP addresses.

  1. Sign into your Microsoft Azure Synapse Analytics account and navigate to your dashboard.

  2. In the sidenav, click SQL databases.
  3. On the page that displays, click the name of the database you want to connect to Stitch.
  4. The details page for the database will display. Click the link in the Server name field.

  5. The details page for the server will display. Click the Show firewall settings link in the Firewalls and virtual networks field.

  6. For each of the IP addresses you retrieved in Step 1 for your Stitch data pipeline region, create a rule:
    • Rule name: Enter a name for the rule. For example: Stitch<number>
    • Start IP: Paste one of the Stitch data pipeline region IP addresses.
    • End IP: Paste the same IP address.
  7. Click the three dots to the right of the End IP field to add the rule.

  8. Repeat steps 6 and 7 until there is a rule for each IP address for your Stitch data pipeline region. Below is an example of what this might look like using Stitch’s North America IP addresses:

    IP address rules for Stitch's North America IP addresses in Microsoft Azure firewall settings

    Note: You may also want to add your own IP address(es) to this list. This ensures that you’ll also be able to connect to the database as needed.

  9. Click Save.

I'm using an SSH tunnel to connect.

  1. Follow the steps in the Setting up an SSH Tunnel for a Microsoft Azure database guide to set up an SSH tunnel for Microsoft Azure Synapse Analytics.
  2. Complete the steps in this guide after the SSH setup is complete.

Step 3: Generate storage credentials

In this step, you’ll generate the storage credentials required to access Azure Storage. Stitch uses Azure Blob Storage to stage data for Polybase before it’s finally loaded into your Microsoft Azure Synapse Analytics.

Step 3.1: Generate a shared access signature URL

  1. In the sidenav, click Storage accounts.
  2. On the page that displays, click the name of the storage account you want to use.
  3. The middle panel contains the Storage account menu. In the Settings section, click Shared access signature.

  4. Leave the boxes for the Allowed services, Allowed resource types, and Allowed permissions checked. Because Microsoft doesn’t currently allow users to individually grant permissions on services and resources, Stitch currently requires the default configuration, which includes all permissions. Refer to Microsoft’s documentation for more info.

    Note: Despite the permissions granted here, Stitch will never delete data.

  5. Next, you’ll define the access period using the Start and End fields in the Start and expiry date/time section:

    Start and end dates for Shared Access Signature generation in Microsoft Azure settings

    To reduce the likelihood of replication interruption, we recommend setting the expiration date well into the future.

  6. In the Allowed protocols section, select HTTPS only.
  7. For the Signing key, select the name of the key you want to use. Note: Stitch doesn’t require the use of a specific key.

  8. When finished, click the Generate SAS and connection string button.

  9. A handful of fields will appear below the button. Locate the Blob service SAS URL field:

    The Blob Service SAS URL field, highlighted

  10. Copy the URL to somewhere handy - you’ll need it to complete the setup.

Step 3.2: Retrieve your storage access key

  1. In the middle panel menu, click Access keys in the Settings section.
  2. On the page that displays, locate the section for the Signing key you selected in Step 7 of the previous section. For example: If you selected key1 as the signing key, locate the section for key 1.

  3. Copy the value in the corresponding Key field to somewhere handy - you’ll need it to complete the setup.

    The Key fields are highlighted for both keys in the image below. Remember that you’ll only need the Key field value for the signing key you selected:

    Highlighted Key fields for the Storage Access Keys in Microsoft Azure

Step 4: Create a Microsoft Azure Synapse Analytics Stitch user

In this step, you’ll create a dedicated database user for Stitch. Creating a user for Stitch ensures that Stitch will be visible in any audits or logs, and that you can control the permissions granted to the user.

  1. If you haven’t already, connect to your Microsoft Azure Synapse Analytics instance using your SQL client.

  2. Navigate to the master database.

  3. In the master database, run the following commands to create a login and a user named stitch. Replace <STRONG_PASSWORD_HERE> with a strong password:

    CREATE LOGIN stitch WITH PASSWORD = '<STRONG_PASSWORD_HERE>';
CREATE USER stitch FOR LOGIN stitch;

  4. Next, navigate to the database where you want Stitch to load data.

  5. In this database, run the following commands to create a login and a database user named stitch. Replace <DATABASE_NAME> with the name of the database:

    CREATE USER stitch FOR LOGIN stitch;
GRANT CONTROL ON DATABASE::<DATABASE_NAME> to stitch;

In the table below are the database user privileges Stitch requires to connect to and load data into Microsoft Azure Synapse Analytics.

Privilege name Reason for requirement
CONTROL

Required to create the necessary database objects to load and store your data.

The CONTROL permission grants ownership-like capabilities to the Stitch user. This ensures that Stitch has all the necessary permissions to load data into the specified database, including ownership abilities on all schemas in the database, and all objects within all schemas in the database.

Step 5: Connect Stitch

To complete the setup, you need to enter your Microsoft Azure Synapse Analytics connection details into the Destination Settings page in Stitch.

Step 5.1: Locate Microsoft Azure Synapse Analytics connection details

In this step, you’ll retrieve the server address for the Microsoft Azure Synapse Analytics you want to connect to Stitch.

This is the value you’ll enter in the Host field in Stitch in the next step.

  1. From your Azure dashboard, click SQL Databases.
  2. On the page that displays, click the name of the database you want to connect to Stitch.

  3. The details page for the database will display. On this page, locate the Server name field, which is highlighted below:

    The Server name field for an Azure database, highlighted

Step 5.2: Enter connection details into Stitch

  1. If you aren’t signed into your Stitch account, sign in now.

  2. Click the Destination tab.

  3. Locate and click the Microsoft Azure Synapse Analytics icon.

  4. Fill in the fields as follows:

    • Display Name: Enter a display name for your destination, to distinguish various connections of the same type.

    • Description (optional): Enter a description for your destination.

    • Host (Endpoint): Enter the host address (server name) used by the Microsoft Azure Synapse Analytics instance. This is the Server name value you retrieved in the previous step.

    • Port: Enter the port used by the Microsoft Azure Synapse Analytics instance. The default is 1433.

    • Username: Enter the Stitch Microsoft Azure Synapse Analytics database user’s username.

    • Password: Enter the password for the Stitch Microsoft Azure Synapse Analytics database user.

    • **: Enter the name of the Microsoft Azure Synapse Analytics database you want to connect to Stitch.

    • Shared Access Signature URL: Paste the Blob service SAS URL you generated in Step 3.1.

    • Access Key for Azure Storage: Paste the Key associated with the signing key you selected in Step 3.1.

Step 5.3: Define SSH connection details

If you’re using an SSH tunnel to connect your Microsoft Azure Synapse Analytics database to Stitch, you’ll also need to define the SSH settings. Refer to the Setting up an SSH Tunnel for a Microsoft Azure database guide for assistance with completing these fields.

  1. Click the Encryption Type menu.

  2. Select SSH to display the SSH fields.

  3. Fill in the fields as follows:

    • Remote Address: Enter the public IP address or hostname of the server Stitch will SSH into.

    • SSH Port: Enter the SSH port on your server. (22 by default)

    • SSH User: Enter the Stitch Linux (SSH) user’s username.

Step 5.4: Save the destination

When finished, click Check and Save.

Stitch will perform a connection test to the Microsoft Azure Synapse Analytics database; if successful, a Success! message will display at the top of the screen. Note: This test may take a few minutes to complete.

Related Troubleshooting
Questions? Feedback?

Did this article help? If you have questions or feedback, feel free to submit a pull request with your suggestions, open an issue on GitHub, or reach out to us.