Skip to main content
Feedback

Azure SQL as a target

Azure SQL Database is a fully managed platform as a service database engine that handles the majority of database management tasks like upgrading, patching, backups, and monitoring without user involvement.

Prerequisites

  • You must create a new user for Data Integration in your database by copying the following commands:
CREATE LOGIN [ < Data_Integration Username>] WITH PASSWORD='< myPassword >'
note

Set up your password following the review of the Microsoft Password Policy.

Use the database from which you want to pull data. Replace <database> with the name of your current database:

USE [< database >]

Permit the new user you created to access the database:

CREATE USER [< Data_Integration Username >] FOR LOGIN &lt;database&gt;

Now grant the table or schema you want to extract data from to the new user you just created. Change the <database>, <schema>, and <table> to your existing database, schema, and table names:

GRANT SELECT to [< Data_Integration Username >]

Use the following syntax to grant a SELECT operation to a specific schema:

GRANT SELECT on SCHEMA :: [< schema >] TO [< Data_Integration Username >]

Grant Data Integration permissions to the metadata of the tables to get the correct mapping of the tables in the schema or database:

GRANT CONTROL ON [`<db-object>`] TO [< Data_Integration Username >]
GRANT ALTER ON [`<db-object>`] TO [< Data_Integration Username >]
  • If you want to connect using Azure Active Directory, make sure you configure and manage Azure AD authentication with Azure SQL.

Procedure

You can connect to Azure SQL using one of three methods:

  • SQL server authentication
  • Azure active directory
  • Azure AD service principal

SQL server authentication

  1. Allowlist our IP addresses.
  2. Choose your Connection Name.
  3. Enter Host.
  4. Fill in the Port Number.
  5. Enter your Database.
  6. Select SQL Server Authentication method.
  7. Enter your Username and Password
  8. Click Test Connection to verify your connection is up to the task. If the connection succeeds, you can use this connection in Data Integration.

Azure active directory

  1. Allowlist our IP addresses.
  2. Choose your Connection Name.
  3. Enter Host.
  4. Fill in the Port Number.
  5. Enter your Database.
  6. Select Azure Active Directory authentication method.
  7. Enter your Username and Password
  8. Click Test Connection to verify your connection is up to the task. If the connection succeeds, you can use this connection in Data Integration.

Azure AD service principal

Azure AD Service Principal authentication connects using an app registration's Client ID and Client Secret instead of a personal or shared user account. Use this method for system-to-system integrations that must meet enterprise security requirements, such as rotating credentials through Azure Key Vault without a human account in the loop.

Applies to Azure SQL Database.

Create a service principal

Before configuring the connection, create an Azure AD app registration and grant it access to your database. You need permission to register applications in your Azure AD tenant, or ask an administrator to do this for you.

  1. Register an application (service principal) in Microsoft Entra ID. For steps, refer to Microsoft's Register an application documentation.
  2. Generate a client secret for the app registration and record the value. Azure shows the secret only once and you can't retrieve it later. For steps, refer to Microsoft's Add a client secret documentation.
  3. From the app's Overview page, copy the Application (client) ID and the Directory (tenant) ID. These are the Client ID and Tenant ID you enter on the connection. The value you generated in step 2 is the Client Secret.

Grant the service principal access to your database. Connect to the database as an Entra admin, create a contained database user mapped to the app registration, and assign the roles the integration needs to read and write data:

CREATE USER [boomi-data-integration] FROM EXTERNAL PROVIDER;
ALTER ROLE db_datareader ADD MEMBER [boomi-data-integration];
ALTER ROLE db_datawriter ADD MEMBER [boomi-data-integration];
note
  • The user name in the database must match the app registration's display name exactly.
  • Run CREATE USER ... FROM EXTERNAL PROVIDER against each database the connection targets. It creates a database-level user, not a server login.

Configure the connection

  1. Allowlist our IP addresses.
  2. Choose your Connection Name.
  3. Enter Host.
  4. Fill in the Port Number.
  5. Enter your Database.
  6. Select Azure AD Service Principal authentication method.
  7. Optionally, enter your Tenant ID.
  8. Enter your Client ID and Client Secret. Both fields are mandatory.
  9. Click Test Connection to verify your connection is up to the task. If the connection succeeds, you can use this connection in Data Integration.
note
  • Enter the Tenant ID and Client ID as the UUIDs shown on your Azure AD app registration, for example a1b2c3d4-e5f6-7890-abcd-ef1234567890.
  • Managed Identity authentication and certificate-based Service Principal authentication are not supported in this release.

If you rotate the client secret in Azure Key Vault, update the Client Secret field on this connection in Data Integration to match. Otherwise, the connection fails on the next run.

Troubleshooting Azure AD service principal connections

Error codeMessageResolution
RVR-MSS-828Azure SQL connection is missing a field: Client IDEnter the Client ID (application ID) from your Azure AD app registration.
RVR-MSS-829Azure SQL connection is missing a field: Client SecretEnter the Client Secret you generated for the app registration.

Custom file zone

Data Integration lets you create a custom File Zone to manage your data in your own Azure service.

note

For Azure Blob Storage containers to work properly with Azure SQL, you must enter a SAS token in the connection form. To create a SAS token, refer to the Microsoft topic.

  1. Select the Custom File Zone toggle.
  2. By clicking on FileZone Connection, you can select the configured FileZone connection.
  3. Choose a Default Bucket (Container) from the drop-down list.
  4. Click Test Connection to verify your connection is up to the task.
  5. If the connection succeeds, click Save.

Configuration process

  1. After establishing a connection, select your Target Connection from the drop-down list.

  2. Click the curved arrow next to Schema on the right side of the row. After the refresh, click the row and choose the Schema to store the data.

  3. Enter the Table Prefix.

  4. Set the Loading Mode.

    info

    Loading Modes:

    Upsert Merge - Based on the keys column selected, the system matches rows and decide whether to replace matching rows, keep unmatched rows, or add new ones. Recommended for continuous merge runs because it does not keep duplication and creates an index for better performance. Upsert Merge includes two merge techniques:

    • Delete-Insert- Delete all data from the table, then INSERT INTO it using the column mapping defined keys. In this mode, the platform inserts new indexes to the table after deleting the existing rows (when keys between the new and existing data match).
    • Merge - Use the MERGE INTO clause to perform an upsert based on the primary keys chosen for the table mapping.

    Append Only - This appends any new records while keeping all the current records using INSERT INTO the target Table (matching on columns).

    Overwrite - This TRUNCATES the old records and INSERT the new records in the target table.

  5. In the Additional Options, keep the Toggle to True if you want to add Data Integration Metadata to the target table.

    note

    When the Source is in Multi Table mode, this option becomes available.

  6. Choose a Bucket (Container) and a path for your data to land.

  7. Set the period partition time frame for a FileZone folder.

note

You can enable Data Integration to divide the data according to the data insertion day, the Day/Hour, or the Day/Hour/Minute. This means that Data Integration produces data files from your sources under folders that correspond to the correct partition selected.

  1. Any Source (Excluding Webhook and CDC method Sources) to target Data Flow can now send data to your Azure SQL Container.
On this Page