Skip to main content
Feedback

SQL server connection

Prerequisites

Before establishing a connection, you must

  • Configure a secure connection method. For more information, refer to selecting connection methods such as Private networking, SSH tunnel, reverse SSH tunnel, and VPN.

  • Create a new user for Data Integration in your database.

Creating a new user for Data Integration

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 >]

Enable the new user you created above to access the database:

CREATE USER [< Data_Integration Username >] FOR LOGIN `<database>`

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 view permissions to the metadata of the tables to get the correct mapping of the tables in the schema or database:

GRANT VIEW ANY DEFINITION 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.

Password restrictions

Some special characters are not supported in database passwords. Avoid using the following characters in your database password:

  • Double quote (")
  • Dollar sign ($)
  • Backtick (`)
  • Backslash ()

Establishing a connection

You can connect to SQL Server using one of four methods:

  • SQL Server Authentication
  • Azure Active Directory
  • Azure AD Service Principal
  • SSH Tunnel

SQL server authentication

  1. Navigate to the Data Integration Console.

  2. Click Connections and select + New Connection.

  3. Choose SQL server.

  4. Choose your Connection Name.

  5. Enter Host.

  6. Fill in the Port Number.

  7. Enter your Database.

  8. Select SQL Server Authentication method.

  9. Enter your Username and Password

  10. Click Test Connection to verify if your connection is up to the task. If the connection succeeded, you can use this connection in Data Integration.

Azure active directory

  1. Navigate to the Data Integration Console.
  2. Click Connections and select + New Connection.
  3. Choose SQL server.
  4. Choose your Connection Name.
  5. Enter Host.
  6. Fill in the Port Number.
  7. Enter your Database.
  8. Select Azure Active Directory authentication method.
  9. Enter your Username and Password
  10. Use the Test Connection function to verify if your connection is up to the task. If the connection succeeded, you can now use this connection in Data Integration.

Azure AD service principal

Azure AD Service Principal authentication connects using an app registration's Tenant ID, 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 SQL Server (2022 or later, with Microsoft Entra authentication configured) and 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 role the integration needs to read data:

CREATE USER [boomi-data-integration] FROM EXTERNAL PROVIDER;
ALTER ROLE db_datareader 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.
  • For SQL Server (not Azure SQL Database), the instance must be configured for Microsoft Entra authentication first, which requires SQL Server 2022 or later. Earlier versions support Entra service principal authentication only through Azure SQL, not on-premises or VM-hosted SQL Server.

Configure the connection

  1. Navigate to the Data Integration Console.
  2. Click Connections and select + New Connection.
  3. Choose SQL server.
  4. Choose your Connection Name.
  5. Enter Host.
  6. Fill in the Port Number.
  7. Enter your Database.
  8. Select Azure AD Service Principal authentication method.
  9. Enter your Tenant ID.
  10. Enter your Client ID and Client Secret.
  11. Click Test Connection to verify if your connection is up to the task. If the connection succeeded, you can use this connection in Data Integration.
note
  • Tenant ID, Client ID, and Client Secret are all mandatory for this connection type. Enter the Tenant ID and Client ID as the UUIDs shown on your Azure AD app registration, for example a1b2c3d4-e5f6-7890-abcd-ef1234567890.
  • Change Data Capture (CDC) is supported for Azure AD Service Principal-authenticated SQL Server sources.
  • Bulk copy program (BCP) extraction does not support this authentication method. When you select Azure AD Service Principal on a source connection, Data Integration automatically switches to the db-exporter engine. This engine switch is expected behavior, not an error.
  • 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-MSSQL-RDBMS-209Microsoft SQL connection is missing a field: Tenant IDEnter the Tenant ID from your Azure AD app registration.
RVR-MSSQL-RDBMS-210Microsoft SQL connection is missing a field: Client IDEnter the Client ID (application ID) from your Azure AD app registration.
RVR-MSSQL-RDBMS-211Microsoft SQL connection is missing a field: Client SecretEnter the Client Secret you generated for the app registration.

SSH tunnel

To connect via SSH tunnel, follow our step-by-step tutorial.

On this Page