Documentation Index

Fetch the complete documentation index at: https://docs.dataddo.com/llms.txt

Use this file to discover all available pages before exploring further.

Microsoft SQL Server as a Source

  • 6 minute(s) read
Prev Next

Microsoft SQL Server (MSSQL) is a relational database management system developed by Microsoft.

Authorize Connection to Microsoft SQL Server

Before you can create a data source, authorize Dataddo to connect to your Microsoft SQL Server database.

Prerequisites

  • A running Microsoft SQL Server instance reachable from the internet via a public IP or hostname, or reachable through an SSH tunnel (see below).
  • A database user with read access to the tables you want to extract.
  • Your firewall configured to allow Dataddo's IP addresses. See Network ACL.

Create the authorizer

  1. In Dataddo, go to Authorizers and click Authorize New Service.
  2. Select MS SQL Server.
  3. Fill in the connection details:
Field Details
Label A name for this authorizer in Dataddo.
Server IP or Hostname Public IP or Hostname of your MS SQL Server.
Instance Name Use only if the Server hosts a multiple named instances of SQL Server. Leave empty otherwise.
Database Name of the database you will use for writing or reading the data.
Username Username for authentication.
Password Password for authentication.
Port Port to connect to SQL Server. The default value is 1433.
TLS/SSL Settings Keep the value on PREFERRED, this will ensure using the SSL connection when available. If you are using a self-signed certificate, please use SKIP VERIFY
  1. (Optional) To reach a database that is not exposed to the internet, enable Use SSH tunnel and select a tunnel (see Connect via SSH tunnel below).
  2. Click Save. Dataddo validates the connection before the authorizer is created.

Connect via SSH tunnel

If your database is not exposed to the public internet, connect through an SSH bastion (jump) server.

  1. Go to Settings > Security, open the SSH Tunnels tab, and click Create SSH Tunnel.
  2. Fill in the tunnel details:
    • Label - a name for the tunnel.
    • SSH Server IP or Hostname - the public IP or hostname of your bastion server; allow Dataddo's IPs in its firewall.
    • SSH Server Port - usually 22.
    • SSH Server Username.
    • SSH Server Password - or leave it empty and attach a Certificate for key-based authentication (recommended).
  3. Save the tunnel, then select it in the Use SSH tunnel field of the authorizer.

For more detail, see Secure Data Transfer with SSH Tunneling.

Capabilities

Microsoft SQL Server supports the data extraction methods below. Each method differs in which row changes it captures and how much load it places on the source. Choose the one that matches your table and use case; for a full explanation and setup detail, see Database Replication.

Method New rows Updated rows Deleted rows Details
Table Replication by Timestamp Yes Yes No Tracks a datetime column (such as updated_at) and re-extracts a row whenever that timestamp advances. Low to medium load on the source.
Table Replication by Row Sequence Yes No No Tracks a continuously increasing numeric column (such as an auto-increment ID) to capture inserts only. Low load on the source, and supports an optional per-run row limit for very large tables.
Log-based Replication (CDC) Yes Yes Yes Reads committed changes from the database transaction log via Change Data Capture. The only method that also captures deletes, with very low load on the source. Requires setup on the database (see below).
Custom SQL Query Depends on query Depends on query Depends on query Runs your own SQL query, so you control exactly which rows and columns are returned, including joins, filters, and aggregation.

How the Incremental Methods Track Changes

The two incremental methods track progress differently:

  • Table Replication by Timestamp extracts the rows whose Change Tracking Column falls inside the source's relative date range (for example "last 24 hours"), and that window moves forward with the current date. A row re-enters the window whenever its timestamp is refreshed, which is how updates are captured. Two practical consequences: the tracking column must be set on insert and refreshed on every update (such as updated_at), and the window must be at least as wide as the gap between two runs, otherwise rows changed in between are missed.
  • Table Replication by Row Sequence remembers the highest value of the Sequence Tracking Column(s) extracted so far, and each run continues from that value. The first run starts from the beginning of the table. The optional Row limit per run caps one run's size, so the initial load of a very large table can be split across several scheduled runs.
  • In both methods, your optional WHERE clause is combined with the automatic tracking filter, so do not repeat the time or sequence condition in it.

Log-based Replication (CDC)

Log-based replication uses Change Data Capture to read committed changes - inserts, updates, and deletes - directly from the Microsoft SQL Server transaction log instead of querying the tables. It is the only method that detects deleted rows and puts very low load on the source, but it needs extra configuration on the database. For the full walkthrough, see Database Replication.

Before using it, make sure that:

  • SQL Server Change Data Capture (CDC) is enabled on the database and on every table you replicate.
  • Each replicated table has a primary key or unique column so that updates and deletes can be matched to specific rows.
  • The connecting database user has permission to read the change stream.

How to Create a Microsoft SQL Server Data Source

  1. In Dataddo, go to Sources > Create Source and select the Microsoft SQL Server connector.
  2. Choose the Authorizer you created above.
  3. Select the extraction method that fits your use case (see the Capabilities table above).
  4. Fill in the required fields for the selected method, such as the schema, table, and columns.
  5. For the incremental methods, select the tracking column: the Change Tracking Column (a datetime column such as updated_at) for Table Replication by Timestamp, or the Sequence Tracking Column(s) (a strictly increasing numeric column such as an auto-increment ID) for Table Replication by Row Sequence.
  6. (Optional) Add a WHERE clause to filter the extracted rows. Do not repeat the time or sequence condition in it; Dataddo adds the tracking filter automatically.
  7. Click Test Data to preview the result, then click Save.

Troubleshooting

A text column contains binary data. The run reports proto: invalid UTF-8 in string: "\x02\x00\x00\x00...".

  • Cause: a VARCHAR or NVARCHAR column holds binary or encrypted bytes, which Dataddo's serializer rejects.
  • Fix: deselect the column, or convert it in a custom query, for example CONVERT(VARBINARY(MAX), col, 1) AS col.

A custom query references a missing object. The run reports mssql: Invalid object name 'schema.table_name'.

  • Cause: the query points to a table or view that does not exist or is not visible to the user (wrong schema, missing permission, or renamed object).
  • Fix: verify the fully qualified name in SSMS and confirm the user has SELECT on the schema.

The host is unreachable. The run reports unable to open tcp connection with host 'hostname:1433': dial tcp X.X.X.X:1433: i/o timeout.

  • Cause: a firewall or Azure network security group is blocking Dataddo on port 1433.
  • Fix: allowlist Dataddo's IP addresses in the SQL Server firewall rules (Azure Portal, SQL Server, Networking, Firewall rules). See Network ACL.

A column is missing or the source fails on an unsupported data type. Cast the column to a supported type using a Custom SQL Query, for example SELECT CAST(my_column AS VARCHAR) AS my_column FROM my_table.

The data preview is empty. This is usually caused by one of the following:

  • the selected date range contains no data;
  • the database user does not have permission to read the selected table;
  • the selected table, columns, or tracking column are no longer valid;
  • an incompatible combination of options was selected.

Related Articles