Advanced Analytica Advanced Analytica: IBOM
← All connectors
MS SQL Server logo

Destination connector

MS SQL Server

Send governed data into MS SQL Server with a clear, practical setup guide.

Version 2.2.24 certified generally available

MS SQL Server

Set up the MS SQL Server destination connector to write data to a Microsoft SQL Server or Azure SQL Database instance.

Supported sync modes

Sync modeSupported?
Full Refresh - OverwriteYes
Full Refresh - AppendYes
Full Refresh - Overwrite + DedupedYes
Incremental Sync - AppendYes
Incremental Sync - Append + DedupedYes

Output schema

Each stream is written to its own SQL Server table. Each table contains these metadata columns:

  • _airbyte_raw_id: A random UUID assigned to each incoming record. Its SQL Server column type is VARCHAR(MAX).
  • _airbyte_extracted_at: A timestamp showing when the event was pulled from the data source. Its SQL Server column type is BIGINT.
  • _airbyte_meta: Additional information about the record. Its SQL Server column type is TEXT.
  • _airbyte_generation_id: Incremented each time a refresh runs. Its SQL Server column type is TEXT.

See here for more information about these fields.

Get started

Setup guide

  • MS SQL Server: Azure SQL Database, SQL Server 2016 or greater

Network access

Make sure Airbyte can access your SQL Server database. For a database within a VPC, allow access from the IP exposing Airbyte. If you’re using Airbyte Cloud, add Airbyte’s IP addresses to your allowlist.

Permissions

Configure a SQL Server user that can create tables and write rows. Creating a dedicated Airbyte user is highly recommended. Grant the user ALTER permissions on the target schema. These permissions let Airbyte create and modify tables as needed.

Target database

Choose an existing database or create one to store data synced from Airbyte.

Configuration

You’ll need this information to configure the MSSQL destination:

  • Host
    • The MSSQL database host name.
  • Port
    • The MSSQL database port.
  • Database Name
    • The MSSQL database name.
  • Default Schema
    • The default schema for tables when the source doesn’t specify a namespace. The usual value is “dbo”.
  • Username
    • The username used to access the database.
  • Password
    • The password associated with this username.
  • JDBC URL Parameters
    • Additional properties passed to the database’s JDBC URL string. Format them as ‘key=value’ pairs separated by ’&’.
    • Example: key1=value1&key2=value2&key3=value3.
  • SSL Method
    • The SSL configuration supports three modes: Unencrypted, Encrypted (trust server certificate), and Encrypted (verify certificate).
      • Unencrypted: Don’t use SSL encryption for the database connection.
      • Encrypted (trust server certificate): Use SSL encryption without verifying the server’s certificate. This supports self-signed certificates in testing but shouldn’t be used in production.
      • Encrypted (verify certificate): Use the server’s SSL certificate after standard certificate verification.
        • Host Name In Certificate (optional): Set an expected name during certificate verification for added security. Verification fails if the certificate’s host name doesn’t match.
  • Load Type
    • The data load type supports two modes: Insert or Bulk.
      • Insert: Uses SQL INSERT statements to load data into the destination table.
      • Bulk: Uses Azure Blob Storage and the BULK INSERT command to load data into the destination table. This option requires additional configuration:

Set up MSSQL with Azure Blob Storage (Bulk Upload)

Set up and use the Microsoft SQL Server (MSSQL) connector with the Azure Blob Storage Bulk Upload feature. Stage data in Azure Blob Storage and use BULK INSERT. This approach can significantly improve ingestion speed and reduce network overhead for large or frequent data loads.

Why use Azure Blob Storage Bulk Upload?

Row-by-row MSSQL inserts can become slow and resource-intensive with high data volumes or frequent syncs. Staging files in Azure Blob Storage lets you:

  1. Aggregate Data into Bulk Files: Write data to Blob Storage in batches, reducing overhead.
  2. Perform Bulk Ingestion: Let MSSQL use BULK INSERT to load these files directly. This is typically faster than conventional row-by-row inserts.
Prerequisites
  1. A Microsoft SQL Server Instance
    • Compatible with on-premises SQL Server or Azure SQL Database.
  2. Azure Blob Storage Account
    • A storage account and container (e.g., bulk-staging) for your data files.
  3. Permissions
    • Blob Storage: Permission to create, read and delete objects within the designated container.
    • MSSQL: Permission to create or modify tables and execute BULK INSERT.
Setup guide

Follow these steps to configure MSSQL with Azure Blob Storage for bulk uploads.

