SQL Server Destination Setup Guide

Follow our setup guide to connect SQL Server database as a destination to Fivetran.

---

Prerequisites

To connect SQL Server to Fivetran, you need the following:

• SQL Server Version 2012 or above
• IP (e.g., 1.2.3.4) or host (your.server.com)
• Port (usually 1433)
• Access to your SQL Server through for your database's region
• A Fivetran-specific SQL Server user with WRITE-level permissions
• Fivetran role with the permissions

IMPORTANT: We only support the editions of SQL Server.

---

Setup instructions

Choose connection method

Decide whether to connect to your SQL Server database directly, using an SSH tunnel, or using private link. For more information, see our .

Connect directly

If you connect directly, you must create a rule in a security group that allows Fivetran access to your database instance and port.

Configure your firewall and/or other access control systems to allow incoming connections to your host and port from for your database's region.

Connect using an SSH tunnel

If you connect using an SSH tunnel, Fivetran connects to a separate server in your network that provides an SSH tunnel to your SQL Server database. You must then configure your tunnel server's security group to allow Fivetran access and configure the instance's security to allow access from the tunnel.

You must connect through SSH if your database is contained within an inaccessible subnet.

To connect using SSH, do the following:

1. In the , select the Connect via an SSH tunnel option.

2. Copy Fivetran's public SSH key.

3. Add the public key to the authorized_keys file of your SSH server. The key must be all on one line, so make sure that you don't introduce any line breaks when cutting and pasting.

Connect using AWS PrivateLink Beta

IMPORTANT: You must have a Business Critical plan to use AWS PrivateLink.

AWS PrivateLink allows VPCs and AWS-hosted or on-premises services to communicate with one another without exposing traffic to the public internet. PrivateLink is the most secure connection method. Learn more in .

Follow to configure PrivateLink for your destination.

Connect using Azure Private Link Beta

IMPORTANT: You must have a Business Critical plan to use Azure Private Link.

Azure Private Link allows Virtual Networks (VNets) and Azure-hosted or on-premises services to communicate with one another without exposing traffic to the public internet. Learn more in Microsoft's .

Follow to configure Private Link for your destination.

Connect using Google Cloud Private Service Connect Private Preview

Google Cloud Private Service Connect allows VPCs and Google-hosted or on-premises services to communicate with one another without exposing traffic to the public internet. Learn more in Google Cloud's .

Follow to configure Private Link for your destination.

Enable TCP/IP

Verify that your SQL Server database is configured to allow TCP/IP connections. To enable the TCP/IP protocol for your database instance:

1. Open SQL Server Configuration Manager.

   

2. In the navigation pane, click SQL Server Network Configuration, and then click Protocols for MyInstanceName.

   NOTE: If you specified the default instance during installation, the instance name will be MSSQLSERVER.

   

3. In the right pane, verify that the TCP/IP protocol is Enabled. If the status is Disabled, right-click TCP/IP, and then click Enable.

4. Right-click TCP/IP and select Properties.

5. In the IP Addresses tab, go to the IPAll section.

6. In the TCP Port field enter 1433, and then click Apply.

   

7. On the warning dialog box, click OK, and then on the TCP/IP Properties dialog box, click OK.

   

8. In the navigation pane, expand SQL Native Client Configuration, and then click Client Protocols.

9. In the right pane, verify that TCP/IP is Enabled. If TCP/IP is Disabled, right-click TCP/IP, and then click Enable.

   

10. Right-click TCP/IP and select Properties. Verify that the Default Port field is set as 1433 and the Enabled field is set as Yes.

    

11. Click OK to exit the TCP/IP Properties dialog box.

12. In the navigation pane, click SQL Server Services.

13. In the right pane, right-click SQL Server (MyInstanceName), and then click Restart.

Verify SSL encryption

1. Open SQL Server Configuration Manager.
2. In the navigation pane, expand SQL Server Network configuration.
3. Select Protocols for MyInstanceName Properties.
4. In the right pane, go to the Certificate tab and make sure that the encryption certificate is enabled.

See , if the certificate is not enabled.

NOTE: The certificate must be in PFX (.pfx) format. If not, the certificate will not be available in the Microsoft Management Console's All Tasks > Manage Private Keys menu.

Create Fivetran user

Connect to your SQL Server database as an Admin user and execute the following SQL commands to create a user for Fivetran. Choose a memorable username (for example, fivetran). Replace <database> with the name of your database and <password> with a password of your choice:

USE [<database>];
CREATE LOGIN fivetran WITH PASSWORD = '<password>';
CREATE USER fivetran FOR LOGIN fivetran;
content_copy

Grant permissions

Execute the following commands to grant the fivetran user CREATE permission for the database you would like Fivetran to sync:

GRANT CREATE SCHEMA TO fivetran;
GRANT CREATE TABLE TO fivetran;
content_copy

Complete Fivetran configuration

1. Log in to your Fivetran account.
2. Go to the , then click + Add Destination.
3. On the Add destination to your account page, enter a Destination name of your choice.
4. Click Add.
5. Select SQL Server as the destination type.
6. In the destination setup form, enter the Host name or the IP address of the database server.
7. Enter the Port number. For example, 1433.
8. Enter the User name you created in .
9. Enter your Password.
10. Enter the Database name you want to replicate to.
11. Choose your Connection method:
    • Connect directly
    • Connect via an SSH
    • Connect via PrivateLink

    NOTE: The Connect via PrivateLink option is available only for Business Critical accounts.

12. If you choose Connect via an SSH tunnel in the Connection method drop-down menu, enter the following details:
    • SSH Host
    • SSH Port
    • SSH User
    • (Optional) Enable the Require TLS through tunnel toggle if you want to use TLS.
13. Choose the Data processing location. Depending on the plan you are on and your selected cloud service provider, you may also need to choose a Cloud service provider and cloud region as described in our .

    IMPORTANT: If you are using , , or , select the corresponding Cloud service provider.

14. Choose your Time zone.
15. (Optional for Business Critical accounts) To enable , set the Use Failover toggle to ON, and then select your Failover Location and Failover Region. Make note of the IP addresses of the secondary region and safelist these addresses in your firewall.
16. Click Save & Test.

Fivetran the SQL Server destination connection. On successful completion of the setup tests, you can sync your data using Fivetran connectors to the SQL Server destination.

Setup tests

Fivetran performs the following SQL Server connection tests:

• The SSH Tunnel Test validates the SSH tunnel details you provided in the setup form and then checks the connectivity to the instance using the SSH Tunnel if you are connecting using an SSH tunnel.

• The Database Host Connection Test validates the database credentials you provided in the setup form. The test verifies that the host is not private and then checks the connectivity to the host.

• The Database Certificate Validation Test generates a pop-up window where you must choose which certificate you want Fivetran to use. The test then validates that certificate and checks that we can connect to your database using TLS. We skip this test if you aren't connecting directly.

• The SQL Server Connection Test checks if we can access your database.

• The Permission Test checks that we have the correct permissions to create schemas and tables in your database.

• The SQL Server Type Test validates the SQL Server service type. The test checks if your SQL Server implementation matches the destination type. For example, this test will fail if you try to set up a generic SQL Server destination using an Azure SQL database.

  NOTE: The tests may take a couple of minutes to finish running.

---

Related articles