Use SQL server as a source or destination
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.
A (demo) subscription of DataBridge. To sign-up, follow this url: https://my-databridge.infobridgeuniverse.com/Account/SignUp
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 |
|
|---|---|
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 |
|
|---|---|
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 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:
|
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 |
|
|---|---|
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