1. Set up Azure Blob Storage
  1. Create a Storage Account & Container
    • In the Azure Portal, create or reuse a Storage Account.
    • Within that account, create a container (e.g., bulk-staging) to stage your data files.
  2. Establish Access Credentials
    • Use a Shared Access Signature (SAS) scoped to your container.
    • Make sure the SAS token or role assignments include permissions such as Read, Write, Delete, and List.
2. Configure MSSQL

See the official Microsoft documentation for more details. Here’s a simplified overview:

  1. (Optional) Create a Master Encryption Key If your environment requires a master key to store credentials securely, create one:

    CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<your_password>';
  2. Create a Database Scoped Credential Configure a credential that gives MSSQL access to your Blob Storage through the SAS token:

    CREATE DATABASE SCOPED CREDENTIAL <credential_name>
    WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
         SECRET = '<your_sas_token>';
  3. Create an External Data Source Point MSSQL to your Blob container using the credential:

    CREATE EXTERNAL DATA SOURCE <data_source_name>
    WITH (
        TYPE = BLOB_STORAGE,
        LOCATION = 'https://<storage_account>.blob.core.windows.net/<container_name>',
        CREDENTIAL = <credential_name>
    );

    You’ll reference <data_source_name> when configuring the connector.

3. Configure the connector

You’ll need to provide:

  1. MSSQL Connection Details
    • The server hostname/IP, port, database name and authentication details (username/password).
  2. Bulk Load Data Source
    • The external data source name you created (e.g., <data_source_name>).
  3. Azure Storage Account & Container
    • The storage account and container names.
  4. SAS Token
    • The token that grants access to Blob Storage.

See the Getting Started: Configuration section of this guide for more details about configuring the BULK INSERT connector.

Data type limitations

SQL Server limits certain data types. If a value falls outside a supported range, the connector nullifies it. It also records a DESTINATION_FIELD_SIZE_LIMITATION change in _airbyte_meta.

Airbyte typeSQL Server typeSupported rangeNotes
timestamp_without_timezoneDATETIME1753-01-01 00:00:00 through 9999-12-31 23:59:59.997Dates before 1753-01-01 are nullified. This is a SQL Server restriction.
timestamp_with_timezoneDATETIMEOFFSET0001-01-01 through 9999-12-31No additional connector-level validation.
integerBIGINT−9,223,372,036,854,775,808 through 9,223,372,036,854,775,807Values outside this range are nullified.
numberNUMERIC(38,8)Approximately ±10^30Values outside the representable range are nullified.

Namespace support

This destination supports namespaces. The namespace maps to a SQL Server schema.

Changelog

