Use SQL server as a source or destination

Owned by Remco Visser
Last updated: Feb 01, 2024
4 min read

Overview

With the SQL Connector it is possible to use SQL Server to import data into SuperOffice from a SQL Database or export data from SuperOffice to a SQL Database.

Specific features, functionalities, and limitations are dependent on the type of subscription plan. For a comprehensive overview, please refer to the following subscription overview page.

Supported SQL versions

  • SQL Server 2016 (aka SQL Server 12) and newer

  • Azure SQL Database

Security

A user is needed with sufficient rights to connect to the SQL Server instance, access the database and to create, edit and delete tables. The user must have rights to insert, update and delete records in the table.
Besides the SQL roles db_datareader and db_datawriter, the db_ddladmin role is also needed, as the db_datawriter role is not allowed to create/delete tables. The db_ddladmin role can create/delete tables but cannot do Select queries.

We don’t advise to use any users to connect with more rights than needed.

The supported authentication methods are:

  • For SQL Server: SQL Server Authentication

  • For Azure SQL Database: SQL Server Authentication or Azure Active Directory

We advise, for security reasons, to only whitelist the IP addresses that DataBridge uses. These are listed here.

Additional requirements

A database needs to be set up before you connect DataBridge to your SQL instance.

Please note that DataBridge will create, delete and edit tables and columns. This can also happen if DataBridge did not create the table or column. Our advice is to use a separate database for use with DataBridge. If you use an existing database, you must make sure that the user that you use to connect from DataBridge does not have any rights to do harm to any existing element in that database.

Prerequisites

The following prerequisites are required before you follow the steps below.

  1. A (demo) subscription of DataBridge. To sign-up, follow this url: https://my-databridge.infobridgeuniverse.com/Account/SignUp

  2. A SuperOffice CRM login name with administrator rights

Create profile

Location and Destination

Select “Microsoft Azure SQL & Microsoft SQL Server” as Location when importing or as Destination when exporting.

Configure profile

CONNECTION

 

CONNECTION

 

Authentication mode

Select the authentication type for connecting to SQL Server (SQL Server Authentication or Active Directory)

Server name

Enter the name of your database engine and port number, seperated by a comma. The server name can be an ip-address or DNS name and port number is only necessary if it is different than the default port (1433)

Database name

Enter the name of the database

Username

Enter username

Password

Enter password

Configure Source

When exporting to SQL

When exporting the “configure source” step is the same as default.

When importing from SQL

CONFIGURE SOURCE

 

CONFIGURE SOURCE

 

Source table

Select the table or view you want to import data from

Delete Imported rows

When set to “Yes”, DataBridge will delete the records in the source table after importing. This is only possible when importing from a table that has a primary key and does not apply to views.

Destination Settings

When exporting to SQL

TABLE

 

TABLE

 

Table Schema

The schema name of the target table

Table name

The name of the target table. If this table does not exist, it will be created

Table action

Wat should be done if the target table already exists. There are 3 options:

  1. Keep the table and all records

  2. Keep the table but remove all records

  3. Drop the table and recreate it from scratch

Duplicate match

Select the column that will be used for duplicate checking

Duplicate action

Select what action that has to done when duplicates are found

Skip if not found

If set to “Yes“, only the records that already exist will be processed. No new records will be created

COLUMNS

 

COLUMNS

 

Use display name as column name

If set to “Yes”, the friendly display name will be added between parentheses to the column name

Remove unmatched columns

If set to “Yes”, columns in the target table that do not exist in the source data will be removed

When importing from SQL

When importing the “configure source” step is the same as default.

Profile Settings

The jobs can run scheduled or triggered