SQL Server API Configuration
Request
POST https://api.fivetran.com/v1/connections
{ "group_id": "group_id", "service": "sql_server", "trust_certificates": true, "trust_fingerprints": true, "run_setup_tests": true, "paused": false, "pause_after_trial": false, "sync_frequency": 1440, "data_delay_sensitivity": "NORMAL", "data_delay_threshold": 0, "daily_sync_time": "14:00", "schedule_type": "auto", "connect_card_config": { "redirect_uri": "https://your.site/path", "hide_setup_guide": true }, "proxy_agent_id": "proxy_agent_id", "private_link_id": "private_link_id", "networking_method": "Directly", "hybrid_deployment_agent_id": "hybrid_deployment_agent_id", "destination_configuration": { "virtual_warehouse": "virtual_warehouse" }, "destination_schema_names": "SOURCE_NAMING", "config": { "tde_certificate_path": "c:\\certificates\\tde_cert.cer", "tde_private_key_path": "c:\\certificates\\tde_cert.pvk", "update_method": "BINARY_LOG_READER | NATIVE_UPDATE | TELEPORT", "external_id": "external_id", "backup_storage_type": "AZURE | ALTERNATE_LOCATION | AWS", "backup_log_format": "The format backup_%d_%Y%M%D_%h%m%s.log translates to backup__YYYYMMDD_HHMMSS.log", "connection_string": "BlobEndpoint=https://abc123.blob.core.windows.net/;TableEndpoint=https://abc123.table.core.windows.net/;SharedAccessSignature=xxxxxxxxx", "tunnel_port": 22, "database": "sqldb", "password": "test_password", "binary_log_truncater": "FIVETRAN", "tde_password": "string", "host": "sqlinstance.mycompany.com", "backup_log_path": "C:\\BackupLogs", "binary_log_type": "BACKUP | ONLINE", "tde_private_key_backup": "string", "connection_type": "Directly | PrivateLink | SshTunnel | ProxyAgent", "enable_tde": true, "tde_certificate_backup": "string", "always_encrypted": true, "tunnel_user": "fivetran", "bucket": "bucket_name", "container_name": "container_name", "role_arn": "arn:aws:iam::123456789012:role/rolename", "port": 1433, "tunnel_host": "XXX.XXX.XXX.XXX", "user": "test_user", "schema_prefix": "prefix" } }
Config parameters
| Name | Description |
|---|---|
tde_certificate_path | Certificate file path used to protect a database encryption key. Used when decrypting online logs |
tde_private_key_path | Private key file path associated with the TDE certificate. Used when decrypting online logs. |
update_method | (Optional) The incremental update method the connector will use. The possible values are "TELEPORT" or "NATIVE_UPDATE". The type defaults to "NATIVE_UPDATE" if the value is set to null or not specified. |
external_id | The external ID to be used when assuming the IAM role. |
backup_storage_type | Location of transaction log backup files when using BACKUP binary log type. Possible values: "AZURE", "ALTERNATE_LOCATION", "AWS". |
backup_log_format | Filename pattern for transaction log backup files. |
connection_string | The Azure Blob Storage container connection string. |
tunnel_port | SSH port, specify only to connect via an SSH tunnel. |
database | The database name. |
password | The user's password. |
binary_log_truncater | Determine who is responsible for truncating the online transaction log. Possible values: "FIVETRAN", "USER_MANAGED", "SQL_SERVER_AGENT". Default value is "FIVETRAN". |
tde_password | Password of the TDE private key |
host | DB instance host or IP address. |
backup_log_path | Full path to the local directory containing transaction log backup files. Required if backup_storage_type is ALTERNATE_LOCATION |
binary_log_type | Source of transaction log data when using Binary Log Reader. Possible values: "ONLINE", "BACKUP". Default value is "ONLINE". |
tde_private_key_backup | Private key file associated with the TDE certificate. Used when decrypting backup logs. |
connection_type | Possible values: Directly, PrivateLink, SshTunnel. SshTunnel is used as a value if this parameter is omitted in the request and any of the following parameter's values is specified: tunnel_host, tunnel_port, tunnel_user. Otherwise, Directly is used as a value if the parameter is omitted. |
enable_tde | Use transparent data encryption (TDE) |
tde_certificate_backup | Certificate file used to protect a database encryption key. Used when decrypting backup logs. |
always_encrypted | Require TLS. |
tunnel_user | SSH user, specify only to connect via an SSH tunnel. |
bucket | The name of the S3 bucket. |
container_name | The name of the Azure Blob Storage container. |
role_arn | The ARN of the IAM role that has access to the S3 bucket. |
port | The port number. |
tunnel_host | SSH host, specify only to connect via an SSH tunnel (do not use a load balancer). |
user | The user name. For Azure Databases, the format must be user@domain. |
schema_prefix (required) | Destination schema prefix. Prefix for each replicated schema. For example with prefix 'x', source schemas 'foo' and 'bar' get replicated as 'x_foo' and 'x_bar'. The prefix is permanent and cannot be changed after connection creation |
Authorization
There are two ways to authorize this connector type:
- default: By specifying the values for the
host,port,password,useranddatabaseparameters in the request.POST https://api.fivetran.com/v1/connections{ "service": "sql_server", "group_id": "group_id", "config": { "host": "sqlinstance.mycompany.com", "port": 1433, "password": "test_password", "user": "test_user", "database": "sqldb" } } By using the Connect Card or the Fivetran dashboard.