Prerequisites

  • An up-and-running Microsoft Azure SQL Data Warehouse instance. Instructions for creating a Microsoft Azure SQL Data Warehouse 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 SQL Data Warehouse, 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: 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 SQL Data Warehouse instance to allow access from our IP addresses.

  1. Sign into your Microsoft Azure SQL Data Warehouse account.

  2. If you aren’t automatically brought to your dashboard, navigate there.

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

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

  7. For each of Stitch’s IP addresses listed below, create a rule:
    • Rule name: Enter a name for the rule. For example: Stitch <number>
    • Start IP: Paste one of Stitch’s IP addresses.
    • End IP: Paste the same IP address.

    Stitch’s IP addresses are:

    • 52.23.137.21

    • 52.204.223.208

    • 52.204.228.32

    • 52.204.230.227

  8. Click the three dots to the right of the End IP field to add the rule.

  9. Repeat steps 7 and 8 until there is a rule for each IP address. The screen should look similar to the following when you’re finished:

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

  10. 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 SQL Data Warehouse.
  2. Complete the steps in this guide after the SSH setup is complete.

Step 2: 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 SQL Data Warehouse.

Step 2.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 2.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 3: Create a Microsoft Azure SQL Data Warehouse 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 SQL Data Warehouse 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 SQL Data Warehouse.

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 4: Connect Stitch

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

Step 4.1: Locate Microsoft Azure SQL Data Warehouse connection details

In this step, you’ll retrieve the server address for the Microsoft Azure SQL Data Warehouse 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 4.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 SQL Data Warehouse icon.

  4. Fill in the fields as follows:

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

    • Port: Enter the port used by the instance. The default is 1433.

    • Username: Enter the Stitch Microsoft Azure SQL Data Warehouse database user’s username.

    • Password: Enter the password for the Stitch Microsoft Azure SQL Data Warehouse database user.

    • Database: Enter the name of the Microsoft Azure SQL Data Warehouse database you want to connect to Stitch.

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

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

Step 4.3: Define SSH connection details

If you’re using an SSH tunnel to connect your Microsoft Azure SQL Data Warehouse 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 4.4: Save the destination

When finished, click Check and Save.

Stitch will perform a connection test to the Microsoft Azure SQL Data Warehouse database; if successful, a Success! mesage 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.