Microsoft Azure Setup Requirements

To set up Microsoft Azure in Stitch, you need:

  • An up-and-running Azure SQL Data Warehouse instance. Instructions for creating a Azure SQL Data Warehouse data warehouse are outside the scope of this tutorial; our instructions assume that you have an instance up and running. For help getting started with 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 server firewall access

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

  1. Sign into your 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 virutal 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 1
    • 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/32

    • 52.204.223.208/32

    • 52.204.228.32/32

    • 52.204.230.227/32

  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.

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 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 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 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.

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 Azure SQL Data Warehouse connection details into the Destination Settings page in Stitch.

Step 4.1: Locate Azure SQL Data Warehouse connection details

In this step, you’ll retrieve the server address for the 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. Click the Azure SQL Data Warehouse icon.
  4. Fill in the fields as follows:

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

    • Port: Enter the port used by the Azure SQL Data Warehouse instance. The default is 1433.

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

    • Password: Enter the password associated with the Stitch Azure SQL Data Warehouse user.

    • Database: Enter the name of the database in your Azure SQL Data Warehouse data warehouse you want to connect to Stitch.

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

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

When finished, click Update Azure SQL Data Warehouse Settings.

Stitch will perform a connection test to the 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.


Troubleshooting connection errors

If you receive an error message, we recommend trying these troubleshooting steps for common destination connection problems before reaching out to support.

We’ve found that the majority of the time, these resources can resolve most issues.


Learning about Stitch and Azure SQL Data Warehouse

Now that your Azure SQL Data Warehouse data warehouse is up and running, we recommend learning about how Stitch will load your data into it.


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.

Tags: