# Connecting a Microsoft SQL Server Destination to Stitch ## Prerequisites * **An up-and-running Microsoft SQL Server instance.** Instructions for creating a Microsoft SQL Server 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 SQL Server, refer to [Microsoft’s documentation](https://docs.microsoft.com/en-us/sql/sql-server). ## 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](https://help.qlik.com/en-US/stitch/security/supported-data-pipeline-regions). 1. [Sign into your Stitch account](https://app.stitchdata.com/), if you haven’t already. 2. Click **User menu (your icon) > Edit User Settings** and locate the **Data pipeline region** section to verify your account’s region. 3. Locate the list of IP addresses for your region: * [North America region IP addresses](https://help.qlik.com/en-US/stitch/security/stitch-ip-addresses#north-america-ip-addresses) * [Europe region IP addresses](https://help.qlik.com/en-US/stitch/security/stitch-ip-addresses#europe-ip-addresses) 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 an SSH tunnel to connect. 1. Follow the steps in the [Setting up an SSH Tunnel for a database connection guide](https://help.qlik.com/en-US/stitch/security/data-encryption/setting-up-ssh-tunnel-for-database-connection) to set up an SSH tunnel for Microsoft SQL Server. 2. Complete the steps in this guide after the SSH setup is complete. #### I'm using a direct connection. For the connection to be successful, you’ll need to configure your firewall to allow access from our IP addresses. The IP addresses you’ll whitelist depend on the [**Data pipeline region** your account is in](https://help.qlik.com/en-US/stitch/security/supported-data-pipeline-regions). 1. [Sign into your Stitch account](https://app.stitchdata.com/), if you haven’t already. 2. Click **User menu (your icon) > Edit User Settings** and locate the **Data pipeline region** section to verify your account’s region. 3. Locate the list of IP addresses for your region: * [North America region IP addresses](https://help.qlik.com/en-US/stitch/security/stitch-ip-addresses#north-america-ip-addresses) * [Europe region IP addresses](https://help.qlik.com/en-US/stitch/security/stitch-ip-addresses#europe-ip-addresses) 4. Whitelist the appropriate IP addresses. ## Step 3: Create a Stitch Microsoft SQL Server database user Information note **Note**: You must have superuser privileges or the ability to create a user and grant privileges to complete this step. In the following tabs are the instructions for creating a Stitch Microsoft SQL Server database user and explanations for the permissions Stitch requires. * Instructions * Privileges list Information note **Why does Stitch require these permissions?** For an explanation of why Stitch requires each permission outlined here, see the **Privileges list** tab. 1. If you haven’t already, connect to your Microsoft SQL Server 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 `` with a strong password: ``` CREATE LOGIN stitch WITH PASSWORD = ''; CREATE USER stitch FOR LOGIN stitch; ``` `CREATE LOGIN stitch WITH PASSWORD = ''; CREATE USER stitch FOR LOGIN stitch;`CREATECREATE LOGINLOGIN stitchstitch WITHWITH PASSWORDPASSWORD == '<STRONG\_PASSWORD\_HERE>''<STRONG\_PASSWORD\_HERE>';; CREATECREATE USERUSER stitchstitch FORFOR LOGINLOGIN stitchstitch;; 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 `` with the name of the database: ``` CREATE USER stitch FOR LOGIN stitch; GRANT CONTROL ON DATABASE:: to stitch; ``` `CREATE USER stitch FOR LOGIN stitch; GRANT CONTROL ON DATABASE:: to stitch;`CREATECREATE USERUSER stitchstitch FORFOR LOGINLOGIN stitchstitch;; GRANTGRANT CONTROLCONTROL ONON DATABASEDATABASE::::<<DATABASE\_NAMEDATABASE\_NAME>> toto stitchstitch;; 6. Run the following commands to allow the user to create tables and schemas: ``` GRANT CREATE TABLE ON DATABASE:: to stitch; GRANT CREATE SCHEMA ON DATABASE:: to stitch; ``` `GRANT CREATE TABLE ON DATABASE:: to stitch; GRANT CREATE SCHEMA ON DATABASE:: to stitch;`GRANTGRANT CREATECREATE TABLETABLE ONON DATABASEDATABASE::::<<DATABASE\_NAMEDATABASE\_NAME>> toto stitchstitch;; GRANTGRANT CREATECREATE SCHEMASCHEMA ONON DATABASEDATABASE::::<<DATABASE\_NAMEDATABASE\_NAME>> toto stitchstitch;; In the table below are the database user privileges Stitch requires to connect to and load data into Microsoft SQL Server. | **Privilege name** | **Reason for requirement** | | --- | --- | | [CONTROL](https://docs.microsoft.com/en-us/sql/relational-databases/security/permissions-database-engine?view=sql-server-ver15) | 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. | | [CREATE TABLE](https://docs.microsoft.com/en-us/sql/relational-databases/security/permissions-database-engine?view=sql-server-ver15) | Required to create tables in the database. | | [CREATE SCHEMA](https://docs.microsoft.com/en-us/sql/relational-databases/security/permissions-database-engine?view=sql-server-ver15) | Required to create schemas in the database. | ## Step 4: Connect Stitch To complete the setup, you need to enter your Microsoft SQL Server connection details into the Destination Settings page in Stitch. ### Step 4.1: Enter connection details into Stitch 1. If you aren’t signed into your Stitch account, [sign in now](https://app.stitchdata.com/). 2. Click the **Destination tab**. 3. Locate and click the **Microsoft SQL Server** 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 (endpoint) used by the Microsoft SQL Server instance. For example: This could be a network address such as `192.68.0.1`, or a server endpoint like `dbname.hosting-provider.com`. * **Port**: Enter the port used by the Microsoft SQL Server instance. The default is `1433`. * **Username**: Enter the Stitch Microsoft SQL Server database user’s username. * **Password**: Enter the password for the Stitch Microsoft SQL Server database user. * *\*\**: Enter the name of the Microsoft SQL Server database you want to connect to Stitch. ### Step 4.2: Define SSH connection details Information note **Note**: Skip this step if you’re not using SSH to connect to Stitch. If you’re using an SSH tunnel to connect your Microsoft SQL Server database to Stitch, you’ll also need to define the SSH settings. Refer to the [Setting up an SSH Tunnel for a database connection guide](https://help.qlik.com/en-US/stitch/security/data-encryption/setting-up-ssh-tunnel-for-database-connection) 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.3: Define SSL connection details Information note SSL can only be used with versions of Microsoft SQL Server that support TSL 1.2. Check which versions support it in [Microsoft’s documentation](https://support.microsoft.com/en-us/topic/kb3135244-tls-1-2-support-for-microsoft-sql-server-e4472ef8-90a9-13c1-e4d8-44aad198cdbe). If your Microsoft SQL Server instance is not hosted on RDS or Azure, you will not have the option to submit your own SSL certificate. Check the **Connect using SSL** checkbox. **Note:** The database must support and allow SSL connections for this setting to work correctly. ### Step 4.4: Save the destination When finished, click **Check and Save**. Stitch will perform a connection test to the Microsoft SQL Server database; if successful, a *Success!* message will display at the top of the screen. **Note**: This test may take a few minutes to complete.