SQL Server

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

Prerequisites

To connect your SQL Server as a destination database in ETLrobot, you need:

• SQL Server version 2012 - 2019 (2016+ preferred to support JSON data types).
• Your database's IP address (e.g., 1.2.3.4) or domain (your.server.com).
• Your database's port (usually 1433).
• Database privileges that allow you to create users and grant privileges. This is required to create a database user and role for ETLrobot.
• It is recommended to set the database timezone to UTC.

---

Common supported implementations:

• Generic SQL Server
• Amazon RDS SQL Server
• Google Cloud SQL Server
• Azure SQL Server

Important

We only support the Enterprise, Standard, and Web editions of Microsoft SQL Server.

---

Setup instructions

1. Database setup

   1. Connect to your SQL Server instance as an admin user using your SQL client.

   2. Navigate to the master database and create a database to use for this data warehouse if you don't have one already.
      Possible name: dw (for data warehouse)

      USE master;
      create database <database_name>;
      

   3. Execute the following command to create a SQL Server login and user. Choose a memorable username (for example, etlrobot). Replace <password> with a strong password of your choice.

      SyntaxInfo

      USE <database_name>;
      CREATE LOGIN etlrobot_login WITH PASSWORD = '<password>';
      CREATE USER etlrobot FOR LOGIN etlrobot_login;
      

      • CREATE LOGIN: grants entry to the server instance.
      • CREATE USER: grants entry to a specific database.
      • Logins are created at the server level, while users are created at the database level.

   4. Execute the following commands to grant the etlrobot user the following privileges.

      SyntaxInfo

      GRANT CREATE SCHEMA TO etlrobot;
      GRANT CREATE TABLE TO etlrobot;
      GRANT CONTROL ON DATABASE::<database_name> to etlrobot;
      

      • CREATE SCHEMA: Required to create schemas in the database.
      • CREATE TABLE: Required to create tables in the database.
      • CONTROL: This ensures that ETLrobot 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.

2. Choose your connection method

   Decide whether to connect to your PostgreSQL database directly or via an SSH tunnel.

   Connect directly

   If your database is publicly accessible you may connect directly. You must whitelist ETLrobot's IPs by creating a rule in a security group that allows ETLrobot access to your database port and instance.

   Configure your firewall and/or other access control systems to allow:

   1. incoming connections to your host and port (usually 1433) from ETLrobot's IPs for your database's region
   2. outgoing connections from all ports (1024 to 65535) to ETLrobot's IPs
   Connect via an SSH tunnel

   If your database is not publicly accessible you will need to connect using an SSH tunnel.

   SSH requires a publicly accessible SSH server to act as an intermediary between ETLrobot and your private database. The SSH server will forward traffic from ETLrobot through a secured encrypted tunnel to the private database.

   To connect using SSH, do the following:

   1. Select the SSH option.
   2. Copy ETLrobot's public SSH key.
   3. Add the public key to the authorized_keys (usually $user/.ssh/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.

   Click here detailed instructions on connecting via an SSH tunnel.

3. Complete the destination setup

   Be sure to fill out all the required fields.

   ETLrobot tests and validates your SQL Server destination connection and database setup. If successful, the sync to your database will automatically start.