SQL Server RDS Setup Guide

Here's instructions about how to setup SQL Server RDS as a data warehouse:

Enable access

Decide whether to connect to your warehouse directly or connect using an SSH tunnel.

• If you decide to connect directly to your database, you will need to create a security rule to allow access. This is the simplest and most secure method.
• If you decide to connect using an SSH tunnel, Fivetran will connect to a separate server in your network which provides an SSH tunnel to your database. This method is necessary if your database is in an inaccessible subnet. To connect using an SSH tunnel, follow these instructions.

The Fivetran data processing servers will need access to your database server. You will need to configure your VPC Security Groups and Network ACLs (Access Control Lists) to allow incoming connections to your SQL server host and port (usually 1433) from your SSH tunnel server's IP address.

Configure security group

These instructions assume that your instance is in a VPC.

Expand the view on the instance by clicking the little black triangle and click the Configuration Details tab:

A panel of details for your read replica will appear.

Check that Publicly Accessible reads Yes.

Write down the read instance's port number (you will need this later),

then click the link to its Security Group:

In the security group panel, select the "Inbound" tab:

Click edit:

Click "Add Rule":

A new Custom TCP Rule gets created at the bottom of the list with a blank space for a Port Range and a Source IP address. For the Port, enter your instance's port number that you wrote down earlier (usually 1433).

For the Source, enter a Custom IP of {your-ssh-tunnel-server-ip-address}/32:

Click "Save":

Configure Network ACLs

Return to the RDS Dashboard and expand the view on the instance:

Click the link to the instance's VPC:

Select the VPC:

In the "Summary" tab, click the "Network ACL" link:

You will see tabs for Inbound Rules and Outbound Rules. We will need to edit both.

Edit inbound rules

Select "Inbound Rules":

If you have a default VPC that was automatically created by AWS, then the settings already allow all incoming traffic as indicated by the Source value 0.0.0.0/0 and the fact that the ALLOW entry is listed above the DENY entry:

If your inbound rules don't include an ALL - 0.0.0.0/0 - ALLOW entry, edit the rules to allow {your-ssh-tunnel-server-ip-address}/32 to access the port number of your read replica (usually 1433).

Help on ACL configuration can be in Amazon's documentation.

Edit outbound rules

Select "Outbound Rules":

If your outbound rules don't include an ALL - 0.0.0.0/0 - ALLOW entry, edit the rules to allow outbound traffic to all ports 1024-65535 for destination {your-ssh-tunnel-ip-address}/32

Enter host and port in wizard

• Host: enter url from Endpoint in RDS dashboard, without the port

• Port: enter port from Endpoint in RDS dashboard (1433 is the default)

---

Create a Fivetran user

Connect to your SQL Server database as an admin user and execute the following SQL commands to create a user for Fivetran:

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

replacing <database> with the name of your database and <password> with a password of your choice.

Grant permissions to user

Once user fivetran is created, grant it CREATE permission for the database you would like Fivetran to sync.

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

Enter user, password and database in wizard

• User: fivetran, or whichever username you created

• Password: the password you set

• Database: the database you want to replicate from