Expand to review
VersionDatePull RequestSubject
2.2.242026-10-2289656Upgrade to Bulk CDK 1.1.9: fix critical CVEs in Apache MINA (mina-core 2.0.31), MINA SSHD 2.20.0 and BouncyCastle 1.86; netty 4.1.139; mssql-jdbc 12.10.2.
2.2.232026-10-0187618Upgrade to Bulk CDK 1.1.1.
2.2.222026-09-0985768Report SQL Server permission-denied and string-truncation errors as configuration errors instead of system errors.
2.2.212026-09-0184990Upgrade to Bulk CDK 1.0.25.
2.2.202026-07-1081536Add connectorIPCOptions to enable speed mode
2.2.192026-06-3080269Extract shared value coercion into MSSQLValueCoercer; enable acceptance tests
2.2.182026-06-0979602Fix bulk load — schema-qualified staging tables, always serialize booleans as 0/1
2.2.172026-06-0879154Nullify timestamp-without-timezone values before 1753-01-01 instead of failing the sync
2.2.162026-04-2376946Upgrade Bulk CDK to 1.0.11 and fix _ab_cdc_deleted_at column type so the secondary index on CDC streams can be created.
2.2.152026-01-2672297Upgrade CDK to 0.2.0
2.2.142025-11-0569130Upgrade to Bulk CDK 0.1.61.
2.2.132025-09-2466684Pin to CDK artifact
2.2.122025-06-2662078Add SSH tunnel support
2.2.112025-05-3061017Integration test fixes
2.2.102025-05-2960897Internal fixes
2.2.92025-05-1960791Fix bug in detecting schema change when stream has no columns
2.2.82025-05-0859735Cleanup: Remove unused code
2.2.72025-05-0756444CDK: Internal refactor; perf improvements
2.2.62025-04-2158146Fix numeric bounds-handling
2.2.52025-04-1858140Upgrade to latest CDK
2.2.42025-04-1157563Improve BULK INSERT documentation.
2.2.32025-04-1658085Internal refactoring
2.2.22025-04-0756391Add support for Azure blob storage auth via storage account key.
2.2.12025-03-2756402Improve Azure blob storage load logic.
2.2.02025-03-2356353Bulk Load performance improvements
2.1.22025-03-2556346Internal refactor
2.1.12025-03-2456355Upgrade to airbyte/java-connector-base:2.0.1 to be M4 compatible.
2.1.02025-03-2455849Misc. bugfixes in type-handling (esp. in complex types)
2.0.52025-03-2455904Fix handling of invalid schemas (correctly JSON-serialize values)
2.0.42025-03-2055886Internal refactor
2.0.32025-03-1855811CDK: Pass DestinationStream around vs Descriptor
2.0.22025-03-1255720Restore definition ID
2.0.12025-03-1255718Fix breaking change information in metadata.yaml
2.0.02025-03-1155684Release 2.0.0
2.0.0.rc132025-03-0755252RC13: Bugfix for OOM on Bulk Load
2.0.0.rc122025-03-0554159RC12: Support For Bulk Insert Using Azure Blob Storage
2.0.0.rc112025-03-0455193RC11: Increase decimal precision
2.0.0.rc102025-02-2454648RC10: Fix index column names with hyphens
2.0.0.rc92025-02-2154197RC9: Fix index column names with invalid characters
2.0.0.rc82025-02-2054186RC8: Fix String support
2.0.0.rc72025-02-1153364RC7: Revert deletion change
2.0.0.rc62025-02-1153364RC6: Break up deletes into loop to reduce locking
2.0.0.rc52025-02-0753236RC5: Use rowlock hint
2.0.0.rc42025-02-0653192RC4: Fix config, timehandling, performance tweak
2.0.0.rc32025-02-0453174RC3: Fix metadata.yaml for publish
2.0.0.rc22025-02-0452704RC2: Performance improvement
2.0.0.rc12025-01-2452096Release candidate
1.0.32025-01-1051497Use a non root base image
1.0.22024-12-1849891Use a base image: airbyte/java-connector-base:1.0.0
1.0.12024-11-04#48134Fix supported sync modes (destination-mssql 1.x.y does not support dedup)
1.0.02024-04-11#36050Update to Dv2 Table Format and Remove normalization
0.2.02023-06-27#27781License Update: Elv2
0.1.252023-06-21#27555Reduce image size
0.1.242023-06-05#27034Internal code change for future development (install normalization packages inside connector)
0.1.232023-04-04#24604Support for destination checkpointing
0.1.222022-10-21#18275Upgrade commons-text for CVE 2022-42889
0.1.202022-07-14#14618Removed additionalProperties: false from JDBC destination connectors
0.1.192022-05-25#13054Destination MSSQL: added custom JDBC parameters support.
0.1.182022-05-17#12820Improved ‘check’ operation performance
0.1.172022-04-05#11729Bump mina-sshd from 2.7.0 to 2.8.0
0.1.152022-02-25#10421Refactor JDBC parameters handling
0.1.142022-02-14#10256Add -XX:+ExitOnOutOfMemoryError JVM option
0.1.132021-12-28#9158Update connector fields title/description
0.1.122021-12-01#8371Fixed incorrect handling “\n” in ssh key
0.1.112021-11-08#7719Improve handling of wide rows by buffering records based on their byte size rather than their count
0.1.102021-10-11#6877Add normalization capability, add append+deduplication sync mode
0.1.92021-09-29#5970Add support & test cases for MSSQL Destination via SSH tunnels
0.1.82021-08-07#5272Add batch method to insert records
0.1.72021-07-30#5125Enable additionalPropertities in spec.json
0.1.62021-06-21#3555Partial Success in BufferedStreamConsumer
0.1.52021-07-20#4874declare object types correctly in spec
0.1.42021-06-17#3744Fix doc/params in specification file
0.1.32021-05-28#3973Change dockerfile entrypoint
0.1.22021-05-13#3671Fix handle symbols unicode
0.1.12021-05-11#3195MS SQL Server Destination Release!
Advanced Analytica

Most businesses are sitting on information they can’t use at speed. The people who hold it are busy, and when they leave, the knowledge goes with them. The documents are static, and the processes around them weren’t designed for AI.

Advanced Analytica works with these businesses to turn that information into structured data assets. Once they're integrated, AI systems can govern it, and the business can use it. We bring in the strategy, the specification and the AI skills to rework how the business operates.

MOVE FAST. STAY SAFE.