Follow our setup guide to connect your SQL Server database as a destination to ETLrobot.
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.,
184.108.40.206) or domain (
- Your database's port (usually
- 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:¶
We only support the Enterprise, Standard, and Web editions of Microsoft SQL Server.
Connect to your SQL Server instance as an admin user using your SQL client.
Navigate to the
masterdatabase and create a database to use for this data warehouse if you don't have one already.
dw(for data warehouse)
USE master; create database <database_name>;
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.
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.
Execute the following commands to grant the
etlrobotuser the following privileges.
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.
Choose your connection method¶
Decide whether to connect to your PostgreSQL database directly or via an SSH tunnel.
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:
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:
- Select the SSH option.
- Copy ETLrobot's public SSH key.
- 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.
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.